Oracle lock mechanism deep understanding of _oracle

A database is a shared resource that is used by multiple users. When multiple users have concurrent access to the data, multiple transactions are generated in the database while the same data is being accessed. Without control of concurrent operations, incorrect data can be read and stored, which destroys the consistency of the database.
Lock is a very important technology to realize concurrency control of database. When a transaction is operating on a data object, the system is asked to lock it before it is made. After the lock transaction has some control over the data object, the other transaction cannot update the data object until the transaction releases the lock.
There are two basic types of locks in the database: exclusive locks (Exclusive Locks, i.e., x locks) and shared locks (Share Locks, or S locks). When a data object is added to the exclusive lock, no other transaction can read and modify it. Data objects with shared locks can be read by other transactions, but cannot be modified. The database uses these two basic lock types to concurrently control the transactions of the database.

Lock type for Oracle database
Depending on the object being protected, Oracle database locks can be grouped into the following categories: DML locks (data locks, database locks) to protect the integrity of data, DDL locks (Dictionary locks, Dictionary locks), which protect the structure of database objects, such as tables, Structure definitions such as indexes, internal locks and latches (internal locks and latches) to protect the internal structure of the database.
The purpose of DML locks is to ensure data integrity in concurrent situations. In Oracle databases, DML locks consist primarily of TM and TX locks, where TM locks are called table-level locks, and TX locks are called transaction locks or row-level locks.

When Oracle executes a DML statement, the system automatically requests a TM type of lock on the table to be manipulated. When the TM lock is obtained, the system automatically requests the TX type of lock and resets the lock flag bit of the actual locked data row. In this way, check the TX lock compatibility before the transaction is locked without further checking the lock flag, and only check the compatibility of TM lock mode, which can greatly improve the efficiency of the system. TM locks include SS, SX, S, X, and many other modes, represented in the database in 0-6. Different SQL operations produce different types of TM locks.

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 a DML statement on the same record on the table, the first session is locked on the record, and the other session is in the waiting state. When the first session is committed, the TX lock is freed and the other sessions can be locked.

When a TX lock wait occurs in an Oracle database, failure to process it often causes the Oracle database to hang, or cause deadlocks to occur, resulting in ORA-60 errors. These phenomena will cause great harm to the actual application, such as long time not responding, a large number of transaction failures.

pessimistic blockade and optimistic blockade
One, the pessimistic blockade
The lock works before the user modifies it:
Select.. For update (NOWAIT)
Select * from TAB1 for update
After the user has issued this command, Oracle will establish a row-level blockade of the data returned to prevent other users from modifying it.
If the other user at this time is DML or DDL operations on the data returned by the result set above, an error message or blocking occurs.
1: An update or delete operation on the return result set will block.
2: DDL operations on this table will be reported: Ora-00054:resource busy and acquire with nowait specified.
cause Analysis
Oracle has already added row-level locks to the returned result set, all other modifications or deletions to the data must wait for the lock to be released, and the resulting external phenomenon is that other operations will be blocked, this operation commits or rollback.
The same query's transaction will have a table-level lock on the table, no DDL operations on the table are allowed, or a ora-00054 error will be reported:: Resource busy and acquire with nowait specified.

Ii. optimistic blockade
Optimistic that the data in the select out to update the enterprising and submit the time data will not be changed. There is a potential danger that because the chosen result set is not locked, there is a possibility of being changed by another user. So Oracle is still suggesting a pessimistic blockade, because it's safer.

Blocking
Defined:
Blocking occurs when a session holds a lock on a resource that another session is requesting. The blocked session is suspended until the locked session discards the locked resource. 4 common DML statements can cause blocking
INSERT
UPDATE
DELETE
SELECT ... For UPDATE

INSERT
The only case where inserts are blocked is if the user has a table with a PRIMARY key constraint. When 2 sessions attempt to insert the same data into the table at the same time, one of the sessions is blocked until another session commits or rolls. When one session commits, another session receives a duplicate primary key error. When rolling back, the blocked session continues to execute.
Update and delete When the data row that performs the update and delete operation is locked by another session, blocking occurs until another session commits or rolls.
Select ... for update
When a user issues a select. For the error of update is prepared to modify the returned result set, blocking occurs if the result set is locked by another session. You need to wait until another session has ended before you can continue execution. You can avoid blocking by issuing a statement for the select ... for update nowait, and if the resource is locked by another session, the following error is returned: Ora-00054:resource busy and Acquire with nowait Specified.
dead Lock-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 is determined to have a deadlock, in which case, at the expense of one user, another user continues execution, and the user's transaction is rolled back.
Example:
1: User 1 To update a table, did not submit.
2: User 2 to the B table update, did not submit.
At this point, there is no resource sharing problem with the double counter.
3: If the user 2 at this time to update a table, it will be blocked, need to wait until the end of the user one thing.
4: If the user 1 again to the B table for update, then create a deadlock. Oracle then chooses one of the users to roll, allowing another user to continue the operation.
causes:
Oracle deadlock problem is actually very rare, if occur, is basically incorrect program design caused, after adjustment, basically will avoid the occurrence of deadlock.

DML Lock Classification Table
Table 1Oracle TM Lock type
Lock mode Lock description Explanation SQL operation
0 None
1 Null empty Select
2 SS (row-s) row-level shared locks, other objects
You 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 prior to commit Insert, Update,
Delete, Lock Row share
4 S (Share) shared lock Create Index, lock Share
5 SSX (s/row-x) shared row-level row lock lock share row Exclusive
6 X (Exclusive) exclusive lock Alter table, drop able, DROP index, Truncate table, lock Exclusive
Solution to Oracle Lock problem

The running state of the database can be monitored with spotlight software.
When the session lock appears, we have to deal with it in time.
1. See 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 ' 132,731 ';
Alter system kill session ' 275,15205 ';
Alter system kill session ' 308,206 ';
Alter system kill session ' 407,3510 ';
2. View 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 fro M v$lock where block = 1) order by piece;
SID Sql_text
---------- ----------------------------------------------------------------
UPDATE Profile_user SET id=1,company_id=2,customer_id=3,named
_insured_id=4,login=5,role_id=6,password=7,email=8,time_zon
E=9 WHERE profile_user.id=:34
3 Rows selected.

3. The process of the kill lock.
SQL statement: Alter system kill session ' 77,22198 ';
Sql> alter system kill session ' 391,48398 ';
System altered.
4. Find out who locked who.
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:
>: Redirect output, redirect the standard output of a file to a file, or use the data file as the standard input for another program.
| : Unix Pipeline: The output of one file as input to another file.
Try the Execute SQL statement: Alter system kill session ' 391,48398 ' (SID is 391); It should be noted that you should be cautious about SIDS below 100, perhaps the process corresponds to a application, such as a transaction, you can kill.