Demonstrating row-level exclusive locks

The update command belongs to a DML statement, which produces a row-level lock when the transaction is not committed/rolled back. The goal is to prevent other users from modifying the data at the same time.

Preparatory work:

sql> CREATE TABLE TT (ID number); Table created. SQL> INSERT INTO TT values (1); 1 row created. SQL> INSERT INTO TT values (2); 1 row created. SQL> commit; Commit complete.

Window One:

SQL>updateset id=+where id=1; 1 Row updated.

Window Two:

SQL>updateset id=$where id= 1;

The cursor stops here and does not move.

This is the row level lock.

View the v$session under the SYS user.

V$session This parameter can see the current session through Blocking_session can see sid=43 session is sid=35 session blocked;

Sql> SelectSid,serial#,username,blocking_session fromV$sessionwhereUsername='HR'; SID serial# USERNAME blocking_session---------- ---------- ------------------------------ ----------------         *       1655HR +       6817HR *

In addition to executing commit/rollback in the window, you can also execute the following command in the library to kill the sid=35 session directly.

SQL>alterkill'35,1655'  immediate; System altered.

Window One

Sql> Select *  fromtt;Select *  fromTT*ERROR at line1: ORA-03135: Connection lost contactprocess ID:19460Session ID: *Serial Number:1655

Windows two

QL>updateset id=nwhere id=1; 1 row updated. SQL>Select* from tt;        ID----------                2

Row-level locks do not exist because the session of window one has been disconnected. The Windows Two UPDATE statement is executed.

Demonstrating row-level exclusive locks