Oracle Level 2: Lock

A database is a shared resource used by multiple users. When multiple users access data concurrently, multiple transactions can access the same data simultaneously in the database. If concurrent operations are not controlled, incorrect data may be read and stored, compromising Database Consistency.

Locking is a very important technology for implementing database concurrency control. Before a transaction operates on a data object, it first sends a request to the system to lock it. After the lock, the transaction has certain control over the data object. Before the transaction releases the lock, other transactions cannot update the data object.

There are two basic lock types in the database: exclusive locks and share locks ). When an exclusive lock is applied to a data object, other transactions cannot read or modify it. Data Objects with a shared lock can be read by other transactions, but cannot be modified. The database uses these two basic lock types to control the concurrency of database transactions.

Oracle Database locks can be divided into the following categories based on different protected objects:
DML locks are used to protect data integrity;
DDL locks are used to protect the structure of database objects, such as table and index structures;
Internal locks and latches protect the internal structure of the database and apply it to SGA;

DML locks are involved in our actual application development. The DBA will be more concerned with the other two types of locks;
The purpose of the DML lock is to ensure data integrity in the case of concurrency, including the tmlock and TX lock. The tmlock is called the table-Level Lock, And the Tx lock is called the transaction lock or row-Level Lock.

When Oracle executes the DML statement, the system automatically applies for a TM lock on the table to be operated. After the tmlock is obtained, the system automatically applies for the Tx lock and places the lock flag of the actually locked data row. In this way, when the consistency of the Tx lock is checked before the transaction locks, the lock mark does not need to be checked row by row. Instead, you only need to check the compatibility of the tmlock mode, which greatly improves the system efficiency. The tmlock includes multiple modes, such as SS, Sx, S, and X, which are represented by 0-6 in the database. Different SQL operations generate different types of tmlocks. As shown in:

Value	Lock mode	Lock description	SQL
0	None
1	Null	Null	Select
2	SS (ROW-S)	Row-level shared lock Other objects can only query these data rows.	Select for update, lock for update, Lock row share
3	SX (ROW-X)	Row-level exclusive lock DML operations are not allowed before submission	Insert, update, delete, Lock row share
4	S (SHARE)	Shared lock	Create index, lock share
5	Ssx (S/ROW-X)	Shared row-level exclusive locks	Lock share row exclusive
6	X (exclusive)	Exclusive lock	Alter table, drop table, drop index, Truncate table, lock exclusive

Only the X lock (exclusive lock) exists on the Data row ). In the Oracle database, a TX lock is obtained when a transaction initiates a DML statement for the first time. The lock is kept until the transaction is committed or rolled back. When two or more sessions execute DML statements on the same record of the table, the first session locks the record, and other sessions are in the waiting state. After the first session is submitted, the Tx lock is released before other sessions can be locked.

After learning about the Oracle lock mechanism, let's solve several basic problems:
1. The update/delete operation will lock the RS until the operation is committed or rollback;
If another session changes the same Rs before the operation is committed, the operation will be held until the update/delete operation of the previous session is committed;

2. RS range of select inside and outside the session
Prerequisites: select is performed before the insert and update operations are committed;
If the selected RS is in the same session, the records affected by the previous insert and update operations are included;
If they are not in the same session, the RS generated by the SELECT statement will not include records not recorded by the commit statement;

3. Select... for update [of Cols] [Nowait/Wait] [Skip locked]
Of Cols: only the RS of the table where the specified field is located is locked, but the table that is not specified is not locked. It will only appear during multi-table joint query;
Nowait: the statement does not hold, but directly returns the error ORA-00054: Resource busy and acquire with Nowait specified;
Wait N: the statement is held for n seconds and then returns the error ORA-30006: Resource busy; acquire with wait timeout expired;
Skip locked: no error is returned, but no rows selected is returned;
The preceding options can be used in combination. We recommend that you:
Select... for update Nowait: an error is returned directly when the same RS is executed;
Select... for update Nowait Skip locked: When the SQL statement is executed on the same RS, empty rows are directly returned;
PS: When the RS is locked, it is only valid for statements that request the same lock and has no effect on select statements that do not require lock;

 

Reference: http://blog.csdn.net/sfdev/archive/2008/01/13/2042325.aspx