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:
Post a Comment