February 16, 2011

Oracle locks

Follow-up to oracle lock – look it again from Oracle/Java/Others

Step 1


create table test (id number)
insert into test values (1);

Before commit or rollback, check the v$lock table.
Two locks:
First one’s lock type is TM (DML enqueue), Lock mode is 3 – row-x(SX), Request is 0 (none)
and
Second one’s lock type is TX (Transaction enqueue), Lock mode is 6 – exclusive (X), Request is 0 (none).

Check v$locked_object table. It displays the similar information as well

Step 2
In the same session

update test set id = 2

The locks in v$lock and v$locked_objects show same information as step 1.

Step 3
Open a new session

update test set id = 3

Now check the v$lock table
The lock associated with the first session is still same: two locks.
But the “block” column in the “TX” lock is 1 , means it is blocking another session.
The “block” column in “TM” lock is still 0.

Session 2 has two locks now:
TM lock, Lmode is 3 (SX), request is 0
TX lock, LMode is 6 (X), request is 6 (X – exclusive X)

Check the v$locked_object

The row attached to session 1 is same.
A new row attached to session 2 has same (locked_mode, object_id), but XIDUSN, XIDSLOT, XIDSQN are all zero.

Conclusion

v$locked_object is very useful. It displays 1) user 2) object_id information 3) blocker & waiter

how to find out locked object in v$lock?

Other tables

- dba_waiters: including waiters and blocker, mode_held & mode_requested
- dba_blockers: just one column holding_session.
- dba_lock_internal: Very slow to access
- v$access : Very slow to access


- No comments Not publicly viewable


Add a comment

You are not allowed to comment on this entry as it has restricted commenting permissions.

Search this blog

Search Warwick Blogs

 

Tags

Most recent comments

  • Oracle 11g is the clear leader in this area. Oracle has invested heavily into self–tuning capabiliti… by shaane on this entry
  • All the features are very nice.I like Repair Advisors the most.Oracle 10G introduced some of the dat… by lucy on this entry
  • "logical standby failed to re–start, I am also getting the same error. I have search on internet and… by anemia on this entry
  • Thanks for the nice information because from last three days i am stuck on this problem…. by robot vacuum on this entry
  • So what happen City speed dating when you tried this coding once in your PC.. Please tell me so i am… by John Bergeron on this entry
Not signed in
Sign in

Powered by BlogBuilder
© MMXXI