Example of blocking caused by insert, update, and delete

Example 1. example of update lock generation: www.2cto.com session 1: SQL> drop table t purge; SQL> create table t (id int primary key); Table created. SQL> select sid from v $ mystat where rownum = 1; SID ---------- 854 SQL> insert into t values (1); 1 row created. SQL> commit; Commit complete. SQL> update t set id = 2 where id = 1; -- update 1 row updated not submitted. session 2: SQL> select sid from v $ mystat where rownum = 1; SID ------ ---- 834SQL> update t set id = 3 where id = 1; -- when the same record is updated, the SQL statement waits for session 3: SQL> select sid, type, id1, id2, lmode, request, block from v $ lock where sid in (834,854) order by 1, 2; sid ty ID1 ID2 lmode request block ---------- -- ---------- 834 TM 91874 0 3 0 0 834 TX 655407 0 6 0 1648480 TM 854 0 3 0 91874 TX 854 655407 6 0 1SQL> select sid, event from v $ se Ssion_wait where sid in (834,854); sid event ---------- limit 834 enq: TX-row lock contention 854 SQL * Net message from client description: Here sid = 854 is session1, sid = 834 is session2; TM is a table-Level Lock (segment lock), indicating that the table's records are not allowed to perform DDL operations on the table during modification; TX is a row-Level Lock (transaction lock) that does not allow DML operations on the table's modified records. When TY = TM and ID2 = 0, the ID1 value is for this table (segment) when TY = TX and ID2 <> 0, ID1 + ID2 constitute the position of the transaction in the rollback segment; LMODE = 3 indicates a table-level shared lock, LMOD E = 6-row exclusive locks (the lock in the highest mode); REQUEST = 6 indicates that the current session is waiting for a lock in LMODE = 6, indicating that the session is being blocked; block = 1 indicates that this session is blocking other sessions; 2. example of delete lock generation: session 1: SQL> select * from t; ID ---------- 1SQL> delete from t where id = 1; -- delete SQL 1 row deleted not submitted. session 2: SQL> delete from t where id = 1; -- when the same record is deleted, the SQL statement waits for session 3: SQL> select sid, type, id1, id2, lmode, request, block from v $ lock where sid in (834,854) order by 1, 2; sid ty ID1 ID2 LMODE REQUEST B LOCK ---------- -- ---------- 834 TM 91874 0 3 0 0 834 TX 655364 1648669 0 6 0 854 TM 91874 0 3 0 0 854 TX 655364 1648669 6 0 1SQL> select sid, event from v $ session_wait where sid in (834,854); sid event ---------- limit 834 enq: TX-row lock contention 854 SQL * Net message from client Description: Same as update 3. example of insert lock generation: session 1: SQL> drop table t purge; SQL> create table t (id int primary key); Table created. SQL> insert into t values (1); -- insert 1 row created not submitted. session 2: SQL> insert into t values (1); -- when the same primary key record is inserted, the SQL statement waits for session 3: SQL> select sid, type, id1, id2, lmode, request, block from v $ lock where sid in (834,854) order by 1, 2; sid ty ID1 ID2 lmode request block --------------------------------------- --- ---------- 834 TM 91874 0 3 0 834 TX 262174 192335 6 0 834 TX 458776 193901 0 4 0 854 TM 91874 0 3 0 0 854 TX 458776 193901 6 0 1SQL> select sid, event from v $ session_wait where sid in (834,854); sid event ---------- limit 834 enq: TX-row lock contention 854 SQL * Net message from client description: in this case, unlike the update lock, Session 2 holds an LMO DE = 6 exclusive lock and wait for an LMODE = 4 lock. Because not the same record is inserted, the record inserted at Session 2 is not blocked, but the block header of the table is blocked. Therefore, the Session requests a lock with LMODE = 4.