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.
Your developer is mistaken. You need either SELECT ... FOR UPDATE or row versioning, not both.
Try it and see. Open three MySQL sessions (A), (B) and (C) to the same database.
In (C) issue:
CREATE TABLE test(
id integer PRIMARY KEY,
data varchar(255) not null,
version integer not null
);
INSERT INTO test(id,data,version) VALUES (1,'fred',0);
BEGIN;
LOCK TABLES test WRITE;
In both (A) and (B) issue an UPDATE that tests and sets the row version, changing the winner text in each so you can see which session is which:
-- In (A):
BEGIN;
UPDATE test SET data = 'winnerA',
version = version + 1
WHERE id = 1 AND version = 0;
-- in (B):
BEGIN;
UPDATE test SET data = 'winnerB',
version = version + 1
WHERE id = 1 AND version = 0;
Now in (C), UNLOCK TABLES; to release the lock.
(A) and (B) will race for the row lock. One of them will win and get the lock. The other will block on the lock. The winner who got the lock will proceed to change the row. Assuming (A) is the winner, you can now see the changed row (still uncommitted so not visible to other transactions) with a SELECT * FROM test WHERE id = 1.
Now COMMIT in the winner session, say (A).
(B) will get the lock and proceed with the update. However, the version no longer matches, so it will change no rows, as reported by the row count result. Only one UPDATE had any effect, and the client application can clearly see which UPDATE succeeded and which failed. No further locking is necessary.
See session logs at pastebin here. I used mysql --prompt="A> " etc to make it easy to tell the difference between sessions. I copied and pasted the output interleaved in time sequence, so it's not totally raw output and it's possible I could've made errors copying and pasting it. Test it yourself to see.
If you had not added a row version field, then you would need to SELECT ... FOR UPDATE to be able to reliably ensure ordering.
If you think about it, a SELECT ... FOR UPDATE is completely redundant if you're immediately doing an UPDATE without re-using data from the SELECT, or if you're using row versioning. The UPDATE will take a lock anyway. If someone else updates the row between your read and subsequent write, your version won't match anymore so your update will fail. That's how optimistic locking works.
The purpose of SELECT ... FOR UPDATE is:
- To manage lock ordering to avoid deadlocks; and
- To extend the span of a row lock for when you want to read data from a row, change it in the application, and write a new row that's based on the original one without having to use
SERIALIZABLE isolation or row versioning.
You do not need to use both optimistic locking (row versioning) and SELECT ... FOR UPDATE. Use one or the other.
Best Answer
No
You can be sure that one of the threads will obtain the lock and hold it until the transaction ends. And while it holds the lock, no other transaction can get the lock. But there is no guarantee that the thread you started first will get the lock first, because there is no coordination of the threads from the start to the update statement.
This is no special problem of Oracle but that is typical when working with threads. If you want to guarantee some order of processing you are responsible to coordinate this. For example your first thread requests a lock L (in your program, on your client or in the database, what ever is necessary)) and after it got the lock the second thread is started and requests the lock L. But L is released not before the first thread has finished.