Oracle lock Experiment

MASICONG @ orcl> select sid from v $ mystat where rownum = 1;

SID

----------

35

MASICONG @ orcl> select sid from v $ mystat where rownum = 1;

SID

----------

1

1. Simulate the blocking example caused by insert, update, and delete, describe the corresponding information in v $ lock, and give an SQL demonstration.

MASICONG @ orcl> create table test (id varchar (2) primary key );

Table created.

INsert Blocking

MASICONG @ orcl> insert into test values (10 );

1 row created.

MASICONG @ orcl> insert into test values (10); another user will get stuck when submitting the same request

MASICONG @ orcl> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ');

Sid ty ID1 ID2 LMODE REQUEST BLOCK

--------------------------------------------------------------

1 TX 131090 674 0 4 0

35 TM 74571 0 3 0 0

1 TM 74571 0 3 0 0

1 TX 65540 508 6 0 0

35 TX 131090 674 6 0 1

 

It indicates that a level-4 lock is required for a session, but a level-6 lock has been added to 35 to block the session. Because Session 1 has inserted a record, another TX lock can pass through.

MASICONG @ orcl> select object_name from dba_objects where object_id = 74571;

OBJECT_NAME

--------------------------------------------------------------------------------

TEST

 

You can use TM to know the lock currently applied to a table. All the IDS can be used to check which table is operated.

MASICONG @ orcl> commit;

Commit complete.

MASICONG @ orcl> insert into test values (10); when one session is submitted, the other session can access

*

ERROR at line 1:

ORA-00001: unique constraint (MASICONG. SYS_C0011055) violated

Update Blocking

MASICONG @ orcl> commit

2;

Commit complete.

MASICONG @ orcl> select * from test;

ID

--

1

10

MASICONG @ orcl> update test set id = 2 where id = 1;

1 row updated.

MASICONG @ orcl> update test set id = 2 where id = 1; the other is in the Block state.

MASICONG @ orcl> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ');

 

Sid ty ID1 ID2 LMODE REQUEST BLOCK

--------------------------------------------------------------

1 TX 458775 552 0 6 0

1 TM 74571 0 3 0 0

35 TM 74571 0 3 0 0

35 TX 458775 552 6 0 1

 

The blocking information is displayed, and the session is waiting for a 6-Level Lock.

MASICONG @ orcl> select sid, event from v $ session_wait where sid in (1, 35 );

SID EVENT

--------------------------------------------------------------------------

1 enq: TX-row lock contention

35 SQL * Net message from client

The above indicates that 1 session requires a TX lock

DELETE operation

MASICONG @ orcl> delete from test where id = 10;

1 row deleted.

MASICONG @ orcl> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ');

Sid ty ID1 ID2 LMODE REQUEST BLOCK

--------------------------------------------------------------

1 TX 524289 908 0 6 0

35 TM 74571 0 3 0 0

1 TM 74571 0 3 0 0

35 TX 524289 908 6 0 1

It indicates that a session requires a 6-Level Lock, but it is blocked by a 6-Level Lock of 35 sessions.

2. Simulate the scenario where RI locking causes blocking, analyze the corresponding locking information of v $ lock, and give an SQL demonstration.

MASICONG @ orcl> create table zhu (id number primary key );

Table created.

MASICONG @ orcl> create table cong (id references zhu (id ));

Table created.

MASICONG @ orcl> insert into zhu values (1 );

1 row created.

MASICONG @ orcl> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by 1, 2;

 

Sid ty ID1 ID2 LMODE REQUEST BLOCK

--------------------------------------------------------------

35 TM 74573 0 3 0 0

35 TM 74575 0 3 0 0

35 TX 131087 676 6 0 0

 

At this time, a level 3 lock has been added to the master table and the slave table to prevent DDL operations. At the same time, a level 6 lock has been added to prevent DML operations.

MASICONG @ orcl> insert into zhu values (1); if another session is executed, blocking will occur.

MASICONG @ orcl> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by 1, 2;

Sid ty ID1 ID2 LMODE REQUEST BLOCK

--------------------------------------------------------------

1 TM 74573 0 3 0 0

1 TM 74575 0 3 0 0

1 TX 262165 551 0 4 0

1 TX 589835 690 6 0 0

35 TM 74575 0 3 0 0

35 TM 74573 0 3 0 0

35 TX 262165 551 6 0 1

The above example indicates that a row of TX lock with 6 levels is not blocked and has been successfully executed. Three table locks are applied to the master and slave tables. One of them is blocked by a level 4 lock instead of a level 6 lock, resulting in waiting.

MASICONG @ orcl> update zhu set id = 2 where id = 1;

1 row updated.

MASICONG @ orcl> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by 1, 2;

Sid ty ID1 ID2 LMODE REQUEST BLOCK

--------------------------------------------------------------

35 TM 74573 0 3 0 0

35 TX 327699 721 6 0 0

 

When updating the records of the master table, only the tmlock and TX lock are applied to the master table.

MASICONG @ orcl> delete from zhu where id = 2;

1 row deleted.

MASICONG @ orcl> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by 1, 2;

 

Sid ty ID1 ID2 LMODE REQUEST BLOCK

--------------------------------------------------------------

35 TM 74573 0 3 0 0

35 TX 393248 700 6 0 0

When deleting the content of the primary table, only the tmlock and TX lock are added to the primary table.

MASICONG @ orcl> insert into zhu values (10 );

1 row created.

MASICONG @ orcl> insert into cong values (10 );

1 row created.

MASICONG @ orcl> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by 1, 2;

Sid ty ID1 ID2 LMODE REQUEST BLOCK

--------------------------------------------------------------

35 TM 74573 0 3 0 0

35 TM 74575 0 3 0 0

35 TX 393244 700 6 0 0

When the slave table is inserted, both the master table and the slave table have three levels of table locks and six levels of TX locks.

ASICONG @ orcl> delete from cong where id = 10

1 row deleted.

MASICONG @ orcl> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by 1, 2;

Sid ty ID1 ID2 LMODE REQUEST BLOCK

--------------------------------------------------------------

35 TM 74573 0 3 0 0

35 TM 74575 0 3 0 0

35 TX 262165 551 6 0 0

When the table is deleted, the master table also has a level 3 lock.

5.A deadlock-caused SQL statement is provided.Example.

Scenario: when the same table has two sessions, one is 23, and the other is 35, when the 23 session inserts data into the table, the 1 session inserts data into the table is operated, and the 2 session inserts data. After 23, inserting 2, 35 sessions to insert data 1 will cause blocking and eventually lead to a deadlock.

 

MASICONG @ orcl> select sid from v $ mystat where rownum = 1;

 

SID

----------

23

MASICONG @ orcl> insert into test values (1 );

 

1 row created.

 

MASICONG @ orcl> insert into test values (2 );

Insert into test values (2)

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

 

MASICONG @ orcl> select sid from v $ mystat where rownum = 1;

 

SID

----------

37

MASICONG @ orcl> insert into test values (2 );

 

1 row created.

 

MASICONG @ orcl> insert into test values (1 );

Insert into test values (1)

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

 

This article is from the "wushuang City" blog and is not reposted!