Tracing, debugging and fixing Row Lock Contentions

locked-objectslockingoracle-10g

Off late, I've been facing a lot of row lock contentions. The table in contention seems to be a particular table.

This is generally what happens –

Developer 1 starts a transaction from Oracle Forms front end screen
Developer 2 starts another transaction, from a different session using the same screen

~5 minutes in, the front end seems unresponsive. Checking sessions shows row lock contention. The "solution" that everyone throws around is to kill sessions :/

As a database developer

What can be done to eliminate row lock contentions?
Would it be possible to find out which line of a stored procedure is causing these row lock contentions
What would be the general guideline to reduce/avoid/eliminate such problems which coding?

If this question feels too open-ended/insufficient information please feel free to edit/let me know – I'll do my best to add in some additional information.

The table in question is under a lot of inserts and updates, I'd say it's one of the most busiest tables. The SP is fairly complex – to simplify – it fetches data from various tables, populates it into work tables, a lot of arithmetic operations occur on the work table and the result of the work table is inserted/updated into the table in question.

The database version is Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit. The flow of logic is executed the same order in both the sessions, the transaction isn't kept open for too long ( or at least I think so), and the locks occur during active execution of transactions.

Update: The table row count is larger than I expected, at about 3.1 million rows. Also, after tracing a session I found that couple of update statements to this table are not utilizing the index. Why is it so – I'm not sure. The column referenced in the where clause is indexed. I'm currently rebuilding the index.

Best Answer

Would it be possible to find out which line of a stored procedure is causing these row lock contentions?

Not exactly but you can get the SQL statement causing the lock and in turn identify the related lines in the procedure.

SELECT sid, sql_text
FROM v$session s
LEFT JOIN v$sql q ON q.sql_id=s.sql_id
WHERE state = 'WAITING' AND wait_class != 'Idle'
AND event = 'enq: TX - row lock contention';

What would be the general guideline to reduce/avoid/eliminate such problems with coding?

The Oracle Concepts Guide section on locks says, "A row is locked only when modified by a writer." Another session updating the same row will then wait for the first session to COMMIT or ROLLBACK before it can continue. To eliminate the problem you could serialize the users, but here are some things that can reduce the problem perhaps to the level of it not being an issue.

COMMIT more frequently. Every COMMIT releases locks, so if you can do the updates in batches the likelihood of another session needing the same row is reduced.
Make sure you aren't updating any rows without changing their values. For example, UPDATE t1 SET f1=DECODE(f2,’a’,f1+1,f1); should be rewritten as the more selective (read fewer locks) UPDATE t1 SET f1=f1+1 WHERE f2=’a’;. Of course if the changing the statement will still lock the majority of rows in the table then the change will only have a readability benefit.
Make sure you are using sequences rather than locking a table to add one to the highest current value.
Make sure you aren’t using a function that is causing an index to not be used. If the function is necessary consider making it a function based index.
Think in sets. Consider whether a loop running a block of PL/SQL doing updates could be rewritten as a single update statement. If not then perhaps bulk processing could be used with BULK COLLECT ... FORALL.
Reduce the work that gets done between the first UPDATE and the COMMIT. For example, if the code sends an email after each update, consider queuing the emails and sending them after committing the updates.
Design the application to handle waiting by doing a SELECT ... FOR UPDATE NOWAIT or WAIT 2. You can then catch the inability to lock the row and inform the user that another session is modifying the same data.

Simplified scenario:

CREATE TABLE IF NOT EXISTS `test`.`revision` (
  `idrevision` INT UNSIGNED NOT NULL,
  `iduser` INT UNSIGNED NOT NULL,
  `idpost` INT UNSIGNED NOT NULL,
  `content` VARCHAR(4000) NOT NULL,
   UNIQUE INDEX `index1` (`idpost`, `idrevision`),
   PRIMARY KEY (`idrevision`, `iduser`),
   INDEX `user1_idx` (`iduser`));

INSERT INTO `test`.`revision` (`idrevision`, `iduser`, `idpost`, content`) VALUES (1, 1, 1, 'this is old content of first post');

Session1:

BEGIN;
INSERT INTO revision (idrevision, idpost, iduser, content) VALUES  
  ((SELECT max_idrevision + 1 
    FROM (SELECT max(idrevision) AS max_idrevision
          FROM revision WHERE idpost = 1) AS anon_1), 
   1, 2, 'abc xyz');

select max(idrevision) AS max_idrevision from revision where idpost=1;

The above query will fetch the value from the secondary index 'index1'. Because idpost and idrevision is covered by index1 itself. So index1 is a covering index for this query.
It will take S lock on the row(1,1) in the index 'index1' due to repeatable read isolation level. (Read about repeatable read isolation level).

Session2:

BEGIN;
UPDATE revision SET content='nwe asdfs' WHERE idpost=1 AND iduser=1;

Above query will change the clustered index alone, it won't change any secondary index like 'index1' because 'content' is not part of any secondary index.

UPDATE revision SET idrevision=3 WHERE idpost=1 AND iduser =1;

But in this case, idrevision has been changed. So it will try to update the key part of 'index1' ( because idrevision is a part of key in index1).

Why it leads to hang:

As I mentioned earlier in session 1, S row lock taken for the row (1,1) in the index1.
Now session 2 tries to take X row lock to update the row(1,1) in the same index1.
If you look lock type compatibility matrix, S and X locks are Conflicts.

Note:

During hanging, you can open one more session and give the following query to find out where it is hanging.

mysql> select * from information_schema.innodb_locks;
--------------
select * from information_schema.innodb_locks
--------------

+------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+
| lock_id    | lock_trx_id | lock_mode | lock_type | lock_table        | lock_index | lock_space | lock_page | lock_rec | lock_data |
+------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+
| 1826:8:4:2 | 1826        | X         | RECORD    | `test`.`revision` | index1     |          8 |         4 |        2 | 1, 1      |
| 1817:8:4:2 | 1817        | S         | RECORD    | `test`.`revision` | index1     |          8 |         4 |        2 | 1, 1      |
+------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+

The above analysis is based on mysql-5.6

MySQL locking in Duplicate Key Error

I did a small simulation of that situation, in a simple bash script that does that:

➜  rsandbox_5_6_30 cat test.sh 
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select  sleep(3); rollback;" test 2> tx1 &
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select sleep(5); commit;" test 2> tx2 &
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select sleep(5); commit;" test 2> tx3 &

./m -e "SHOW ENGINE INNODB STATUS\G" test > istatus

Result:

➜  rsandbox_5_6_30 cat tx1 
ERROR 1062 (23000) at line 1: Duplicate entry '6' for key 'PRIMARY'
➜  rsandbox_5_6_30 cat tx2
ERROR 1062 (23000) at line 1: Duplicate entry '6' for key 'PRIMARY'
➜  rsandbox_5_6_30 cat tx3

Transaction status:

---TRANSACTION 265035, not started
MySQL thread id 4, OS thread handle 0x700000b0b000, query id 204 localhost msandbox cleaning up
---TRANSACTION 265017, not started
MySQL thread id 3, OS thread handle 0x700000ac7000, query id 171 localhost msandbox cleaning up
---TRANSACTION 265041, ACTIVE 0 sec
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 31, OS thread handle 0x700000ca3000, query id 210 localhost msandbox User sleep
select sleep(5)

If you execute the first transaction within certain delay, you can incur on a frame where one of the subsequent transactions is killed by deadlock detection, making the other transaction to insert the value.

The status of the alive transaction reflects that still needs an explicit commit (undo log entry 1).

-- Edited 2nd

"Neither can acquire an exclusive lock for the row because of the shared lock held by the other." Actually the deadlock kills only 1 transaction, not both. So the EL is acquired by the 2nd session.

Due to how gap locking works we see the difference between executions. The deadlock happens as an intention of the 3rd transaction to acquire the shared lock when a duplicate key error is detected. When this happens, the 2nd transactions has the shared lock acquired, producing a deadlock and killing the 3rd transaction. This is a limitation in the amount of concurrent writes over the same record.