When you notice enqueue locks in your database and suspect that a blocking lock may be holding up other sessions. You’d like to identify the blocking and the blocked sessions.
So when you see an enqueue wait event in an Oracle database, chances are that it’s a locking phenomenon that’s holding up some sessions from executing their SQL statements. When a session waits on an “enqueue” wait event, that session is waiting for a lock that’s held by a different session. The blocking session is holding the lock in a mode that’s incompatible with the lock mode that’s being requested by the blocked session. You can issue the following command to view information about the blocked and the blocking sessions:
SQL> select decode(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type from v$lock
where (id1, id2, type) in
(select id1, id2, type from v$lock where request>0)
order by id1, request;
The V$LOCK view shows if there are any blocking locks in the instance. If there are blocking locks, it also shows the blocking session(s) and the blocked session(s). Note that a blocking session can block multiple sessions simultaneously, if all of them need the same object that’s being blocked. Here’s an example that shows there are locks present:
The key column to watch is the BLOCK column—the blocking session will have the value 1 for this column. In our example, session 68 is the blocking session, because it shows the value 1 under the BLOCK column. The blocking session, with a SID of 68, also shows a lock mode 6 under the LMODE column, indicating that it’s holding this lock in the exclusive mode—this is the reason session 81 is “hanging,” unable to perform its update operation. The blocked session, of course, is the victim—so it shows a value of 0 in the BLOCK column. It also shows a value of 6 under the REQUEST column, because it’s requesting a lock in the exclusive mode to perform its update of the column. The blocking session, in turn, will show a value of 0 for the REQUEST column, because it isn’t requesting any locks—it’s already holding it.
If you want to find out the wait class and for how long a blocking session has been blocking others, you can do so by querying the V$SESSION view, as shown here:
The query shows that the session with SID=68 is blocking the session with SID=81, and the block started 7,069 seconds ago.
How It Works
The following are the most common types of enqueue locks you’ll see in an Oracle database:
• TX: These are due to a transaction lock and usually caused by faulty application logic.
• TM: These are table-level DML locks, and the most common cause is that you haven’t indexed foreign key constraints in a child table.
In addition, you are also likely to notice ST enqueue locks on occasion. These indicate sessions that are waiting while Oracle is performing space management operations, such as the allocation of temporary segments for performing a sort.
So when you see an enqueue wait event in an Oracle database, chances are that it’s a locking phenomenon that’s holding up some sessions from executing their SQL statements. When a session waits on an “enqueue” wait event, that session is waiting for a lock that’s held by a different session. The blocking session is holding the lock in a mode that’s incompatible with the lock mode that’s being requested by the blocked session. You can issue the following command to view information about the blocked and the blocking sessions:
SQL> select decode(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type from v$lock
where (id1, id2, type) in
(select id1, id2, type from v$lock where request>0)
order by id1, request;
The V$LOCK view shows if there are any blocking locks in the instance. If there are blocking locks, it also shows the blocking session(s) and the blocked session(s). Note that a blocking session can block multiple sessions simultaneously, if all of them need the same object that’s being blocked. Here’s an example that shows there are locks present:
The key column to watch is the BLOCK column—the blocking session will have the value 1 for this column. In our example, session 68 is the blocking session, because it shows the value 1 under the BLOCK column. The blocking session, with a SID of 68, also shows a lock mode 6 under the LMODE column, indicating that it’s holding this lock in the exclusive mode—this is the reason session 81 is “hanging,” unable to perform its update operation. The blocked session, of course, is the victim—so it shows a value of 0 in the BLOCK column. It also shows a value of 6 under the REQUEST column, because it’s requesting a lock in the exclusive mode to perform its update of the column. The blocking session, in turn, will show a value of 0 for the REQUEST column, because it isn’t requesting any locks—it’s already holding it.
If you want to find out the wait class and for how long a blocking session has been blocking others, you can do so by querying the V$SESSION view, as shown here:
How It Works
The following are the most common types of enqueue locks you’ll see in an Oracle database:
• TX: These are due to a transaction lock and usually caused by faulty application logic.
• TM: These are table-level DML locks, and the most common cause is that you haven’t indexed foreign key constraints in a child table.
In addition, you are also likely to notice ST enqueue locks on occasion. These indicate sessions that are waiting while Oracle is performing space management operations, such as the allocation of temporary segments for performing a sort.
No comments:
Post a Comment