Shulou(Shulou.com)06/01 Report--

Information about locks extracted in 11g Concepts

Table Locks (TM)

A table lock, also called a TM lock, is acquired by a transaction when a table is modified by an INSERT, UPDATE, DELETE, MERGE, SELECT with the FOR UPDATE clause, or LOCK TABLE statement. DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction.

When a transaction modifies a table through INSERT, UPDATE, DELETE, MERGE, and FOR UPDATE, it acquires a table lock, also known as a TM lock clause, or a lock table statement. DML operations require table locks to retain DML access to the table for transactions and to prevent operations where DDL conflicts with transactions.

A table lock can be held in any of the following modes:

Row Share (RS)

This lock, also called a subshare table lock (SS), indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. A row share lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table.

This lock, also known as a child shared table lock (SS), indicates that the transaction holding the lock on the table has locked the rows in the table and intends to lock and update them. Row sharing lock is the least restrictive mode in table locks, which provides the highest degree of concurrency for tables.

Row Exclusive Table Lock (RX)

This lock, also called a subexclusive table lock (SX), generally indicates that the transaction holding the lock has updated table rows or issued SELECT... FOR UPDATE. An SX lock allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, SX locks allow multiple transactions to obtain simultaneous SX and subshare table locks for the same table.

This lock, also known as subexclusive table lock (SX), usually indicates that the transaction holding the lock has updated the table row or issued a SELECT. FOR UPDATE . SX locks allow other transactions to query, insert, update, delete, or lock rows simultaneously in the same table. Therefore, SX locks allow many transactions to acquire synchronized SX and child shared table locks for the same table.

Share Table Lock (S)

A share table lock held by a transaction allows other transactions to query the table (without using SELECT... FOR UPDATE), but updates are allowed only if a single transaction holds the share table lock. Because multiple transactions may hold a share table lock concurrently, holding this lock is not sufficient to ensure that a transaction can modify the table.

Shared table locks held by transactions allow other transactions to query tables (except for SELECT. FOR UPDATE), but only if a transaction holds a shared table lock. Because multiple transactions may hold a shared table lock at the same time, holding this lock is not sufficient to ensure that the transaction can modify the table.

Share Row Exclusive Table Lock (SRX)

This lock, also called a share-subexclusive table lock (SSX), is more restrictive than a share table lock. Only one transaction at a time can acquire an SSX lock on a given table. An SSX lock held by a transaction allows other transactions to query the table (except for SELECT... FOR UPDATE) but not to update the table.

This lock, also known as a shared-subexclusive table lock (SSX), has more restrictions than a shared table lock. Only one transaction SSX can be obtained at a time to lock a given table. The SSX lock held by the transaction allows other transactions to query the table (except SELECT … FOR UPDATE), but does not update the table.

Exclusive Table Lock (X)

This lock is the most restrictive, prohibiting other transactions from performing any type of DML statement or placing any type of lock on the table.

This lock is the strictest, preventing other transactions from executing any type of DML statement or placing any type of lock on the table.

Because ORACLE has to deal with different concurrency functions, once it can not handle so much concurrency, it needs to queue. In order to ensure the fairness of queuing, there will be a variety of priorities, so many lock patterns are derived to support the concurrency requirements of different business layers.

In the same session, you execute a UPDATE statement with a DML lock on the table, can you do the DDL statement yourself, such as DROP?

Because it is the same session, it does not involve concurrency. If you make your own update and do not submit it, then drop table is also possible.

Row locks: 0,6 locks

Table locks: 0, 1, 2, 3, 4, 5, 6 seven locks

0 (none)

1 (null)

2 (RS)

3 (RX)

4 (S)

5 (SRX)

6 (X)

R is ROW line, S is SHARE sharing, X is eXclusive exclusive, exclusive lock

0:null null

A general SELECT has a level 0 lock on both tables and rows

1:null null

Level 1 locks are: Select sometimes appears in v$locked_object.

2:Row-S row sharing (RS): shared table lock, sub share

Level 2 locks are: Lock Row Share,create index online

In the case of table lock

Locked_mode 2 does not affect the session of the latter locked_mode 2, 3, 4, 5, and if the latter session locked_mode is 6, the latter session operation prompts an ora-00054 error.

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

In the case of row lock

Locked_mode 2 corresponds to a row lock level 0 lock, which does not affect other sessions.

3:Row-X line monopoly (RX): for line modification, sub exclusive

Level 3 locks are: Insert, Update, Delete, Select for update,Lock Row Exclusive

In the case of table lock

Locked_mode 3 does not affect the session of the latter locked_mode 3, but if the locked_mode of the latter session is 4pm 5pm 6, then the latter session operation will prompt an ora-00054 error.

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

In the case of row lock

The table lock of locked_mode 3 corresponds to the row lock level 6, and two sessions affect the same row.

4:Share shared lock (S): block other DML operations, share

Level 4 locks are: Create Index, Lock Share

5:S/Row-X shared row exclusive (SRX): block other transaction operations, share/sub exclusive

Level 5 locks are: Lock Share Row Exclusive

Specifically, when there is a primary foreign key constraint, update/delete...; a lock of 4 and 5 may be generated.

6:exclusive exclusive (X): for independent access, exclusive

Level 6 locks are: Drop table, Drop Index, Alter table,Truncate table, Lock Exclusive

Refer to Maclean's lecture and use a jewelry store as an analogy.

The jewelry store can give you a free visit, you can book it, you can buy it after a trial, and you can buy it all.

Category 0, people who visit the jewelry store for free

The first category is the old, weak and pregnant guests who visit the jewelry store for free.

The second category of people, booked a probation period, buy it for a few days first, and then buy it if you feel good after the trial.

In the third category, the purpose of going directly to the store is to buy immediately.

The fourth category of people wrap up the jewelry of the whole store for others to visit and book, but cannot buy or sell (this is called a read-only lock in ORACLE, only others are allowed to read, that is, people of category 0,1 are only allowed to come to the jewelry store, let others visit the jewelry store in a read-only way, and buying and selling are not allowed, and a fourth kind of person is still allowed, because although everyone wants to pack, but everyone's goal is to share, not monopolize. So it is compatible)

Type 5 people, there is only one difference between category 5 people and type 4 people, that is, after type 5 people take out the whole jewelry store, another type 5 people are no longer allowed to do so (this is called write locking in ORACLE), that is, type 5 people are single-aisle. You can only find one category 5 person in the jewelry store, but it is impossible to find the second category 5 person, but after category 5 people take out the jewelry store, they can still let the number 0, 1. Two types of people visit, but trading is not allowed.

The sixth category of people, it put the whole jewelry store down, do not allow anyone to visit purposefully, only allow free visit, it is exclusive, only allow 0BI people to visit, other people are not allowed

-- booked by category 2 above, so category 3 is not compatible with category 6

-- the above category 3 people want to buy jewelry, so category 3 is not compatible with category 4, 5, and 6.

Treat the jewelry store as a watch and the jewelry cabinet in the jewelry store as a business.

Jewelry store, 7 kinds of people correspond to 7 kinds of modes, corresponding to 7 kinds of locks of table, 0, 1, 2, 3, 4, 5, 6

Cabinet, open or close two states corresponding to two modes, corresponding to the row of two kinds of locks, 0,6

Jewelry store

(can you enter the store at the same time, yes?)

The watch lock is equivalent to the big door lock of the jewelry store, which is guarded by the doorman. The watch lock has 0Magi 1, 3, 4, 5, 6 corresponding to 7 groups of people, and 7 groups of people can enter the store at the same time. For example, 0, 1, 2, 3 people come in at the same time, or 3 kinds of people come in at the same time.

Level 0 lock: there are no locks, only pure select statements

Category 0: free visit, no competition with other customers

Level 1 lock: actually does not play the role of locking, it just has a notification function, which can not stop DDL at all, similar to notifying the object in the execution plan to the session to which the object belongs.

Category 1: (old, weak, sick and disabled) visit for free, do not have any competition with other customers, but this customer has the right to know what the store will do in the future, such as whether it has been demolished.

For example, session An executes select * from T, and then saves the execution plan to memory. In order to ensure that the execution plan is correct, session A should enjoy the treatment of old, weak, sick, pregnant and young, because if the T table is deleted by someone else, is the execution plan generated by session A still useful? If you do not notify, how does A know that the table object has expired, that is, the object with lock No. 1? once deleted, it will notify the session that owns the object, and the object has been deleted. Please re-analyze that your SQL,1 number lock is automatically generated by the system.

Level 2 table lock: only conflicts with X, because the others are shared locks. Although RX,SRX also has X, it is the X of the row, which still means shared on the table. Level 2 locks do not conflict with levels 0-5 at the table level.

Category 2: people who intend to buy jewelry, but now just come to see if the goods are worth buying, so to open the counter, it's just a SELECT action. Will not directly conflict with customers who have free visits and have business intentions.

The generation mode of level 2 table lock

Explicitly generate table-level locks (LOCK TABLE table IN ROW SHARE MODE, explicitly generate a RS table-level lock)

Note that explicitly generating table-level locks produces only table-level locks, not cascading row-level locks, so row locks are not generated with other sessions

Level 3 lock: cause (update, delete, select for update, display lock table LOCK TABLE table IN ROW EXCLUSIVE MODE)

Three types of people: people who buy jewelry directly, so open the counter

The X of No. 6 is an exclusive lock at the entire table level, showing the lock table LOCK TABLE table IN Exclusive MODE

Jewelry cabinet

(can you open the same cabinet at the same time? no, there is no such concept.)

The row lock is equivalent to the jewelry store counter lock, which is guarded by the salesperson. The row lock is closed and opened in two states corresponding to 0 and 6 counter.

Usually, if customers enter a jewelry store, what are the purposes of running to the counter?

Visit

Cabinet status is closed: mode 0

Only for customers who want to visit (category 0, type 1), there is no problem of resource competition, so do you still need the salesperson to take out the lock and open the counter? No, because there is no resource competition, there is no need for locks.

The row-level lock of 0 mode is caused by the table-level lock of 0 and 1. The simple select statement is both 0-level table-level lock and 0-level row-level lock, that is, no lock.

Purchase

Cabinet status is open: mode 6

Category 2, probationary period (no one else can use it during the probation period)

Category 3, buy immediately (equivalent to our update, delete, select for update, LOCK TABLE table IN ROW EXCLUSIVE MODE statements)

Summary: update, delete, and select for update all generate exclusive locks on the line.

Shared locks will allow other shared locks to exist, that is, there is no conflict between sharing and sharing.

For example, if user An executes the first row of UPDATE on table T, then there is a table-level shared lock on table t, and user B executes the second row of UPDATE on table T, then there will also be a table-level shared lock on table t. Although the rows are exclusive locks, they are not the same row, so they have no conflict on the row and there is no conflict on the table.

For example, user An executes LOCK TABLE T IN ROW EXCLUSIVE MODE, and user B can execute LOCK TABLE T IN ROW EXCLUSIVE MODE or LOCK TABLE T IN ROW SHARE MODE at the same time

There are row-level locks, there must be table-level locks (level 3 table locks cause level 6 row locks)

There is a table-level lock, but there can be no row-level lock (explicit lock, table-level lock corresponding to 2pyrm no. 6 display lock)

The row-level lock of mode 6 is caused by the table-level lock of No. 2 and 3.

The lock of ORACLE is placed in the block of DATABASE BUFFER and LIBRARY CACHE and does not take up other memory. In other db2 and informix, locks take up memory, so db2 row locks are often upgraded to table locks.

The type of lock can be divided into three categories according to the object of the lock.

DML lock

DDL lock

Internal lock or LATCH

DML and DDL involve visible SCHEMA objects

DML is our DELETE,UPDATE,INSERT statement, which operates on tables, views, etc., and is a visible SCHEMA object.

DDL statements are ALTER TABLE,CREATE TABLE statements, etc. The same objects are tables, views, stored procedures, etc., and are also visible SCHEMA objects

Internal lock or LATCH

Users can not see, can not see what are encapsulated objects, that is, internal locks (LIBRARY CACHE,DATABASE BUFFER), because these objects are shared, shared objects are related to resource competition, so we must use locks to restrict access to resources. For low-level locks to protect memory, we call it latch, its mechanism is similar to traffic lights, a road is public, we have to set traffic lights. If it is private, then there is no need to set traffic lights, so PGA does not have latch.

DML is a data maintenance lock

DML locks are used to control data accessed by multiple users in parallel to ensure consistency. SELECT does not have any locks, only select for update has locks.

Select...for update locks the result row, causing other session to fail to update

DML locks ensure that data is modified during one transaction and that other transactions are not allowed to modify it

The DML lock ensures that other transactions are not allowed to DDL on the table when the transaction of the modified table has not finished.

(of course, this user's current session does not submit a table update, and this user's current session can directly ddl the table. This user cannot ddl the table if he opens a new session, and other users cannot ddl the table.)

DML locking is divided by object level:

Table-level lock TM (acts on table objects, Table Manager)

Row-level lock TX (acting on row objects, Transaction eXclusive)

ORACLE will not upgrade row-level locks to table-level locks.

This is like a courtyard, where four doors form a courtyard. The door of the courtyard is a watch lock, and each room is a row lock.

If in the sqlserver database, when there are three doors to lock, then I directly lock the door, because the sqlserver database, the lock key is very valuable, in order to save the key, there is a lock upgrade, from row-level lock to page-level lock, and then from page-level lock to table-level lock.

View current session sid

SQL > select distinct sid from v$mystat

Query lock information for two sessions

SQL > select sid,id1,id2,type,lmode,request from v$lock where sid in (sid1,sid2) order by sid

Query the specific meaning of the lock type

SQL > select * from V$LOCK_TYPE where type in ('TX','AE','TM','TO','OD')

TYPE NAME ID1_TAG ID2_TAG IS_USE DESCRIPTION

-

TM DML object # table/partition YES Synchronizes accesses to an object

TX Transaction usn alter table test add hid3 number

According to the result of sid3 query, it is found that the table level locks of sid1 and sid2 are both 3.

SQL > select sid,id1,id2,type,lmode,request from v$lock where sid in (161189) order by sid

SID ID1 ID2 TY LMODE REQUEST

-

161 65547 1930 TX 6 0

161 88539 0 TM 3 0-the table level lock of sid1 is 3

161 100 0 AE 4 0

161 79833 1 TO 3 0

189 196612 2185 TX 6 0

189 88539 0 TM 3 0-the table level lock of sid2 is 3

189 100 0 AE 4 0

189 88539 0 OD 6 0

189 65547 1930 TX 0 4

189 79833 1 TO 3 0

SQL > select sid,FINAL_BLOCKING_SESSION,event from v$session where state='WAITING' and FINAL_BLOCKING_SESSION_STATUS='VALID'

SID FINAL_BLOCKING_SESSION EVENT

189161 enq: TX-row lock contention

Case 2

The sid of session 1 is 161and the sid of session 2 is 189,

Sid1, no commit.

SQL > update test set id=11

1 row updated

Sid2, report the error directly.

SQL > drop table test

Drop table test

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

After sid3 modifies ddl, sid2 executes it again, and sid queries the result.

SQL > alter system set ddl_lock_timeout=60

SQL > select sid,id1,id2,type,lmode,request from v$lock where sid in (161189) order by sid

SID ID1 ID2 TY LMODE REQUEST

-

161 88539 0 TM 3 0-the table level lock of sid1 is 3

161 100 0 AE 4 0

161 79833 1 TO 3 0

161 458768 1934 TX 6 0

189 88539 0 TM 0 6-sid2 currently has a table level lock of 0, but requests a table level lock of 6

189 100 0 AE 4 0

189 0 1 AE 4 0

189 79833 1 TO 3 0

SQL > select sid,FINAL_BLOCKING_SESSION,event from v$session where state='WAITING' and FINAL_BLOCKING_SESSION_STATUS='VALID'

SID FINAL_BLOCKING_SESSION EVENT

-

189161 enq: TM-contention

CREATE INDEX ONLINE

Will create index online clog update?

No.

Execute update first and then do not commit, and then execute create index online will not report an error, but create index online has been in a blocking state

Execute create index online first, then execute update normal update, but if update does not commit, create index online has been blocked.

Understand: create index online in the process of creating an index row by row, does not mean that this row has created an index, and then update this row must wait until all the rows are create index online completed before the normal udpate, that is to say, no matter whether the update is before or after the create index online, create index online does not affect update, but if update does not commit it will affect create index online.

The sid of the following two experimental sessions 1 is 161and the sid of session 2 is 189,

Experiment 1, first execute create index online, and after halfway through creation, the line of the smallest rowid of update, in theory, create index online should have passed this line and should block the update session. In fact, there is no blocking, as fast as update. When the query finally comes down, it is found that update has blocked the create index online.

Sid1 execution

SQL > select object_id from test1 where rowid in (select min (rowid) from test1)

OBJECT_ID

-

4559

Sid2 execution, which takes 6 seconds to create normally

SQL > create index ind_obd on test1 (OBJECT_ID) online

Index created.

Elapsed: 00:00:06.06

SQL > drop index ind_obd

Index dropped.

Elapsed: 00:00:00.14

SQL > create index ind_obd on test1 (OBJECT_ID) online

During the 6 seconds of sid2 execution, it was immediately executed in sid1, and it was found that sid1 execution was very fast and not blocked.

SQL > update test1 set object_id=1 where OBJECT_ID=4559

32 rows updated.

The sid3 executes as follows and finds that sid1 161blocks sid2 189s

SQL > select sid,FINAL_BLOCKING_SESSION,event from v$session where state='WAITING' and FINAL_BLOCKING_SESSION_STATUS='VALID'

SID FINAL_BLOCKING_SESSION EVENT

189161 enq: TX-row lock contention

SQL > select sid,id1,id2,type,lmode,request from v$lock where sid in (161189) order by sid

SID ID1 ID2 TY LMODE REQUEST

-

161 79833 1 TO 3 0

161 262151 1938 TX 6 0

161 88544 0 TM 3 0

161 100 0 AE 4 0

189 100 0 AE 4 0

189 79833 1 TO 3 0

189 131075 2139 TX 6 0

189 88544 0 DL 3 0

189 262151 1938 TX 0 4

189 88552 0 TM 4 0

189 88544 0 DL 3 0

189 88544 0 OD 4 0

189 88544 0 TM 2 0

13 rows selected.

Experiment 2, first execute create index online, and after halfway through creation, the line of the largest rowid of update, in theory, create index online should not have reached this line and will not block the update session. The experiment also found that this is true. Update is very fast. When the query comes down at last, it is update that blocks create index online.

Sid1 execution

SQL > select object_id from test1 where rowid in (select max (rowid) from test1)

OBJECT_ID

-

85998

Sid2 execution, which takes 6 seconds to create normally

SQL > create index ind_obd on test1 (OBJECT_ID) online

Index created.

Elapsed: 00:00:06.06

SQL > drop index ind_obd

Index dropped.

Elapsed: 00:00:00.14

SQL > create index ind_obd on test1 (OBJECT_ID) online

During the 6 seconds of sid2 execution, it was immediately executed in sid1, and it was found that sid1 execution was very fast and not blocked.

SQL > update test1 set object_id=1 where OBJECT_ID=85998

32 rows updated.

The sid3 executes as follows and finds that sid1 161blocks sid2 189s

SQL > select sid,FINAL_BLOCKING_SESSION,event from v$session where state='WAITING' and FINAL_BLOCKING_SESSION_STATUS='VALID'

SID FINAL_BLOCKING_SESSION EVENT

189161 enq: TX-row lock contention

SQL > select sid,id1,id2,type,lmode,request from v$lock where sid in (161189) order by sid

SID ID1 ID2 TY LMODE REQUEST

-

161 79833 1 TO 3 0

161 88544 0 TM 3 0

161 393242 2315 TX 6 0

161 100 0 AE 4 0

189 79833 1 TO 3 0

189 88544 0 TM 2 0

189 88546 0 TM 4 0

189 458777 1936 TX 6 0

189 100 0 AE 4 0

189 88544 0 DL 3 0

189 88544 0 DL 3 0

189 393242 2315 TX 0 4

189 88544 0 OD 4 0

13 rows selected.

Query which table and which row of SQL the lock object is

First find out the SID of the blocked session, and then query which table is blocked and which row is blocked

Select a.sid, a. Rowboy waitworthy objures, a. Rowboys waitworthy fileholders, a. Rowboys waitpieces blockblocks, a. Rowboys waitpieces rowstones journal b.ownercogne b.objectbrush name from v$session a dbathing objects b where a.row_wait_obj#=b.object_id and sid in (XX)

Select sid, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where sid in (XX);-- the query to row_wait_obj#=-1 indicates that it is a session holding a lock

Which object is the line that row_wait_obj#: is waiting for?

Which file is the waiting line for row_wait_file#: on?

Which block is the line that row_wait_block#: is waiting for?

Where is the line that row_wait_row#: is waiting for?

Locks encountered in statistics collection

DBMS_STATS: GATHER_STATS_JOB encountered errors

ORA-04021: timeout occurred while waiting to lock object

When collecting statistics, you need to lock the definition of the table or index. In fact, the lock here is library cache lock/pin~.

Instead of locking this object, when collecting statistics about an object, it is found that the required object has been locked by another session, and after waiting for a certain period of time, the other session still does not release the lock that already holds the object, causing the statistics session to fail to get the lock for this object.

Collecting statistics will hold the library cache lock of X mode (representation in the table in library cache), so there will be locks, but not the enqueue locks we usually understand.

There will be conflicts / blocking when other users need to apply for the library cache lock of S mode's table library cache object when parsing the SQL that uses this table.

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.