Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Thursday, September 14, 2006

Avoiding Deadlocks: SKIP LOCKED

After reading Tim's article on Deadlocks, I've recalled solution that can help avoid deadlocks in some situations.
Assume that in our application we have to choose few (say 5) documents from pool to assign to users for processing. Usually we can use just update statement like that:
UPDATE documents
SET doc_state = 'ASSIGNED',
  doc_user = USER
WHERE doc_state = 'TO PROCESS'
 AND rownum <= 5;
But this can cause some waits in highly loaded environments where this statement is run often. To overcome that we can use SKIP LOCKED clause:
BEGIN
  FOR r IN
    (SELECT id FROM documents
     WHERE doc_state = 'TO PROCESS'
     AND rownum <= 5 FOR UPDATE SKIP LOCKED) LOOP
    UPDATE documents
    SET doc_state = 'ASSIGNED',
      doc_user = USER
    WHERE id = r.id;
  END LOOP;
END;
In this statement we do not suffer from waits, and we get up to 5 rows assigned to current user.

Cheers, Paweł

No comments:

 

Copyright © Paweł Barut
Printing from DOS to USB Printer