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

This article focuses on "how to verify the FIFO mechanism of LOCK requests". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to verify the FIFO mechanism of LOCK requests.

In a multi-concurrent session scenario, you need to obtain the X lock of the resource before you can modify the resource. At the same time, when other sessions also request to modify the resource, they must queue up in the request queue in a first-in-first-served way (FIFO). The following experiments can verify this mechanism.

Create a tabl

Create table T1 (C1 number, c2 number)

Insert into T1 values (101,5000)

Commit

The three sessions attempt to update the data in order, and the SID of the session can be obtained through the following query

Select userenv ('sid') from dual

Session1 (SID:129)

Update T1 set c2 "6000 where C1 101"

Session2 (SID:135)

Update T1 set c2 "7000 where C1" 101

Session3 (SID:138)

Update T1 set c2m 8000 where c1m 101

As you can see, both Session2 and Session3 are waiting after Session1 performs the update because it is not committed. At this point, the transaction information is queried, showing that there is a transaction.

Select xid, addr, ses_addr, xidusn, xidslot, xidsqn, ubafil, ubablk, ubasqn, ubarec, status, start_time from v$transaction

XID ADDR SES_ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_TIME

0100060090020000 000007FF49D24CA8 000007FF4C78B2A0 1 6 656 3 717 145 35 ACTIVE 10-04-17 11:44:47

Check the lock information. The session with the transaction rollback segment number XIDUSN indicates the transaction being executed. The session takes priority to acquire the lock. A XIDUSN of 0 indicates that the session is locked and is in a waiting state. A LOCKED_MODE of 3 indicates a row exclusive lock.

Col oracle_username for a15

Select * from v$locked_object

XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE

- --

1 6 656 73449 129 SYS VM-ORA11G-1\ Administrator 3136 purl 1796 3

0 73449 135 SYS VM-ORA11G-1\ Administrator 2932 purl 2184 3

00 0 73449 138 SYS VM-ORA11G-1\ Administrator 3820 purl 2100 3

If you look at the lock queue, 129sessions have acquired the lock, so only AE session lock, no TX lock request, CTIME is the request time, 135session precedes 138session, REQUEST greater than 0 indicates that the current session is blocked, and other sessions occupy the lock in the corresponding mode.

Select * from v$enqueue_lock where sid in (129,135,138)

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

-

000007FF4CC56968 000007FF4CC569C0 129 AE 100 0 4 0 6898 0

000007FF4CC56BD8 000007FF4CC56C30 135 AE 100 0 4 0 3183 0

000007FF4CC56E48 000007FF4CC56EA0 135 TX 65542 656 0 6 3152 1

000007FF4CC56F18 000007FF4CC56F70 138 AE 100 0 40 3140 0

000007FF4CC57000 000007FF4CC57058 138 TX 65542 656 06 3106 0

Definition of lock type TYPE column: TM- table level lock, TX- transaction lock, MR-Media Recovery (one per file), AE- session lock (one per session), UL- user-defined lock type

Definition of lock mode LMODE column: 0murnoneshare 1-null (NULL); 2-row sharing (RS); 3-row exclusive (RX); 4-shared lock (S); 5-shared row exclusive (SRX); 6-exclusive lock (X)

Dump waiting queue. It can be seen that 129session que is own, indicating that the lock is being held. 135session and 138session que are wat indicating that the lock request is waiting.

Oradebug setmypid

Oradebug tracefile_name

Oradebug dump enqueues 8

000007FF4CD05DC0 TX-00010006-00000290 U 00000 1 40 [4cd25530 recorder 4cd25530]

[49d24d30,49d24d30] [4cd05df0,4cd05df0] [4cc56eb0,4cc57068]

Lock que owner session hold wait ser link

000007FF49D24D20 OWN 000007FF4C78B2A0 000007FF4C78B2A0 (129) X NLCK 73 [4cd05dd0meme4cd05dd0]

000007FF4CC56EA0 WAT 000007FF4C779C00 000007FF4C779C00 NLCK X 1468 [4cc57068pr 4cd05de0]

000007FF4CC57058 WAT 000007FF4C7710B0 000007FF4C7710B0 (138C) NLCK X 1191 [4cd05de0meme4cc56eb0]

Against validation, commit Session1, view transactions, new transactions are being executed

Select xid, addr, ses_addr, xidusn, xidslot, xidsqn, ubafil, ubablk, ubasqn, ubarec, status, start_time from v$transaction

XID ADDR SES_ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_TIME

050006005D030000 000007FF49D256A8 000007FF4C779C00 56 861 3 536 187 14 ACTIVE 10-04-17 11:45:36

Looking at the locked object, the lock for 129 sessions has been released, so there is no record, 135 sessions are performing transactions and acquire locks, 138 sessions continue to wait

Col oracle_username for a15

Select * from v$locked_object

XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE

- --

5 6 861 73449 73449 SYS VM-ORA11G-1\ Administrator 2932 purl 2184 3

00 0 73449 138 SYS VM-ORA11G-1\ Administrator 3820 purl 2100 3

Check the request queue. 129sessions and 135sessions have obtained requests, 138sessions still have TX lock requests.

Select * from v$enqueue_lock where sid in (129,135,138)

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK

000007FF4CC56968 000007FF4CC569C0 129 AE 100 0 4 0 21793 0

000007FF4CC56BD8 000007FF4CC56C30 135 AE 100 0 4 0 18078 0

000007FF4CC56E48 000007FF4CC56EA0 138 TX 327686 861 0 6 674 0

000007FF4CC56F18 000007FF4CC56F70 138 AE 100 0 4 0 18035 0

Dump waiting queue. 135session que: own indicates holding lock. 138session que: wat indicates waiting for lock request.

Oradebug setmypid

Oradebug tracefile_name

Oradebug dump enqueues 8

000007FF4CD07F98 TX-00050006-0000035d U 00000 1 40 [4cd25bc0rec 4cd25bc0]

[49d25730,49d25730] [4cd07fc8,4cd07fc8] [4cc56eb0,4cc56eb0]

Lock que owner session hold wait ser link

000007FF49D25720 OWN 000007FF4C779C00 000007FF4C779C00 X NLCK 1468 [4cd07fa8 record4cd07fa8]

000007FF4CC56EA0 WAT 000007FF4C7710B0 000007FF4C7710B0 [4cd07fb8] NLCK X 1191 [4cd07fb8]

Against validation, commit the Session2, view the transaction, and a new transaction is executed

Select xid, addr, ses_addr, xidusn, xidslot, xidsqn, ubafil, ubablk, ubasqn, ubarec, status, start_time from v$transaction

XID ADDR SES_ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_TIME

09000D003D030000 000007FF49CC9678 000007FF4C7710B0 9 13 829 3 2477 184 28 ACTIVE 10-04-17 11:46:22

Check the locked object, the lock for the 135 session has also been released, the 138 session is performing a transaction and the lock is obtained, and no session is waiting

Col oracle_username for a15

Select * from v$locked_object

XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE

- --

9 13 829 73449 73449 SYS VM-ORA11G-1\ Administrator 3820 purl 2100 3

Check the request queue. There are no TX lock requests.

Select * from v$enqueue_lock where sid in (129,135,138)

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

-

000007FF4CC56968 000007FF4CC569C0 129 AE 100 0 4 0 24483 0

000007FF4CC56BD8 000007FF4CC56C30 135 AE 100 0 4 0 20768 0

000007FF4CC56F18 000007FF4CC56F70 138 AE 100 0 4 0 20725 0

Dump waiting queue, only 138sessions have been recorded, and que for own indicates that it already has

Oradebug setmypid

Oradebug tracefile_name

Oradebug dump enqueues 8

000007FF4CD00378 TX-0009000d-0000033d U 00000 1 40 [4cd23730,4cd23730]

[49cc9700,49cc9700] [4cd003a8,4cd003a8] [4cd00398,4cd00398]

Lock que owner session hold wait ser link

000007FF49CC96F0 OWN 000007FF4C7710B0 000007FF4C7710B0 (138) X NLCK 1191 [4cd00388]

If the committed Session1 and Session2 sessions are closed at this time, there will be no more AE lock records for 129,135 sessions in the request queue, one for each session in Oracle 11g, and no AE session lock for Oracle 10g

Select * from v$enqueue_lock where sid in (129,135,138)

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

-

000007FF4CC56F18 000007FF4CC56F70 138 AE 100 0 4 0 21120 0

Therefore, we verify the FIFO mechanism of lock queue through experiments, following the principle of "request first, service first".

Finally, clean up the experimental table

Drop table t1 purge

At this point, I believe you have a deeper understanding of "how to verify the FIFO mechanism of LOCK requests". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.