Lock

Database locks are used to provide concurrency control in order to ensure data consistency and integrity. Common uses of locks are:

ensure that only one user can modify a record at a time;
ensure that a table cannot be dropped while another user is querying it;
ensure that one user cannot delete a record while another is updating it.

Lock Types and Modes[edit]

Depending on their lock type (see V$LOCK.TYPE), locks can be grouped into user types and system types. The user type locks are:

TX - Transaction enqueue, acquired at row level
TM - DML enqueue, acquired at object (i.e. table) level
UL - User supplied

Another distinction for locks is based on lock modes (See V$LOCK.LMODE). The following lock modes exist:

0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)

Lock detection[edit]

This query will show locked objects with session details:

SELECT a.owner,a.object_name,b.sid,b.serial#,b.username,b.status
  FROM dba_objects a, v$session b, v$locked_object c
 WHERE a.object_id = c.object_id
   AND b.sid       = c.session_id;

Finding more info about an Oracle lock[edit]

Oracle uses locks and enqueues to serialize access to certain resources. They can be seen in v$lock. Every DBA is familiar with common lock types, such as TX and TM. Some other types such as CI, CF, JQ, ST are also fairly well-known and documented in the v$lock reference manual (go to documentation -> Reference -> v$lock).

Unfortunately, not all lock types are listed in v$lock reference page. In addition, that page does not give you the meanings of ID1 and ID2 columns of the v$lock view for each lock type. Most people know id1 for a TM lock matches dba_objects.object_id (not data_object_id, in case they differ). Some DBAs go too far and write scripts matching id1 with an object_id without the type='TM' predicate. If you have Anjo Kolk's wait event article, you know that's not true. Anjo's article is titled "Description of Oracle7 Wait Events and Enqueues". As of this writing, you can find it at http://www.orsweb.com/techpapers/eventsenque.pdf Since any URL pointing to that article may be gone at any time, please search the Internet if the given URL is not valid. Part 3 of the article lists all lock types in Oracle 7, giving much more details than just the type names.

Oracle Metalink has a note pointing to other articles about specific lock types. Look at Locking Reference section of Note:131775.1. Some good articles about oracle locks in general are listed too. Note:267332.1 lists the locks new to 10g as claimed. But take the word "new" with a grain of salt. When searching for something obscure on Metalink, make sure you use Advanced Search, which you can access through this URL http://metalink.oracle.com/metalink/plsql/kno_main.newAdvancedQuery and check Bug Database and Archived Articles checkboxes.

When you search on google, make sure you search on both Web and Groups pages (groups.google.com).

If you need in-depth knowledge of Oracle locks, Steve Adams's book "Oracle8i Internal Services" is the ultimate reference unless you work for Oracle (even then that bumble bee book is still invaluable).

The problem with searching for lock information is that all locks use two-letter codes (see sys.x$ksqst.ksqsttyp). Short strings make it difficult to search on the Internet. So often times you have to do your own research. Get a rough idea of what this lock is about with any available reference. Let's say it's CI, somehow named for Cross Instance as if it were for RAC or OPS only. According to Anjo Kolk's article, the lock is held for checkpoints, log switches... Let's check. In 8i query for x$ksqst.ksqstget where ksqsttyp= 'CI' and in 9i v$enqueue_stat.total_req# and succ_req# where eq_typ = 'CI'. These queries may tell you whether your recent actions caused enqueue gets to increment. My test shows that checkpoints indeed increment the gets but log switches do not. If you can find a way to hold the lock for some time, that's even better, because then you can look at v$lock instead, and possibly figure out what id1 and id2 are. If you can't hold the lock, you may still be able to catch it by tracing the session. In your own session, alter session set events = '10046 trace name context forever, level 8', or exec sys.dbms_system.set_ev(<sid>,<seq#>,10046,8,) for a victim session. In 9i, the generated trace file in udump contains lines like

WAIT #1: nam='enqueue' ela= 910796 p1=1128857606 p2=2 p3=5

That tells us that the approximately 1 second wait is on an enqueue of type CI. I know it's CI because p1 is 0x43490006 in hex and 0x43 0x49 are ASCII codes for C and I. The ending 6 means it's waiting for a CI lock held in exclusive mode. ID1 for the lock is 2 and ID2 is 5. According to Anjo's paper, ID1=2 means "DBWR synchronization of SGA with control file", which makes perfect sense in checkpoint, ID2=5 means "Used to queue up interested clients".

Unfortunately not all lock gets cause waits and therefore are not exposed as wait events. For instance, I haven't figured out a way to artificially induce a CF (controlfile) enqueue wait even though the lock is very frequently held even on a quiet database (just keep running select * from v$enqueue_stat where eq_type = 'CF'). If you see such CF in v$lock, you have a precious opportunity for research. Maybe dump the process state just for a record and analyze later, and possibly a hanganalyze trace, which is a simplified systemstate dump.