Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
@Sathya - can you elaborate the complexity of stored procedure? is the suspected table is under rigorous update or insert? –  Coder Hawk Mar 14 '11 at 11:16
    
Do foreign keys play a role here ?(sometimes this needs an index) What version of database is in place? Is the flow of the logic executed in the same order in both sessions? Is the transaction kept 'open' for a long time? Does the lock occur during the users think time or during active execution of the transaction? –  ik_zelf Mar 14 '11 at 19:40
    
@Sandy I've updated the question –  Sathya Mar 15 '11 at 5:28
    
@ik_zelf I've updated the question –  Sathya Mar 15 '11 at 5:28
    
It's not clear to me why this is a problem - Oracle is doing exactly what it's supposed to do, which is serializing access to a single row. If someone has that row, you can read the previous version of it, but to write you have to wait for them to release the lock. The only "fix" for it is to either a) not fool around and COMMIT or ROLLBACK in a reasonable time or b) arrange such that the same people don't always want the same row at the same time. –  Gaius Mar 15 '11 at 7:09

3 Answers 3

up vote 6 down vote accepted

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.
share|improve this answer
    
Thank you, Leigh. –  Sathya Mar 15 '11 at 14:42

This answer would probably qualify for an entry in The Daily WTF.

Right, after tracing the sessions and searching through USER_SOURCE - I tracked down the root cause

  • The cause, unsurprisingly was flawed logic
  • Recently, an update statement was added to the SP. The update statement would basically update the entire table. Apparently the developer in question forgot about adding the right where clauses to update the required statements.
  • The table being updated was as mentioned above, one of the most transacted tables and had a large number of records. The update would take a long, agonizing time.
  • The result was that other sessions were not able to get a lock on the table and would sit in row-lock contentions.
share|improve this answer

I will provide an answer from a developer point of view.

In my opinion, when you encounter a row contention such as the one you describe, it's because you have a bug in your application. In most cases this type of contention is a sign of a lost-update vulnerability. This thread on AskTom explains the concept of a lost update:

A lost update happens when:

session 1: read out Tom's Employee record

session 2: read out Tom's Employee record

session 1: update Tom's employee record

session 2: update Tom's employee record

Session 2 will OVER WRITE session 1's changes without ever seeing them -- resulting in a lost update.

You have experienced one nasty side-effect of lost update: session 2 can be blocked because session 1 has not commited yet. The main problem however is that session 2 blindly updates the record. Suppose that both sessions issue the statement:

UPDATE table SET col1=:col1, ..., coln=:coln WHERE id = :pk

After both statements, session1's modifications have been overwritten, without session2 having been notified that the row had been modified by session 1.


Lost update (and the contention side effect) should never happen, they are 100% avoidable. You should use locking to prevent them with two main methods: optimistic and pessimistic locking.

1) Pessimistic Locking

You want to update a row. In this mode you will prevent others from modifying this row by requesting a lock on that row (SELECT ... FOR UPDATE NOWAIT statement). If the row is already being modified, you will get an error message, which you can gracefully translate to the end-user (this row is being modified by another user). If the row is available, make your modifications (UPDATE), then commit whenever your transaction is complete.

2) Optimistic Locking

You want to update a row. However, you don't want to maintain a lock on that row, perhaps because you use several transactions to update the row (web-based stateless application), or perhaps you don't want any user from holding a lock for too long (which may result in other people being blocked). In that case you will not request a lock right away. You will use a marker to make sure that the row has not changed when your update will be issued. You could cache the value of all columns, or you could use a timestamp column that gets updated automatically, or a sequence-based column. Whatever your choice, when you are about to perform your update, you will make sure that the marker on that row has not changed by issuing a query like:

SELECT <...>
  FROM table
 WHERE id = :id
   AND marker = :marker
   FOR UPDATE NOWAIT

If the query returns a row, make your update. If it does not, this means that someone has modified the row since the last time you queried it. You will have to restart the process from the beginning.

Note: If you have a complete trust over all applications that access your DB, you can rely on a direct update for the optimistic locking. You could issue directly:

UPDATE table
   SET <...>, 
       marker = marker + 1
 WHERE id = :id;

If the statement updates no row, you know that someone has changed this row and you need to start all over.

If all applications agree on this scheme, you would never be blocked by someone else and you would avoid the blind update. However, if you don't lock the row beforehand, you are still susceptible to indefinite locking if another application, batch job or direct update doesn't implement optimistic locking. This is why I advise to always lock the row, whatever your locking scheme choice (the performance hit can be negligible since you retrieve all values including the rowid when you lock the row).

TL;DR

  • Updating a row without having a lock on it beforehand exposes the application to potential "freezing". This can be avoided if all DML to the DB implement optimistic or pessimistic locking.
  • Verify that the SELECT statement return values consistent with any previous SELECT (to avoid any lost update problem)
share|improve this answer
    
Awesome, thanks @Vincent –  Sathya Mar 16 '11 at 7:08
    
1  
@MartinSmith I feel that some of my points were lost with the edit (especially the importance of defensive locking). Still, there was some good suggestion and I merged some of the edit with my answer because the "traditional" way to implement optimistic locking skips the locking part entirely. –  Vincent Malgrat Nov 20 '12 at 9:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.