Shulou(Shulou.com)05/31 Report--

According to the analysis of lock mechanism and experiment in Oracle, I believe that many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

What is the purpose of using locks:

In order to solve the problem of data modification caused by concurrent operation of the same resources in a multi-user environment. Locks do not need to be considered in a single-user environment because all operations are serial. The following article gives a brief introduction

Main points:

The classification of locks is extremely complex, enqueue, latch, mutex, etc., are all in order to solve the concurrency, and there is some confusion of their own, so I will not explain too much. Here are some key points for lock.

Exclusive lock:

Related resources are not allowed to be shared. Only one transaction of a resource can acquire the exclusive lock of the resource at a point in time, and only the transaction that holds the lock can modify the related resource. Other transactions that want to acquire the lock can only wait for the transaction to release the exclusive lock because of commit or rollback.

Shared lock: allows related resources to be shared. That is, multiple transactions are allowed to hold a shared lock on a resource at the same time. For a dml operation, locks are added to the table and rows, that is, TM locks and TX locks in v$lock.

Basic principles of row-level locking:

The information of the row-level lock is placed in the data block. If you want to modify the value of a record, you are actually accessing the corresponding block, assigning an ITL, and then accessing the rowpiece header through rowid. If the second byte lock byte (lock byte occupies only 1 byte, the maximum value is 255, which is why the maximum maxtrans is 255mm) is 0, change it to the assigned ITL slot number. If another transaction also wants to modify the data, it will find that the lock byte is not 0, and if the first transaction has not been completed, the second transaction enters the enqueue wait, that is, transaction enqueue.

Table lock can be divided into the following types:

1. Row Share (RS | SS)

2. Row Exclusive Table Lock (RX | SX)

3. Share Table Lock (S)

4. Share Row Exclusive Table Lock (SRX | SSX)

5. Exclusive Table Lock (X)

The following are the lock types corresponding to the numbers in the v$lock.LMODE field

LMODE (Lockmode in which the session holds the lock):

0-none

1-null (NULL)

2-row-S (SS)

3-row-X (SX)

4-share (S)

5-S/Row-X (SSX)

6-exclusive (X)

To better develop the following, here is a list of the compatibility of various TM lock types.

Detailed verification will be given in 4.

By the way, I would like to quote the classic:

Rows are locked only when they are modified.

When a statement modifies a record, only that record is locked, and there is no lock escalation in the Oracle database.

When a line is modified, it will block other people's changes to it.

When a transaction modifies a row, a row lock (TX) is added to the row to prevent other transactions from modifying the same row.

Reading never stops writing.

Reading does not block writing, but the only exception is select. For update.

Writing never blocks reading.

When a row is modified, Oracle provides consistent reading of the data through the rollback segment

1. Simulate the blocking caused by insert,update and delete respectively

A simple description of a update update statement

When we update the record of a table, there are two kinds of locks, one is the DML lock (TM), which can be called table lock, and the other is the transaction lock (TX), which can also be called row lock.

It can be found in v$lock.

For example, in the following example:

SQL > select * from tt

ID NAME

--

1 aaa

2 aaa

3 aaa

4 aaa

5 aaa

SQL > update tt set id=7

5 rows updated.

SQL > select sid,type,lmode,request,block from v$lock where sid = (select sid from v$mystat where rownum select sid from v$mystat where rownum insert / * + append_values * / into tt values (8)

1 row created.

SQL > select sid, type, lmode, request, block from v$lock where sid = (select sid from v$mystat where rownum update tt set id=9

Waiting...

Take a look at the lock:

Select sid, type, id1, lmode, request, block

From v$lock l

Where sid in (select session_id from v$locked_object)

And type in ('TM',' TX')

Order by 1

SID TY ID1 LMODE REQUEST BLOCK

1 TM 89451 6 0 1-session1 contains table 6 level locks, which are blocking other transactions

1 TX 262155 6 0 0

30 TM 89451 0 30-session2 it is requesting a level 3 lock on the table.

Therefore, when the direct path is loaded, a level 6 lock is added to the table, blocking the operation of other transactions to add any type of lock to the table.

(sqlldr parallel + level 4 locks will be added when direct paths are loaded)

Because the blocking caused by the primary key | unique key

SQL > truncate table tt

Table truncated.

SQL > insert into tt values (1)

1 row created.

SQL > insert into tt values (2)

1 row created.

SQL > commit

Commit complete.

Session1 session_id=1:

SQL > alter table tt add primary key (id)

Table altered.

SQL >

SQL > insert into tt values (3)

1 row created.

Session2 session_id=30:

SQL > insert into tt values (3)

Waiting...

SQL > select sid, type, id1, lmode, request, block

From v$lock l

Where sid in (select session_id from v$locked_object)

And type in ('TM',' TX')

Order by 1

SID TY ID1 LMODE REQUEST BLOCK

1 TX 458773 6 0 1

1 TM 89451 3 0 0

30 TX 524308 6 0 0

30 TX 458773 0 4 0

30 TM 89451 30 00

SQL > set lines 200 pages 999

SQL > select sid,seq#,event from v$session_wait where sid=30

SID SEQ# EVENT

-

30 24 enq: TX-row lock contention

A row lock wait event occurred here.

You can see that because the same value is inserted on the owning primary key column, the second session is requesting a level 4 shared lock in addition to holding its own level 6 exclusive lock for this transaction. There's a blockage here. If the first session is submitted.

The second session will report an error.

SQL > insert into tt values (3)

Insert into tt values (3)

*

ERROR at line 1:

ORA-00001: unique constraint (SYS.SYS_C0011620) violated

Update blocking

Blocking in this part is relatively simple. Whenever a update operation occurs, a 6-level exclusive lock is added to the existing row and a 3-level shared lock is added to the table.

Session1 session_id=1:

SQL > select * from tt

ID NAME

--

1 a

2 b

3 c

SQL > update tt set name='AA' where id=1

1 row updated.

Session2 session_id=30:

SQL > update tt set name='BB' where id=2

1 row updated.

Session3 session_id=32:

SQL > update tt set name='ABC' where id=1

Waiting...

Let's take a look at the lock:

SQL > select sid, type, id1, lmode, request, block

2 from v$lock l

3 where sid in (select session_id from v$locked_object)

4 and type in ('TM',' TX')

5 order by 1

SID TY ID1 LMODE REQUEST BLOCK

1 TX 196629 6 0 1-session1 is blocking session 3

1 TM 89451 3 0 0

30 TX 327706 6 0 0

30 TM 89451 30 0

32 TX 196629 0 6 0

32 TM 89451 3 0 0

6 rows selected.

As you can see above, multiple level 3 shared locks can be added to a single table.

Session2 can be executed normally because it modifies the record of id=2.

Because session3 modified the id=1 record, session1 was modifying it at this time, and added 6 levels of exclusive lock to the resources in this line. So session3 blocking needs to wait for session 1 to be released before it can be executed smoothly.

Delete blocking

In fact, the locking operations for delete, update, and insert operations are roughly the same, adding level 3 shared locks to the table and exclusive locks to modified rows.

So as long as you want to modify the same row in the table concurrently, blocking will occur later before the end of the first transaction that acquires the lock.

SQL > select * from tt

ID NAME

--

1 ABC

2 BB

3 c

Session1 session_id=1:

Delete from tt where id=1

1 row deleted.

Session2 session_id=30:

SQL > delete from tt where id > 1

2 rows deleted.

Session3 session_id=32

SQL > delete tt

Waiting...

SQL > select sid, type, id1, lmode, request, block

From v$lock l

Where sid in (select session_id from v$locked_object)

And type in ('TM',' TX')

Order by 1

SID TY ID1 LMODE REQUEST BLOCK

1 TX 262174 6 0 1

1 TM 89451 3 0 0

30 TX 655368 6 0 0

30 TM 89451 30 0

32 TX 262174 0 6 0

32 TM 89451 3 0 0

6 rows selected.

Blocking has occurred, and session 3 can complete successfully only after the transaction between session 1 and session 2 ends.

Here are two interesting experiments

Interesting experiment 1

SQL > insert into tt values (1)

1 row created.

SQL > insert into tt values (2)

1 row created.

SQL > insert into tt values (3)

1 row created.

SQL > commit

Commit complete.

SQL > select * from tt

ID NAME

--

1 a

2 b

3 c

Session1 session_id=1

SQL > delete from tt where id=2

1 row deleted.

Session2 session_id=32

SQL > update tt set name='wang' where id > 1

Waiting...

Session3 session_id=32

SQL > delete from tt where id=3

1 row deleted.

Check the lock:

Select sid, type, id1, lmode, request, block

From v$lock l

Where sid in (select session_id from v$locked_object)

And type in ('TM',' TX')

Order by 1

SID TY ID1 LMODE REQUEST BLOCK

1 TX 655382 6 0 1

1 TM 89451 3 0 0

30 TX 655382 0 6 0

30 TM 89451 30 0

32 TX 196631 6 0 0

32 TM 89451 3 0 0

6 rows selected.

It's interesting here, because the record of session 2 update includes the line id=2, so when the line of id=2 is locked, transaction enqueue occurs here, and it goes into waiting before it can lock any records. When session3 executes, it finds that this line of id=3 does not have a lock mark, so it successfully locks the record of id=3.

At this time, after we rollback the first record,

Session1:

SQL > rollback

Rollback complete.

Found that session2 is still waiting.

Take another look at the lock:

SQL > select sid, type, id1, lmode, request, block

From v$lock l

Where sid in (select session_id from v$locked_object)

And type in ('TM',' TX')

Order by 1

SID TY ID1 LMODE REQUEST BLOCK

30 TX 196631 0 6 0

30 TM 89451 30 0

30 TX 327712 6 0 0

32 TX 196631 6 0 1

32 TM 89451 3 0 0

At this point, we can see that session2 is waiting for the transaction of session3 to finish in order to acquire the lock of the record of id=3.

Interesting experiment 2

SQL > select * from tt

ID NAME

--

1 a

2 wang

SQL > insert into tt values (3)

1 row created.

SQL > commit

Commit complete.

SQL > select * from tt

ID NAME

--

1 a

3 c

2 wang

Session1 session_id=1

SQL > delete from tt where id = 3

1 row deleted.

Session2 session_id=30

SQL > update tt set name='dddddddddd' where id > 1

Waiting..

Session3 session_id=32

SQL > delete from tt where id = 2

1 row deleted.

SQL > select sid, type, id1, lmode, request, block

2 from v$lock l

3 where sid in (select session_id from v$locked_object)

4 and type in ('TM',' TX')

5 order by 1

SID TY ID1 LMODE REQUEST BLOCK

1 TX 131087 6 0 1

1 TM 89451 3 0 0

30 TX 458774 6 0 1

30 TX 131087 0 6 0

30 TM 89451 30 0

32 TX 458774 0 6 0

32 TM 89451 3 0 0

7 rows selected.

Session 3 is also waiting because session2 first acquires id=2 's row lock and then waits for id=3 's row lock.

Blocking caused by ITL

Blocking occurs when there is no extra space in the block to add ITL entry. You can take a look at the following example:

SQL > create table tb_itl (id int, name varchar2 (4000)) pctfree 0 initrans 1

Table created.

SQL > insert into tb_itl select level,'d' from dual connect by level commit

Commit complete.

SQL > update tb_itl set name=lpad ('Xuezhu 2000 pr. Name)

10000 rows updated.

SQL > commit

Commit complete.

The above operation ensures that there is no extra space in at least the first block.

Select t.id

Dbms_rowid.rowid_relative_fno (t.rowid) as "FNO#"

Dbms_rowid.rowid_block_number (t.rowid) as "BLK#"

Dbms_rowid.rowid_row_number (t.rowid) as "ROW#"

From tb_itl t

Where rownum select t.id

2 dbms_rowid.rowid_relative_fno (t.rowid) as "FNO#"

3 dbms_rowid.rowid_block_number (t.rowid) as "BLK#"

4 dbms_rowid.rowid_row_number (t.rowid) as "ROW#"

5 from tb_itl t

6 where rownumupdate tb_itl set name=lpad ('Xuejie 2000) where id = 1

1 rowupdated.

Session2 session_id=30

SQL > update tb_itl set name=lpad ('Xuezhong 2000 MagneName) where id = 2

1 rowupdated.

Session3 session_id=30

SQL > update tb_itl set name=lpad ('Xuezhong 2000 MagneName) where id = 3

Waiting...

Take a look at the lock information:

Select sid, type, id1, lmode, request, block

From v$lock l

Where sid in (select session_id from v$locked_object)

And type in ('TM',' TX')

Order by 1

SID TY ID1 LMODE REQUEST BLOCK

1 TX 327705 6 0 1

1 TM 89470 3 0 0

30 TX 131081 6 0 0

30 TM 89470 30 0

32 TX 327705 0 40-apply for level 4 lock

32 TM 89470 3 0 0

6 rows selected.

SQL > set lines 200

SQL > select sid,seq#,event from v$session_wait where sid=32

SID SEQ# EVENT

-

32 67 enq: TX-allocate ITL entry

Because you can't add more ITL in block 94905, you can't add more ITL entry (it only takes 24b to extend one).

This doesn't usually happen.

Solution: set the inittrans parameter of the table to a reasonable value.

Blocking caused by Bitmap

SQL > create table tb_bitmap_test (id number, gender varchar2 (1))

Table created.

SQL > insert into tb_bitmap_test select level, 'F'from dual connect by level insert into tb_bitmap_test select level,' M'from dual connect by level create bitmap index tb_bitmap_test_btidx1 on tb_bitmap_test (gender)

Index created.

SQL > select * from tb_bitmap_test

ID G

--

1 F

2 F

3 F

1 M

2 M

Session1 session_id=1:

SQL > update tb_bitmap_test set gender='M' where id=1 and gender='F'

1 row updated.

Session2 session_id=30:

SQL > delete tb_bitmap_test where gender='M' and id = 1

Waiting...

Session3 session_id=32

SQL > insert into tb_bitmap_test values (1)

1 row created.

-- Lock condition:

Select sid, type, id1, lmode, request, block

From v$lock l

Where sid in (select session_id from v$locked_object)

And type in ('TM',' TX')

Order by 1

SID TY ID1 LMODE REQUEST BLOCK

1 TM 89471 3 0 0

1 TX 262147 6 0 1

30 TX 589837 6 0 0

30 TM 89471 30 0

30 TX 262147 0 4 0

32 TM 89471 3 0 0

32 TX 196608 60 0

7 rows selected.

Regardless of whether it is gender='M' or'F', any dml operation involving the values of these two fields will go into waiting (including insert)

Because the first session locks the entire bitmap segment. However, as long as the value of gender does not involve M or F, it can be executed smoothly. So the session3 was executed smoothly.

two。 Simulates a scenario in which RI locking causes blocking.

-initialize the environment

SQL > create table tun2_p (id int primary key)

Table created.

SQL > create table tun2_c (pid references tun2_p (id))

Table created.

SQL > insert into tun2_c values (1)

Insert into tun2_c values (1)

*

ERROR at line 1:

ORA-02291: integrity constraint (SYS.SYS_C0011622) violated-parent key not found

Here, because of referential integrity constraints, the contents of the child table must match the contents of the parent table. Because there is no record of id=1 in the parent table, an error is reported here

-- main table insert

SQL > insert into tun2_p values (2)

1 row created.

SQL > select l.sidrecoveryl.typerecoverl.id1reportl.requestrewagel.blockjold d.objectwriting name from v$lock lwrence lockedwriting object orelessobjects d

2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM',' TX')

3 order by 1

SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME

-

1 TX 589833 6 0 0 TUN2_P

1 TM 89475 3 0 0 TUN2_P

1 TM 89473 3 0 0 TUN2_C

1 TX 589833 6 0 0 TUN2_C

1 TM 89475 3 0 0 TUN2_C

1 TM 89473 3 0 0 TUN2_P

6 rows selected.

SQL > select * from TUN2_P

ID

-

two

SQL > select * from TUN2_C

No rows selected

-- updates to the main table (records that are not referenced in child tables)

Update tun2_p set id=3 where id=2

1 row updated.

SQL > select l.sidrecoveryl.typerecoverl.id1reportl.requestrewagel.blockjold d.objectwriting name from v$lock lwrence lockedwriting object orelessobjects d

2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM',' TX')

3 order by 1

SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME

1 TM 89473 3 0 0 TUN2_P

1 TX 655386 6 0 0 TUN2_P

-main table deletion (records that are not referenced in child tables)

SQL > delete tun2_p where id=3

1 row deleted.

SQL > select l.sidrecoveryl.typerecoverl.id1reportl.requestrewagel.blockjold d.objectwriting name from v$lock lwrence lockedwriting object orelessobjects d

2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM',' TX')

3 order by 1

SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME

-

1 TM 89473 3 0 0 TUN2_P

1 TX 655386 6 0 0 TUN2_P

SQL > commit

Commit complete.

If the records referenced by the child table are not included in the upadte and delete operations, the child table is not locked. Insert, on the other hand, is a bit more complex, cascading and locking child tables.

If a change occurs on a record referenced by a child table, an error is reported. For example:

Updatetun2_p set id=3 where id=1

ERROR atline 1:

ORA-02292:integrity constraint (DEXTER.SYS_C0014143) violated-child record found

Child table insertion

-- query:

SQL > select * from tun2_p

ID

-

two

Insert child table:

SQL > insert into tun2_c values (2)

1 row created.

SQL > select l.sidrecoveryl.typerecoverl.id1reportl.requestrewagel.blockjold d.objectwriting name from v$lock lwrence lockedwriting object orelessobjects d

2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM',' TX')

3 order by 1

SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME

1 TX 524302 6 0 0 TUN2_P

1 TM 89475 3 0 0 TUN2_P

1 TM 89473 3 0 0 TUN2_C

1 TX 524302 6 0 0 TUN2_C

1 TM 89475 3 0 0 TUN2_C

1 TM 89473 3 0 0 TUN2_P

6 rows selected.

-- Child table update:

SQL > update tun2_c set pid=1 where pid=2

Update tun2_c set pid=1 where pid=2

*

ERROR at line 1:

ORA-02291: integrity constraint (SYS.SYS_C0011622) violated-parent key not found

-- increase the parent key

SQL > insert into tun2_p values (1)

1 row created.

SQL > commit

Commit complete.

-- update child tables

SQL > update tun2_c set pid=1 where pid=2

1 row updated.

SQL > select l.sidrecoveryl.typerecoverl.id1reportl.requestrewagel.blockjold d.objectwriting name from v$lock lwrence lockedwriting object orelessobjects d

2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM',' TX')

3 order by 1

SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME

1 TX 196632 6 0 0 TUN2_P

1 TM 89475 3 0 0 TUN2_P

1 TM 89473 3 0 0 TUN2_C

1 TX 196632 6 0 0 TUN2_C

1 TM 89475 3 0 0 TUN2_C

1 TM 89473 3 0 0 TUN2_P

6 rows selected.

-- Child table deletion

SQL > delete from tun2_c where pid=1

1 row deleted.

SQL > select l.sidrecoveryl.typerecoverl.id1reportl.requestrewagel.blockjold d.objectwriting name from v$lock lwrence lockedwriting object orelessobjects d

2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM',' TX')

3 order by 1

SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME

-

1 TX 196632 6 0 0 TUN2_P

1 TM 89475 3 0 0 TUN2_P

1 TM 89473 3 0 0 TUN2_C

1 TX 196632 6 0 0 TUN2_C

1 TM 89475 3 0 0 TUN2_C

1 TM 89473 3 0 0 TUN2_P

6 rows selected.

The record of the child table must refer to the record of the parent table, so when you dml the child table, the parent table is locked.

Complex example

There are no records in the two tables.

Session1 session_id=1

SQL > select sid from v$mystat where rownum commit

Commit complete.

SQL > select * from tun2_p

ID

-

one

two

SQL > select * from tun2_c

No rows selected

SQL > insert into tun2_p values (3)

1 row created.

SQL > select l.sidrecoveryl.typerecoverl.id1reportl.requestrewagel.blockjold d.objectwriting name from v$lock lwrence lockedwriting object orelessobjects d

2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM',' TX')

3 order by 1

SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME

1 TX 524309 6 0 0 TUN2_P

1 TM 89475 3 0 0 TUN2_P

1 TM 89473 3 0 0 TUN2_C

1 TX 524309 6 0 0 TUN2_C

1 TM 89475 3 0 0 TUN2_C

1 TM 89473 3 0 0 TUN2_P

6 rows selected.

As you can see, when a record is inserted into the parent table, both the parent and child tables are locked, adding a level 3 shared lock to the table.

Other transactions cannot see the record of id=1 in the parent table before the session1 is committed. Let's try inserting the record of pid=1 into the child table.

Session2 session_id=30:

SQL > insert into tun2_c values (3)

Waiting...

You can see that session2 has entered the blocking state. Let's take a look at the lock.

SQL > select l.sidrecoveryl.typerecoverl.id1reportl.requestrewagel.blockjold d.objectwriting name from v$lock lwrence lockedwriting object orelessobjects d

2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM',' TX')

3 order by 1

SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME

1 TX 524309 6 0 1 TUN2_P

1 TM 89473 3 0 0 TUN2_P

1 TM 89475 3 0 0 TUN2_P

1 TM 89473 3 0 0 TUN2_C

1 TM 89475 3 0 0 TUN2_C

1 TX 524309 6 0 1 TUN2_C

30 TM 89473 30 0 TUN2_C

30 TM 89475 30 0 TUN2_C

30 TX 262146 6 0 0 TUN2_C

30 TX 524309 0 4 0 TUN2_C

30 TM 89473 30 0 TUN2_P

30 TM 89475 30 0 TUN2_P

30 TX 262146 6 0 0 TUN2_P

30 TX 524309 0 4 0 TUN2_P

14 rows selected.

First, we can see that session2 also has two TM table locks that lock the child table and the parent table, respectively. This means that when the child table updates the data, the referenced object is also locked.

Then we see that the subtable is stuck in waiting.

This is because the successful execution of a transaction in session2 depends on the status of the transaction in session1. Transactions in session1 are now in a pending state.

Is it a little confused with reading consistency? Do you think the transaction in the second session should not go into blocking, but report an error directly?

Unlike read consistency, you can get a consistency view based on undo when querying.

When the transaction is executed, it is only related to the current state of the data.

After the first session transaction rollback, session2 will report an error.

SQL > insert into tun2_c values (1)

Insert intotun2_c values (1)

ERROR atline 1:

ORA-02291:integrity constraint (DEXTER.SYS_C0014143) violated-parent key not found

3. Examples of mutual exclusion between TM locks from mode 2-6

Introduce some actions:

The following example demonstrates verifying the contents of the above table

Row Share (RS)

Also called a subshare table lock (SS)

Session1 session_id=1:

SQL > create table tun2_tab (x int)

Tablecreated.

SQL > lock table tun2_tab in ROW SHARE mode nowait

Table (s) Locked.

Session2 session_id=30:

SQL > lock table tun2_tab in ROW SHARE mode

Table (s) Locked.

SQL > commit

Commit complete.

SQL > lock table tun2_tab in ROW EXCLUSIVE mode

Table (s) Locked.

SQL > commit

Commit complete.

SQL > lock table tun2_tab in SHARE MODE

Table (s) Locked.

SQL > commit

Commit complete.

SQL > lock table tun2_tab in SHARE ROW EXCLUSIVE MODE

Table (s) Locked.

SQL > commit

Commit complete.

SQL > lock table tun2_tab in EXCLUSIVE MODE

Waiting...

Take a look at the lock

SQL > select l.sidrecoveryl.typerecoverl.id1reportl.requestrewagel.blockjold d.objectwriting name from v$lock lwrence lockedwriting object orelessobjects d

2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM',' TX')

3 order by 1

SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME

1 TM 89479 2 0 1 TUN2_TAB

30 TM 89479 0 6 0 TUN2_TAB

RS | SS locks and X locks are not concurrent, but are compatible with other types of locks.

Row Exclusive TableLock (RX | SX)

Also called a subexclusive table lock (SX)

Session1 session_id=1

SQL > lock table tun2_tab in ROW EXCLUSIVE mode

Table (s) Locked.

Session2 session_id=30

SQL > lock table tun2_tab in ROW SHARE mode

Table (s) Locked.

SQL > commit

Commitcomplete.

SQL > lock table tun2_tab in ROW EXCLUSIVE mode

Table (s) Locked.

SQL > commit

Commitcomplete.

SQL > lock table tun2_tab in SHARE MODE

Waiting...

Take a look at the lock

SQL > /

SID TY ID1 LMODE REQUEST BLOCK

--

35 TM 76917 3 0 1

160 TM 76917 0 4 0

RX | SX and S locks cannot be concurrent, and SRX | SSX locks cannot be concurrent with RX | SX locks after testing.

Share Table Lock (S)

Session1 session_id=1:

SQL > lock table tun2_tab in SHARE MODE

Table (s) Locked.

Session2 session_id=30

SQL > lock table tun2_tab in ROW SHARE mode

Table (s) Locked.

SQL > commit

Commitcomplete.

SQL > lock table tun2_tab in ROW EXCLUSIVE mode

Waiting...

Lock condition:

SQL > /

SID TY ID1 LMODE REQUEST BLOCK

--

35 TM 76917 4 0 1

160 TM 76917 0 3 0

The S lock is special. It does not allow concurrency with RX | SX, that is, level 3 locks, but allows multiple S locks to be held concurrently in multiple transactions.

For example, two sessoin execute the following command simultaneously

Lock table tun2_tab in SHARE MODE

You can see the following lock information:

SQL > /

SID TY ID1 LMODE REQUEST BLOCK

--

35 TM 76917 4 0 0

129 TM 76917 4 0 0

However, S locks cannot be concurrent with SRX | SSX and X locks.

Share Row ExclusiveTable Lock (SRX | SSX)

Also called a share-subexclusive table lock (SSX)

Session1 session_id=1:

SQL > lock table tun2_tab in SHARE ROW EXCLUSIVE MODE

Table (s) Locked.

Session2 session_id=129:

SQL > lock table tun2_tab in ROW SHARE mode

Table (s) Locked.

SQL > commit

Commitcomplete.

SQL > lock table tun2_tab in ROW EXCLUSIVE mode

Waiting...

Lock condition:

SQL > @ lock

SID TY ID1 LMODE REQUEST BLOCK

--

35 TM 76917 5 0 1

129 TM 76917 0 3 0

SRX | SSX locks cannot be held concurrently with locks above RX | SX.

Exclusive Table Lock (X)

Session1 session_id=1:

SQL > lock table tun2_tab in EXCLUSIVE MODE

Table (s) Locked.

Session2 session_id=129:

SQL > lock table tun2_tab in ROW SHARE mode

Waiting...

The X lock cannot be concurrent with any lock.

4. An example of SQL that causes a deadlock.

Here is the simplest example

SQL > create table a (x int)

Table created.

SQL > create table b (x int)

Table created.

SQL > insert into a values (1)

1 row created.

SQL > insert into a values (2)

1 row created.

SQL > insert into b values (1)

1 row created.

SQL > insert into b values (2)

1 row created.

SQL > commit

Commit complete.

SQL > select * from a

X

-

one

two

SQL > select * from b

X

-

one

two

S1 t1:

SQL > update b set Xero3 where x = 1

1 row updated.

S2 t2:

SQL > update a set Xero3 where Xero1

1 row updated.

S1 t3:

SQL > update a set Xero5 where x = 1

S2 t4:

SQL > update b set Xbox 5 where Xuan 1

S1 t5:

SQL > update a set Xero5 where x = 1

Update aset Xero5 where x = 1

ERROR atline 1:

ORA-00060:deadlock detected while waiting for resource

Raise error00600 deadlock

SQL > select * from b

X

-

three

two

S2 t6:

Still waiting

Until S1 ends the transaction

You can see here that the deadlock caused by a logic error lock. Both transactions are waiting for each other to release lock resources.

The deadlock statement caused by the first scramble for resources will be canceled (just cancel this statement, not end the entire transaction)

After reading the above, have you mastered the lock mechanism in Oracle and the method of experimental analysis? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.