The essence of Oracle row-level locks

The nature of Oracle row-level locks (I) three major components of the single-instance Oracle locking mechanism www.2cto.com locking mechanism: ① resource structure Oracle for each resource requiring "concurrent access, both use a Data structure in SGA to describe it. This structure is called resource structure. The data structure has three members: owner, waiter, and converter are three pointers pointing to the linked list composed of lock structures.

Converter and waiter have some differences: If an operation requires two locks in different modes, for example, first S, then X, the process will first request S, after the lock structure is obtained, it will be mounted on the owner. when X is required, S must be released first, then apply for X again, but the request may not be obtained immediately. At this time, the converter's priority in converter will be higher than that in waiter. According to the lmode and request of v $ lock, we can judge three of them: ● lmode> 0, request = 0 → owner ● lmode = 0, request> 0 → waiter ● lmode> 0, request> 0 → converter ② lock structure whenever a process wants to access shared resources, you must first lock the resource. The actual reason is to apply for a lock structure from the SGA to record lmode, PID, and so on, and then check whether you can immediately obtain access to the resource. ● If yes, the lock structure is mounted to the owner linked list of the resource structure. ● otherwise, mount the lock structure to the waiter linked list of the resource structure. ③ The enqueue algorithm allocates locks based on the first-in-first-out principle. (2) the locking mechanism above the row-level locks requires two data structures: resource and lock, it is suitable for coarse-grained resources, but for fine-grained access such as data records, no matter from memory requirements or maintenance costs, it is a nightmare. Oracle's Row-Level Lock is the line-Level Lock that debuted in this situation. It is not a lock in the general sense of Oracle, although it has a lock function, however, row-level locks do not have any related overhead. The number of resources required to lock 10 million rows is exactly the same as the number of resources required to lock one row. This is a constant: 0 and 1 have a flag on each row of Oracle data to indicate whether the row data is locked. to check whether a row is locked, you must find this row directly, instead of counting on which list we can get the answer d Ump a data Block. The trc file of its transaction header is excerpted as follows: [SQL] Block header dump: 0x01000197 Object id on Block? Y seg/obj: 0xcd8a csc: 0x00. a26fe itc: 2 flg: E typ: 1-DATA brn: 0 bdba: 0x1000191 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0001. 005.00000100 0x0080000f. 00ae. 23 -- U-1 fsc 0x0000. 000a2707 0x02 0x0000. 000.00000000 0x00000000. 0000.00 ---- 0 fsc 0x0000.00000000 where the Lck field is the expression of the row-Level Lock: 1 lock, 0 do not lock the data header part of the trc excerpt is as follows: [SQL] tab 0, row 0, @ 0x1f93 tl: 5 fb: -- H-FL -- lb: 0x1 cc: 1 col 0: [1] 61 Where lb => ITL number in fact, lb is Itl concurrent access, the transaction finds the Itl through this lb to determine the Lck value. If it is 1, it is mounted to the queue pool. Therefore, when the user is blocked, it is not blocked by the row-Level Lock of a certain record, it is blocked by the TX lock. The following experiment proves that session_A [SQL] sys @ ORCL> drop table t purge; Table dropped. sys @ ORCL> create table demo (id number, name varchar2 (10); Table created. sys @ ORCL> insert into demo values (1, 'bin'); 1 row created. sys @ ORCL> insert into demo values (2, 'think'); 1 row created. sys @ ORCL> insert into demo values (3, 'water'); 1 row created. sys @ ORCL> commit; Commit complete. sys @ ORCL> select * from demo; id name ---------- 1 bin 2 think 3 water sys @ ORCL> savepoint a; Savepoint created. sys @ ORCL> update demo set name = 'think big 'where id = 2; 1 row updated. session_ B is in session_ B, and the same record is modified concurrently, and the session is blocked [SQL] When your sys @ ORCL> update demo set name = 'think big 'where id = 2; -- blocked. At this time, session_A rolls back to the previous savepoint. This is equivalent to revoking the modification to the record, but session_ B is still in the waiting status because session_ B is blocked by the TX lock of session_A, instead of being blocked by the row-Level lock of session_A, the query is performed on session_C: [SQL] sys @ ORCL> select sid, lmode, request from v $ lock where sid in (Limit ); sid lmode request ---------- 1090 0 6 1081 3 0 1090 3 0 1081 6 0 sys @ ORCL> select sid, event from v $ session where sid in (Limit ); sid event ---------- ---------------------------------------------------------------- 1081 SQL * Net message from client 1090 enq: TX-row lock contention