1, Find out the specific statements that cause lock waiting
Simulate the row lock waiting that may occur in DML operation of Damon database:
Session 1: update table test_ A row of data with id=2 (ID field is the primary key field) in lock was successfully updated and not submitted:
SQL> update test_lock set name='Change_na' where id=2;affect rows 1used time: 2.103(ms). Execute id is 3106.
Session 2: continue to update table test_ The row of data with id=2 in lock is not updated successfully and is in a waiting state:
SQL> update test_lock set name='Change_Tw' where id=2;
--Session 2 will hang up because it is waiting for the resources held by session 1. It can be executed successfully only after session 1 is committed or rolled back.
Lock waiting has been simulated earlier. Here we need to deal with lock waiting:
1) First, view the suspended transaction (TRX_ID)
SQL> SELECT VTW.ID AS TRX_ID, VS.SESS_ID ,VS.SQL_TEXT,VS.APPNAME ,VS.CLNT_IP FROM V$TRXWAIT VTW LEFT JOIN V$TRX VT ON (VTW.ID=VT.ID) LEFT JOIN V$SESSIONS VS ON (VT.SESS_ID=VS.SESS_ID); LINEID TRX_ID SESS_ID SQL_TEXT APPNAME CLNT_IP ---------- -------------------- -------------------- ------------------------------------------------- ------- ----------------1 6158 139808835965528 update test_lock set name='Change_Tw' where id=2; used time: 18.065(ms). Execute id is 3700.
2) Find the transaction it is waiting for (WAIT_FOR_ID) through the pending transaction ID (TRX_ID).
SQL> SELECT WAIT_FOR_ID,WAIT_TIME FROM V$TRXWAIT WHERE ID=6158; LINEID WAIT_FOR_ID WAIT_TIME ---------- -------------------- -----------1 6157 108572 used time: 1.843(ms). Execute id is 3701.
3) Locate the connection and the executed statement by waiting for the transaction ID (WAIT_FOR_ID)
SQL> SELECT VT.ID AS TRX_ID, VS.SESS_ID, VS.SQL_TEXT, VS.APPNAME, VS.CLNT_IP FROM V$TRX VT LEFT JOIN V$SESSIONS VS ON (VT.SESS_ID=VS.SESS_ID) WHERE VT.ID = 6157;LINEID TRX_ID SESS_ID SQL_TEXT APPNAME CLNT_IP ---------- -------------------- -------------------- ------------------------------------------------- ------- ----------------1 6157 139809037292120 update test_lock set name='Change_na' where id=2; used time: 6.749(ms). Execute id is 3702.
2. Handle the statements that cause lock waiting
For the statements causing lock waiting, kill is preferred to ensure the stability of the system. Then analyze and locate the captured statements.
1) kill the statement causing lock waiting:
SQL> SP_CLOSE_SESSION(139809037292120);DMSQL executed successfullyused time: 0.796(ms). Execute id is 3703.
2) Check session 2 again and find that the update operation has been successfully executed:
SQL> update test_lock set name='Change_Tw' where id=2;affect rows 1used time: 00:04:42.183. Execute id is 3600.SQL>
Note: for this kind of lock waiting problem, if it is caused by the business side, it is generally a defect in the business logic, which needs to be checked and repaired; If it is caused by the operation and maintenance side, it is generally caused by non-standard operation, which needs to be rectified in time. In any case, it needs to be handled carefully to standardize the development and operation and maintenance.
Description of official documents introduced:
In DM database, INSERT, UPDATE and DELETE are the most common statements that will cause blocking and deadlock.
The only case where INSERT blocking occurs is that when multiple transactions simultaneously attempt to INSERT the same data into a table with primary key or UNIQUE constraints, one of them will be blocked until the other transaction is committed or rolled back. When a transaction is committed, another transaction will receive an error of uniqueness conflict; When a transaction is rolled back, the blocked transaction can continue to execute.
When the records modified by UPDATE and DELETE have been modified by another transaction, blocking will occur until another transaction is committed or rolled back.