Deep understanding of ORACLE Lock Mechanism

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 lock type
Oracle Database locks can be divided into the following categories based on different protected objects: DML locks (data locks), used to protect data integrity; DDL locks (dictionary locks, dictionary locks) are used to protect the structure of database objects, such as schema definitions for tables and indexes. internal locks and latches protect the internal structure of the database.
The purpose of the DML lock is to ensure data integrity in the case of concurrency ,. In Oracle databases, DML locks mainly include tmlocks and TX locks. tmlocks are called table-level locks, and TX locks are called transaction locks or row-level locks.

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.

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.

When the Oracle database has a TX lock wait, if not timely processing will often cause the Oracle database to suspend, or cause the occurrence of a deadlock, produce ORA-60 errors. These phenomena will cause great harm to the actual application, such as long time not responding, a large number of transactions failed, etc.

Pessimistic blockout and optimistic blockout
I. pessimistic Blocking
The lock takes effect before the user changes:
Select... for update (nowait)
Select * from tab1 for update
After the user issues this command, oracle will establish a row-level blocking for the data in the returned set to prevent modification by other users.
If other users perform dml or ddl operations on the data returned in the result set above, an error message or blocking will be returned.
1: The update or delete operation on the returned result set will be blocked.
2: ddl operations on the table will report: Ora-00054: resource busy and acquire with nowait specified.
Cause Analysis
At this time, Oracle has added row-level locks to the returned result set. All other modification or deletion operations on the data must wait for the lock to be released, the external phenomenon is that other operations will be blocked. This operation is commit or rollback.
Similarly, the transaction for this query will apply a table-Level Lock to the table, and does not allow any ddl operations on the table, otherwise the ora-00054 error will be reported :: resource busy and acquire with nowait specified.

2. Optimistic Blocking
We are optimistic that the data will not be changed during the period from select to update and submit. There is a potential danger that the selected result set is not locked and may be changed by other users. Therefore, we recommend that you use pessimistic blocking for Oracle, because it is safer.

Blocking
Definition:
When one session keeps the lock on the requested resources of another session, blocking occurs. The blocked session will be suspended until the locked session gives up the locked resource. Four Common dml statements may cause blocking.
INSERT
UPDATE
DELETE
SELECT... FOR UPDATE

INSERT
Insert is blocked only when you have a table with primary key constraints. When two sessions attempt to insert the same data to the table at the same time, one session will be blocked until another session is committed or rolled. When one session is submitted, the other session will receive an error with duplicate primary keys. During rollback, the blocked session continues.
UPDATE and DELETE when the data row that executes the Update and delete operations has been locked by another session, it will be blocked until another session is committed or rolled.
Select... For update
When a user sends a select... for update error to prepare to modify the returned result set, if the result set has been locked by another session, blocking occurs. The execution can be continued only after another session ends. Select... For update nowait statements to avoid blocking, if the resource is locked by another session, the following error is returned: Ora-00054: resource busy and acquire with nowait specified.
Deadlock-deadlock
Definition: a deadlock occurs when two users want to hold each other's resources.
That is, when two users wait for each other to release resources, oracle determines that a deadlock occurs. In this case, the execution of another user will be continued at the cost of one user, transactions of the sacrificed users will be rolled back.
Example:
1: User 1 updates table A without submitting.
2: User 2 updates table B without submitting.
In this case, there is no resource sharing problem.
3: If user 2 updates table A at this time, blocking will occur. You need to wait until the end of user 1.
4: If user 1 updates table B, a deadlock occurs. In this case, Oracle will select one of the users for rollback, so that another user can continue the operation.
Cause:
In fact, the deadlock problem in Oracle is rare. If it occurs, it is basically caused by incorrect program design. After adjustment, the deadlock will basically be avoided.

DML lock classification table
Table 1Oracle tmlock type
Lock mode lock description Description SQL operations
0 none
1 NULL Select
2 SS (Row-S) Row-level shared lock, other objects
Only Select for update and Lock for these data rows can be queried.
Update, Lock row share
3 SX (Row-X) Row-level exclusive lock,
DML operations Insert, Update,
Delete, Lock row share
4 S (Share) share Lock Create index, Lock Share
5 SSX (S/Row-X) share row-level exclusive Lock share Row exclusive
6 X (Exclusive) exclusive Lock Alter table, Drop able, Drop index, Truncate table, Lock Exclusive
Oracle Lock solution

You can use Spotlight software to monitor the running status of the database.
When a session lock occurs, we need to handle it in time.
1. view which session locks:
SQL statement: select 'alter system kill session ''' | sid | ',' | serial # | '''; 'From v $ session where sid in (select sid from v $ lock where block = 1 );
SQL> select 'alter system kill session ''' | sid | ',' | serial # | '''; 'From v $ session where sid in (select sid from v $ lock where block = 1 );
'Altersystemkillsession ''' | SID | ',' | SERIAL # | ''';'
--------------------------------------------------------------------------------
Alter system kill session '123 ';
Alter system kill session '2017 05 ';
Alter system kill session '123 ';
Alter system kill session '2017 0 ';
2. view the session lock.
SQL statement: select s. sid, q. SQL _text from v $ sqltext q, v $ session s
Where q. address = s. SQL _address
And s. sid = & sid
Order by piece;
SQL> select s. sid, q. SQL _text from v $ sqltext q, v $ session s where q. address = s. SQL _address and s. sid in (select sid from v $ lock where block = 1) order by piece;
SID SQL _TEXT
--------------------------------------------------------------------------
77 UPDATE PROFILE_USER set id = 1, COMPANY_ID = 2, CUSTOMER_ID = 3, NAMED
77 _ INSURED_ID = 4, LOGIN = 5, ROLE_ID = 6, PASSWORD = 7, EMAIL = 8, TIME_ZON
77 E = 9 WHERE PROFILE_USER.ID =: 34
3 rows selected.

3. kill the lock process.
SQL statement: alter system kill session '000000 ';
SQL> alter system kill session '000000 ';
System altered.
4. Check who has locked the lock.
Select s1.username | [email = '@'] '@' [/email] | s1.machine
| '(SID =' | s1.sid | ') is blocking'
| S2.username | [email = '@'] '@' [/email] | s2.machine | '(SID =' | s2.sid | ')' AS blocking_status
From v $ lock l1, v $ session s1, v $ lock l2, v $ session s2
Where s1.sid = l1.sid and s2.sid = l2.sid
And l1.BLOCK = 1 and l2.request> 0
And l1.id1 = l2.id1
And l2.id2 = l2.id2;
Note:
>: Redirected output: The standard output of the file is output to the file again, or the data file is used as the standard input content of another program.
|: UNIX pipeline: outputs a file as the input of another file.
Try executing the SQL statement: alter system kill session '000000' (sid: 391,483); Be careful when sid is lower than 391. This process may correspond to an application, you can kill a transaction.