Locks and Latches

Lock is used to manage concurrent access to shared resources.

Submit it if necessary, not in advance.

Lock problem:

1. Missing updates

Session1	Session2
(1) Querying a row of data	(2) Querying the same row of data
(3) Update the line and submit
	(4) Update the line and submit
This process is known as missing updates	Because (3) all of the changes made will be lost

2. Locking strategy: Pessimistic lock (pessimistic) optimistic lock (optimistic)

Pessimistic lock This method takes effect before the user modifies the value.

Optimistic lock All locking actions are deferred until the update is about to be performed Implementation method: Use optimistic lock of version column, optimistic lock with user sum check

3. Blocking

Blocked insert With primary KEY or UNIQUE constraint Referential integrity constraints

Blocked merger, update, and delete

4. Deadlock

5. Lock Upgrade

Types of Locks

DML lock Select, insert, UPDATE, merge, delete May be a row-level lock or a table-level lock 1.TX Lock A TX lock is obtained when the transaction initiates the first modification Oracle does not use Lock Manager and the Oracle lockdown process is as follows: (1) Find the line address that you want to lock (2) Reach the line (3) Lock the line in place, in the position of the line (if the row is locked, wait for the end of the transaction to lock him up, unless NOWAIT is specified)

DDL lock Create and ALTER statements, and so on, can protect the object structure definition

Internal lock and latch latch Protecting internal data structures

Sql> CREATE TABLE Dept 2 AS SELECT * from Scott.dept; Table created. Sql> CREATE TABLE EMP 2 AS SELECT * from Scott.emp; Table created. sql> ALTER TABLE Dept 2 Add Constraint DEPT_PK 3 primary key (DEPTNO); Table altered. sql> ALTER TABLE EMP 2 Add Constraint EMP_PK 3 primary key (EMPNO); Table altered. sql> ALTER TABLE EMP 2 Add Constraint Emp_fk_dept 3 foreign KEY (DEPTNO) 4 References Dept (DEPTNO); Table altered. Sql> CREATE INDEX Emp_deptno_idx 2 on EMP (DEPTNO); Index created. Start a transaction: Sql> Update Dept 2 set Dname=initcap (dname); 4 rows updated.

View: SELECT username,        v$lock.sid,        TRUNC (Id1/power (2, +)) RBS,        Bitand (id1, To_number (' ffff ', ' xxxx ')) + 0 SLO T,        id2 seq,        lmode,         request   from V$lock, v$session where     v$lock. TYPE = ' TX '        and v$lock.sid = V$session.sid         and v$session.username = USER; USERNAME SID RBS SLOT SEQ lmode REQUEST --------------------------- ---------- ---------- ---------- ---------- ---------- ---------- Eoda 341 7 17 158 6 0 Sql> select xidusn,xidslot,xidsqn from V$transaction; Xidusn Xidslot xidsqn ---------- ---------- ---------- 7 17 158 There are a few interesting things here. The Lmode of the V$lock table is 6,request 0. View Oracle Database Reference get Lmode=6 is an exclusive lock. Requesting request=0 means that you have not made a request, that is to say you have the lock. This v$lock table has only one row. Oracle does not store a list of row-level locks anywhere. To see if a row is locked, you must find the row directly. The RBS, slot, and SEQ values match v$transaction, which is my transaction ID

Open another session sql> Update emp Set Ename=upper (ename); Rows updated. Sql> Update dept Set deptno=deptno-10; Now this session is blocked

Back to the original session SELECT username, V$lock.sid, TRUNC (Id1/power (2,)) RBS, Bitand (ID1, To_number (' ffff ', ' xxxx ')) + 0 slots, Id2 seq, Lmode, Request From V$lock, v$session WHERE V$lock. TYPE = ' TX ' and V$lock.sid = V$session.sid One and v$session.username = USER; USERNAME SID RBS SLOT SEQ lmode REQUEST ------------------- ---------- ---------- ---------- ---------- ---------- ---------- Eoda 311 10 3 7727 6 0 Eoda 311 7 17 158 0 6 Eoda 341 7 17 158 6 0 Sql> select xidusn,xidslot,xidsqn from V$transaction;     xidusn    xidslot      xidsqn ------------------ ------------     10        3         7727      7       17          158 Can see that a new transaction has started, the transaction ID is (10,3,7727), and this time, this session (sid=311) has two lines in V$lock. One line represents the lock (lmode=6) he owns. There is another row that shows a request with a value of 6. This is a request for a team exclusive lock. Interestingly, the RBS/SLOT/SEQ value of this request line is exactly the transaction ID of the lock holder. The sid=341 transaction is blocking the sid=311 transaction. As you can see from V$lock's self-connected query: Select username           from v$session           WHERE sid = A.sid)            Blocker,        a.sid,        ' is Blocking ',        (SELECT username            from V$session          WHERE sid = B.sid)  & nbsp;        Blockee,        b.sid   From V$lock A, V$lock b 13   WHERE a.block = 1 and b.request > 0 and a.id1 = b.id1 and A.id2 = b.id2; Blocker sid ' Isblocking ' blockee sid ------------------------------ ---------- ------------- ------------------------------ ---------- Eoda 341 is blocking Eoda 311 Now, commit the original transaction (sid=341), query again, and you can see that the request line is gone. Sql> commit; Commit complete. SELECT username,        v$lock.sid,        TRUNC (Id1/power (2, +)) RBS,        Bitand (id1, To_number (' ffff ', ' xxxx ')) + 0 SLO T,        id2 seq,        lmode,         request   from V$lock, v$session where     v$lock. TYPE = ' TX '        and v$lock.sid = V$session.sid 11          and v$session.username = USER; USERNAME SID RBS SLOT SEQ lmode REQUEST ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- Eoda 311 10 3 7727 6 0 Another session can also see the update complete, ending the blocked state. sql> Update emp Set Ename=upper (ename); Rows updated. Sql> Update dept Set deptno=deptno-10; 4 rows updated.

Locks and Latches