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

This article mainly introduces "Oracle and MySQL intentional lock?" In daily operation, I believe many people have doubts about Oracle and MySQL intentional lock. Xiaobian consulted all kinds of information and sorted out simple and easy to use operation methods. I hope to help you answer the question of "Oracle and MySQL intentional lock?" Next, please follow the small series to learn together!

Classification of locks:

0, None

NULL, can be in some cases, such as distributed database queries will produce this lock.

2. SS, table structure shared lock

SX, Table Structure Shared Lock + Exclusive Lock of Operated Record (TM)

4, S, table structure shared lock + all records shared lock

5. SRX table structure shared lock + all records exclusive lock

6, X table structure exclusive lock + all records exclusive lock (TX)

The meaning of the five locks on the table:

RS(SS): is a purely intentional lock that characterizes transactions to access certain rows via SELECT FOR UPDATE;

RX(SX): is a purely intentional lock that indicates that the transaction wants to modify certain rows via UPDATE/ Delete/INSERT;

S: shared locks across the table, no identification required on each row;

X: Table-wide exclusive lock, no need to identify on each row;

SRX(SSX): is the combination of intent lock RX and S lock, it characterizes the transaction both on the table plus S lock, while to modify individual rows, that is, to add X lock on individual rows;(if you use ORACLE8, you can establish a self-reference on a table, such as EMP table MGR reference EMPNO, delete a record, you can observe SRX lock)

On the row,ORALCE has only X locks,

But the X lock acquired by SELECT FOR UPDATE is characterized at the table level as an RS lock,

X locks acquired by INSERT/UPDATE/Delete are characterized at the table level as RX locks

http://blog.itpub.net/29990276/viewspace-1979920/

SELECT /*+ no_merge(l) ,no_merge(t)*/

L.ADDR,

L.KADDR,

L.SID,

T.NAME,

L.ID1,

T.ID1_TAG,

L.ID2,

T.ID2_TAG,

L.TYPE,

L.LMODE,

CASE

WHEN LMODE = 0 THEN

'None'

WHEN LMODE = 1 THEN

'NULL, may occur in some cases, such as queries to distributed databases'

WHEN LMODE = 2 THEN

'SS, a table structure shared lock, is a purely intentional lock that characterizes a transaction to access certain rows via SELECT FOR UPDATE'

WHEN LMODE = 3 THEN

'SX, table structure shared lock + exclusive lock of operated record (TM), is a purely intentional lock that characterizes transactions to modify certain rows via UPDATE/ Delete/INSERT;'

WHEN LMODE = 4 THEN

'S, table structure shared lock + all records shared lock'

WHEN LMODE = 5 THEN

'SRX table structure shared lock + all records exclusive lock, table structure exclusive lock + all records exclusive lock (TX)'

WHEN LMODE = 6 THEN

'X Table Structure Exclusive Lock + All Records Exclusive Lock (TX)'

END AS "meaning",

L.CTIME,

T.DESCRIPTION

FROM V$LOCK L, V$LOCK_TYPE T

WHERE L.TYPE = T.TYPE

AND L.LMODE IN (0, 1, 2, 3, 4, 5, 6)

ORDER BY L.LMODE;

MySQL intent lock with IS and IX, he is layer by layer from the database layer--> table--> page-->record lock, he is also characterized by the type of the next level of lock, and IX IS mutual and between themselves is not blocked, just to ensure that a certain physical structure of the database appears.

SELECT

r.trx_id waiting_trx_id,

r.trx_mysql_thread_id waiting_thread,

r.trx_query waiting_query,

b.trx_id locking_trx_id,

b.trx_mysql_thread_id blocking_thread,

b.trx_query blocking_query

FROM information_schema.INNODB_LOCK_WAITS w

INNER JOIN information_schema.innodb_trx b

ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.innodb_trx r

ON r.trx_id = w.requesting_trx_id;

At this point, the study of "Is there an intentional lock in Oracle and MySQL" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

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.