Locks caused by Oracle uncommitted transactions

The author of the company recently on a system, the system uses middleware to connect to the Oracle database, after a period of time the system will stop responding. The problem is found in 2 points:

1. After the middleware thread has executed the DELETE statement, it is waiting, there is no commit commit transaction, a row lock is applied to the table and the thread is not reusable (the number of middleware bus threads is limited)

2. After a row lock is applied to the table, subsequent middleware threads will be blocked if they need to modify the row.

The above two factors continue to occur, resulting in the number of middleware threads exhausted, the system stops responding.

The following will be divided into two parts, the first part is directly to determine the above failure of the SQL statement, the second part is to do experiments to reproduce the problem

1. Direct determination of row locks on tables caused by uncommitted transactions

1.1 Determine which session executed DML (INSERT/UPDATE/DELETE) but uncommitted (commit), resulting in a row lock

1.2 Determine which session is blocked and who is blocking it, because which row is causing the block

2. Reproduce the process through the experimental process

First of all, the Oracle database is write blocking write, read and write is not blocking each other, that is, the following two statements in a different window (SESSION) execution, because they are updated the same row, if the first execution of no commit, after the execution of that will be in the blocked state:

-- The first window executes the following statement UPDATE Testlock SET AAA=oneWHERE aaa=1-- The second window executes the following statement UPDATE  testlockSET aaa=tripleWHERE aaa=1

Starting with our experiment, Oracle's version 11G has the following issues:

1. How to know that a connection has modified data but not committed, resulting in a lock on the table
2. For the 1 question, can you tell which record of the table was specifically locked?

First we set up three windows in the Ps/sql client, respectively:

Test window 1

Test Window 2

Observation window

1. Set up a Test table in Test window 1 and insert 10 records

/* Initialize the test table */ --Create a test table CREATE TABLE Testlock ( AAA number NOT NULL, BBB Nvarchar2 (TEN) is not NULL, CCC Nvarchar2 (TEN) NOT NULL ); Create INDEX Pk_testlock on Testlock (AAA); --Random insertion point data INSERT into Testlock VALUES (' 1 ', ' BBB ', ' CCC '); INSERT into Testlock VALUES (' 2 ', ' BBB ', ' CCC '); INSERT into Testlock VALUES (' 3 ', ' BBB ', ' CCC '); INSERT into Testlock VALUES (' 4 ', ' BBB ', ' CCC '); INSERT into Testlock VALUES (' 5 ', ' BBB ', ' CCC '); INSERT into Testlock VALUES (' 6 ', ' BBB ', ' CCC '); INSERT into Testlock VALUES (' 7 ', ' BBB ', ' CCC '); INSERT into Testlock VALUES (' 8 ', ' BBB ', ' CCC '); INSERT into Testlock VALUES (' 9 ', ' BBB ', ' CCC '); INSERT into Testlock VALUES (' Ten ', ' BBB ', ' CCC '); --and then we have some data on the surface. SELECT * from Testlock; COMMIT;

1. In Test window 2, query the test table

SELECT * from Testlock;

1. Observe the current session and table lock in the observation window

-The current session situation SELECT sid,serial#,audsid,paddr,user#,username,event,wait_class,seconds_in_wait,row_wait_obj#,row_wait_file#, row_wait_block#,row_wait_row#, Blocking_session_status,blocking_instance,blocking_session From V$session WHERE username= ' TEST '; --the current situation of Testlock table lock SELECT * from V$lock WHERE type= ' TM ' and id1= (SELECT object_id from dba_objects WHERE object_name= ' Testlock '); SELECT Xidusn,xidslot,xidsqn,object_id,session_id,oracle_username,process,locked_mode From V$locked_object WHERE object_id= (SELECT object_id from dba_objects WHERE object_name= ' Testlock ');

You can see that the test account generated a total of 4 sessions, respectively, Plsql itself connected to the database and we built three windows

We focus on several fields:

The data or event that the event:oracle session is waiting for.

Wait_class: The name of the wait event

Blocking_session_status: If the Blocking_session_status field is valid, it means that the session is blocked

Blocking_session: which session is blocked

With the above 4 observations, no session is blocked and the current three sessions are waiting for the client message state (Event=sql*net message from client, Wait_class=idle), The remaining session is sending a message to the client (Event=sql*net message to Client,wait_class=network), which is our current observation window

The following two queries V$lock and V$locked_object have no return, indicating that the current Testlock table is not locked

1. Update testlock in Test window 1, but do not commit

UPDATE Testlock SET aaa=11 WHERE aaa=1

1. Execute the statement in step 3 again in the Observation window:

By querying the V$lock and V$locked_object, the sid=1947 locks the Testlock table, where lmode=3 (row-level exclusive lock, which we are generating via update)

Lock mode	Lock description	Explain	SQL Operation
0	None
1	Null	Empty	Select
2	SS (Row-s)	Row-level shared locks, other objects can only query these data rows	Select for update Lock for update Lock Row Share
3	SX (Row-x)	Row-level exclusive locks that do not allow DML operations before committing	Insert/update/delete Lock Row Share
4	S (Share)	Shared locks	Create Index Lock Share
5	SSX (S/row-x)	Shared row-level exclusive lock	Lock Share Row Exclusive
6	X (Exclusive)	Exclusive lock	Alter table Drop Able Drop Index Truncate table Lock Exclusive

1. Back to the first question, how to know a session modified data but no commit, in the observation window to execute the following statement:

--Find the modified data, but uncommitted session, choose wait_calss= ' Idle ', that is, the session is resting, but there is a locked table SELECT A.sid,a.serial#,a.username,a.event,a.wait_class,a.seconds_in_wait,a.prev_exec_start,b.locked_mode,c. Owner,c.object_name,c.object_type From V$session A INNER JOIN V$locked_object B On a.sid=b.session_id INNER JOIN dba_objects C On b.object_id=c.object_id WHERE a.wait_class= ' Idle ' and a.seconds_in_wait>10/*session after idle for a period of time also locked is a problem, here gave a value of 10 seconds */ and Username= ' TEST ';

Only need to judge wait_class= ' Idle ', at the same time there are locked objects in V$locked_object and the session is idle for a period of time, you can judge sid=1947 locked Testlock table, Seconds_in_ Wait can be considered as the duration of the lock, in seconds

1. Execute the following statement in the observation window, observing from the perspective of the transaction

--Viewing from a transactional perspective, connecting V$session and V$transaction SELECT a.sid,a.serial#,a.username,a.event,a.wait_class,a.seconds_in_wait,a.prev_exec_start,b.start_date From V$session A INNER JOIN v$transaction b On A.TADDR=B.ADDR WHERE username= ' TEST '

It can be observed that the start_date of Prev_exec_start and v$transaction are consistent.

1. What happens when the other session modifies the same record of Testlock in the case where the previous update does not commit?

Execute the following statement in Test window 2:

--Update and Test window 1 the same record UPDATE Testlock SET aaa=12 WHERE aaa=1

The statement will remain in the "executing" state, which is actually a row lock above the Testlock, which waits until the row lock is released

1. Execute the statement in step 3 again in the Observation window:

Observing sid=9, showing event= ' enq:tx-row lock contention ', indicates that a row lock is waiting to be released, blocking_session that the SESSION was blocked by sid=1947, Which is the session of the Test window 1.

Observation V$lock actually not distinguish which lock is not submitted, which is, two lock display is the same, this is very strange

Observe V$locked_object, can be judged by XINUSN/XIDSLOT/XIDSQN, these three fields are and rollback related fields, if all are 0, can be judged to be blocked

1. Is there a way to know which line of the table is locked?

Execute the following statement in the observation window:

--When the session is blocked, find the rowID by row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row# these fields and find the locked record by rowID SELECT sid,serial#,audsid,paddr,user#,username,event,wait_class,seconds_in_wait,row_wait_obj#,row_wait_file#, row_wait_block#,row_wait_row#, Blocking_session_status,blocking_instance,blocking_session,c.owner,c.object_name,c.object_type , Dbms_rowid.rowid_create (1,row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#) From V$session A INNER JOIN V$locked_object B On a.sid=b.session_id INNER JOIN dba_objects C On b.object_id=c.object_id WHERE username= ' TEST ' And blocking_session is not NULL; --Get specific rowid from the previous function rowid_create, then query the record in the locked table SELECT * from Testlock where rowid= ' aaj2qdaanaagrwnaaa '

Get the rowid of a specific blocked table

The query gets a specific blocked record, which is exactly what we did in the Test window 1 update.

The field explanations for v$session can be found in:

Https://docs.oracle.com/cd/E18283_01/server.112/e17110/dynviews_3016.htm

Locks caused by Oracle uncommitted transactions