Oracle Locks some knowledge

Table-level locks have a total of five modes, as shown below.

Row-level exclusive lock (row Exclusive, short rx Lock)

When we do DML, we automatically add the RX lock on the table being updated, or you can explicitly add the RX lock on the table by executing the lock command. In this lock mode, other transactions are allowed to modify other rows of data in the same table through DML statements, or the lock command adds RX locks to the same tables, but does not allow other transactions to add exclusive locks (x locks) to the same table.

row-level shared lock (row shared, referred to as RS lock)

This is usually done through the SELECT ... from UPDATE statement, which is also the primary method we use to manually lock some records. For example, when we query some records, we do not want other users to update the records of the query, you can issue such a statement. When the data is used, the direct issue of the rollback command unlocks the lock. When RS lock is added to the table, no other transactions are allowed to add exclusive locks to the same table, but other transactions are allowed to lock other rows of data in the same tables through the DML statement or the lock command.

shared Lock (Share, abbreviation S lock)

Add the S lock by using the lock table in share mode command. In this lockdown mode, no user is allowed to update the table. But allow other users to issue a select ... from the FOR UPDATE command to add RS locks to the table.

Exclusive Lock (Exclusive, abbreviation x Lock)

The x lock is added via the lock table in exclusive mode command. In this lockdown mode, other users cannot perform any DML and DDL operations on the table, which can only be queried.

shared row-level exclusive lock (Share row Exclusive, abbreviated as SRX Lock)

Add the SRX lock via the lock table in share row exclusive mode command. This locking mode is higher than the level of row-level exclusive and shared locks, and you cannot perform DML operations on the same table or add shared locks.

The compatibility relationship of TM locks in these five modes is shown in the table below (√ indicates mutually compatible requests; X indicates incompatible requests; N/a indicates no lock request):

Java code

1. -   s   x   rs  rx   srx n/a  
2. s   √   x   √    x   x   √  
3. x   x   x    x   x   x   √  
4. RS   √   x   √   √   √   √   
5. rx  x   x   √   √   x    √  
6. srx x   x   √   x    x   √  
7. n/a √   √   √    √   √   √  

As you can see from the previous description, we can not only automatically add TM locks at the table level by Oracle by issuing DML statements. We can also actively add TM locks at the table level by issuing the lock Table command, and in this command you can specify different locking modes with the following command format:

Java code

1. Lock table in [row Share][row exclusive]
2. [Share] [Share row Exclusive] [Exclusive] mode;

Summarize the table-level locks produced by each SQL statement in the Oracle database, as shown in the following table:

Java code

2. Table locking mode allowed for SQL statement table lock module
4. Select * FROM ... Rs RS, RX, S, SRX, X
6. Insert into ... Rx RS, Rx
8. Update ... Rx RS, Rx
10. Delete from ... Rx RS, Rx
12. Select * from for update Rs RS, RX, S, SRX
14. Lock table in row share mode Rs RS, RX, S, SRX
16. Lock table in row exclusive mode RX RS, rx
18. Lock table in Share mode s RS, s
20. Lock table in share row exclusive mode SRX RS
22. Lock table in exclusive mode X RS

For locks that are actively added through the Lock Table command, if you want to release them, you only need to issue the rollback command.


Types of locks for Oracle databases
Depending on the object being protected, the Oracle database lock can be divided into the following categories: DML lock (data locks, lock) to protect the integrity of the data, DDL locks (Dictionary locks, Dictionary locks) to protect the structure of database objects, such as tables, Structure definitions for indexes, internal locks and latches (internal locks and latches), protect the internal structure of the database.
The purpose of DML locks is to guarantee data integrity in case of concurrency, this article mainly discusses DML locks. In Oracle databases, DML locks mainly include TM and TX locks, where TM locks are called table-level locks, and TX locks are called transaction or row-level locks.
When Oracle executes DML statements, the system automatically requests a TM-type lock on the table to be operated on. When the TM lock is obtained, the system automatically requests the TX type of lock and resets the lock flag bit of the data row that is actually locked. In this way, checking the compatibility of the TX lock before the transaction is locked, it is necessary to check the compatibility of the TM lock mode and greatly improve the efficiency of the system. TM locks include SS, SX, S, X and other modes, which are represented in the database by 0-6. Different SQL operations produce different types of TM locks. As shown in table 1.
There is only an X lock (exclusive lock) on the data line. In an Oracle database, a TX lock is obtained when a transaction initiates a DML statement for the first time, and the lock remains until the transaction is committed or rolled back. When two or more sessions execute DML statements on the same record on a table, the first session is locked on that record, and the other sessions are waiting. When the first session is committed, the TX lock is released and other sessions can be locked.
When a TX lock wait occurs on an Oracle database, failure to handle it often causes the Oracle database to hang or cause a deadlock to occur, resulting in ORA-60 errors. These phenomena can cause great harm to the actual application, such as long time not responding, large number of transaction failures, etc.
Analysis of TX lock Waits
After describing the types of Oracle database locks in question, the following discusses how to effectively monitor and resolve lock waits, and how to locate deadlocks when a deadlock occurs.
The related view data dictionary of the monitoring lock is an important part of the Oracle database, and the user can query the data dictionary view to obtain the database information. The data dictionary view associated with the lock is shown in table 2.
TX Lock wait monitoring and resolution in daily work, if you find that the database is not responding for a long time when you execute a SQL, it is likely that a TX lock wait occurs. To solve this problem, you should first identify the locked transaction, and then do the related processing, such as committing a transaction or forcing the transaction to break.
Deadlock monitoring and resolution in a database, a deadlock occurs when two or more sessions request the same resource. Common types of deadlocks are row-level lock deadlocks and page-level lock deadlocks, and row-level locks are generally used in Oracle databases. The following main discussion is the deadlock phenomenon of row-level locks.
When Oracle detects a deadlock, the execution of the deadlock-related statement is interrupted and rolled back, and the ORA-00060 error is reported and recorded in the log file AlertSID.log of the database. At the same time, a trace file is generated under User_dump_dest, which describes the deadlock information in detail.
In daily work, if you find that the ora-00060 error message is logged in the log file, a deadlock is generated. At this point, you need to find the corresponding trace file, based on the information of the tracking file to locate the cause.
If the query results indicate that the deadlock is caused by the bitmap index, the deadlock problem can be resolved by changing the ind_t_product_his_state index to the normal index.

Java code

4. Table 1 TM Lock types for Oracle
6. Lock mode lock description Interpreting SQL operations
8. 0 None
10. 1 NULL NULL Select
12. 2 SS (row-s) row-level shared lock, other objects can only query these data rows
14. Select for Update, lock for update, lock row share
16. 3 SX (row-x) row-level exclusive lock, do not allow DML operations before committing
18. Insert, Update, Delete, Lock Row share
20. 4 S (Share) shared lock Create Index, lock Share
22. 5 SSX (s/row-x) shared row-level exclusive locks lock share row exclusive
24. 6 X (Exclusive) exclusive lock
26. Alter table, drop able, DROP index, Truncate table, Lock exclusive

Java code

2. Table 2 Data dictionary View description
4. View name Description Main field Description
6. V$session information about the query session and the lock.
8. sid,serial#: Represents the session information.
10. Program: Represents the application information for the session.
12. Row_wait_obj#: Represents the awaited object. correspond to the object_id in Dba_objects.
14. V$session_wait Query waiting session information.
16. Sid: Represents the session information that holds the lock.
18. Seconds_in_wait: Represents the time information waiting for the duration
20. Event: Represents the events that the session waits for.
22. V$lock lists all the locks in the system.
24. Sid: Represents the session information that holds the lock.
26. Type: Represents the kind of lock. Values include TM and TX, and so on.
28. ID1: The object ID that represents the lock.
30. Lmode,request: Information that represents the lock mode that the session waits for.  indicated by the number 0-6, corresponds to table 1.
32. Dba_locks a formatted view of the V$lock.
34. SESSION_ID: Corresponds to SID in V$lock.
36. Lock_type: Corresponds to the type in V$lock.
38. LOCK_ID1: Corresponds to ID1 in V$lock.
40. Mode_held,mode_requested: And V$lock
42. The lmode,request corresponds to the corresponding.
44. V$locked_object contains only the lock information for DML, including rollback segments and session information.
46. XIDUSN,XIDSLOT,XIDSQN: Indicates rollback segment information. And
48. V$transaction associated.
50. OBJECT_ID: Represents the identity of the locked object.
52. SESSION_ID: Represents the session information that holds the lock.
54. Locked_mode: Information that represents the lock mode that the session waits for, consistent with Lmode in V$lock.

=====================================================================
Category of content protected by lock
Oracle provides a multi-granularity blocking mechanism that can be divided into protected objects and
A:DML Lock, data locks Lock, to protect data integrity and consistency
B:ddl Lock, dictionary locks dictionary lock, used to protect the structure of the data object, such as the definition of Table,index
C: Internal lock and latch internal locks and latchs to protect the internal structure of the database, such as the SGA memory structure

DML lock
DML locks mainly include TM and TX locks, where TM locks are called table-level locks, and there are S,X,SR,SX,SRX five types of TM locks, and TX locks are called transaction or row-level locks. When Oracle executes the DELETE,UPDATE,INSERT,SELECT for UPDATE DML statement, Oracle first automatically requests a TM-type lock on the table to be operated on. When the TM lock is obtained, the lock of the TX type is automatically applied, and the lock flag bit (lb, lock bytes) of the data row that is actually locked is placed. When a record is locked by a session, other sessions that require access to the locked object wait for the lock to be released in a first-in, out-of-order manner, and for a select operation, no lock is required, so even if the record is locked, the SELECT statement can be executed, in fact, in this case, Oracle is implemented using the content of undo for consistent reads.

In an Oracle database, a TX lock is obtained when a transaction initiates a DML statement for the first time, and the lock remains until the transaction is committed or rolled back. There is only an X lock (exclusive lock) on the data line, which means that the TX lock can only be an exclusive lock, and it is meaningless to set a shared lock on the record line. When two or more sessions execute DML statements on the same record on a table, the first session is locked on that record, and the other sessions are waiting. When the first session is committed, the TX lock is released and other sessions can be locked.

On the datasheet, Oracle defaults to a shared lock, and when executing a DML statement, Oracle requests a shared lock on the object, prevents other sessions from making DDL statements on the object, successfully requests a shared lock on the table, and then adds it to the affected record to prevent other sessions from modifying the action.

In this way, checking the compatibility of the TX lock before the transaction is locked, it is necessary to check the compatibility of the TM lock mode and greatly improve the efficiency of the system. TM locks include SS, SX, S, X and other modes, which are represented in the database by 0-6. Different SQL operations produce different types of TM locks. As shown in table 1.

Description of the performance view associated with the lock

Java code

2. V$lock
4. Sid of Sid session, which can be associated with v$session
6. Type distinguishes between the types of the lock-protected object, such as TM,TX,RT,MR, etc.
8. ID1 lock means 1, see the details below
10. ID2 lock means 2, see the details below
12. Lmode lock mode, see instructions below
14. Request for the lock mode, same as Lmode
16. The time CTIME has held or waited for a lock
18. Block other session lock request 1: Block 0: Do not block
22. Lmode values 0,1,2,3,4,5, 6, the higher thelock level, the more affected the operation.
24. Level 1 Lock:
26. Select, which sometimes appears in V$locked_object.
28. Level 2 Lock is RS lock
30. The corresponding SQL is: Select for UPDATE, Lock xxx on Row Share mode,select for update when the
32. when a cursor is opened with the for update substring, all data rows in the returned set will be at the row level (Row-x)
34. Placeholder locks, other objects can only query these data rows, not update, delete, or select for update
36. Operation.
38. Level 3 lock is RX lock
40. The corresponding SQL is: Insert, Update, Delete, Lock xxx in Row Exclusive mode, no commit
42. Before inserting the same record will not react, because after a 3 lock will wait for the last 3 lock, we
44. You must release the previous to continue working.
46. Level 4 Lock is S lock
48. The corresponding SQL is: Create Index, Lock xxx in Share mode
50. 5 level lock is SRX lock
52. The corresponding SQL has: Lock xxx in Share Row Exclusive mode, update when there is a primary foreign KEY constraint
54. /delete ...;  a lock of 4, 5 may be generated.
56. Level 6 lock is x lock
58. The corresponding SQL is: Alter table, drop table, drop Index, Truncate table, Lock xxx in Exclusive
60. Mode
64. The value of the ID1,ID2 is different depending on the value of the type
66. For TM Locks
68. ID1 indicates that the object_id of the locked table can be associated with the Dba_objects view to obtain specific table information, with a ID2 value of 0
70. For TX Locks
72. ID1 a decimal value that represents the rollback segment number that the firm occupies and the number of the transaction slot slot, in the form of a group:
74. 0xrrrrssss,rrrr=rbs/undo Number,ssss=slot Number
76. ID2 the number of times a decimal value surrounds wrap, that is, the number of times a transaction slot is reused

Java code

2. V$locked_object
4. Xidusn Undo segment number, which can be associated with v$transaction
6. Xidslot Undo Slot Number
8. XIDSQN Serial Number
10. OBJECT_ID the object_id of the object being locked, which can be associated with dba_objects
12. SESSION_ID holds the session_id of the lock and can be associated with v$session
14. Oracle_username The Oracle account holding the lock
16. Os_user_name the operating system account that holds the lock
18. Process operating system number, which can be associated with v$process
20. Locked_mode lock mode, meaning same as V$lock.lmode
24. Dba_locks and V$lock content are similar, slightly
28. V$session If a session is blocked because some rows are locked by another session, the following four fields in the View list information about the objects to which the rows belong
30. The file number of the line where the row_wait_file# waits
32. The object_id to which the row_wait_obj# waiting line belongs
34. BLOCK where the row_wait_block# waiting line belongs
36. row_wait_row# the position of the awaited row in Blcok

Manual release Lock

Java code

1. Alter system kill session ' sid,serial# ';

Oracle Locks some knowledge