Can a select statement create a transaction Lock?

Recently I had to research a “enq: TM contention” wait event. I queried active session history (ASH) an found blocking session’s id.

I queried ASH again and found what the blocking session was doing right at the time when the other session was waiting on the “enq: TM contention” lock. At this time there was a long running select statement active in the blocking session.

How is that possible? Can a select create a lock? What must have happened ? Would it actually help to tune the select?

For a further hint you might wat to read Arup Nanda’s blog on transaction locks.

enq: TX row lock contention and enq:TM contention

Advertisements

One comment

  1. What the session is doing right now doesn’t need to have any relation with what the session did to obtain the lock.

    It may have:
    updated one row from a table
    run intensive query
    (whilst the query is running, the original updated row is still locked)
    then do something else?
    then commit?

    Speeding up the query could potentially mean that the process to release the lock (committing) gets hit sooner, but we don’t know what comes next in the process (if anything)… if it’s causing problems blocking other sessions then the whole process should be looked at.

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s