Bypass Locked Records in SQL Queries

Published: January 14, 2009

Dear Intelligent Colleague:

This tip is a follow-up to both of the tips I published last week. Like Bypassing a Locked Record, Take Two, it has to do with locked records. Like Be Specific When Updating With SQL Cursors, it deals with SQL updates.

You may remember that there are two ways to update in SQL. The positioned update uses a cursor to update one row at a time, as we talked about last week. Updating through a cursor is similar to updating with native I/O.

The searched update, the easier and more powerful of the two, does not work through a declared cursor, but updates a set of records at once. I have written about these types of updates before.

If you attempt a searched update, and one of the selected records is locked, the system sets the SQL state variable to a non-zero value, and one or more of the updates will not have taken place. If you need an all-or-nothing update, you can roll back the update when it fails. But if you want to skip locked records, V6R1 has a new option you need to know about. Take a look at following SQL update, paying special attention to the last line.

UPDATE CustMaster SET chgcod = 7
   WHERE state = 'TX'
      WITH CS
         SKIP LOCKED DATA

SKIP LOCKED DATA tells the system to ignore rows (records) that are locked by other transactions.

The SKIP LOCKED DATA clause is available only in the cursor stability and read stability isolation levels, hence WITH CS in this example. You can use SKIP LOCKED DATA with SELECT, UPDATE, and DELETE commands.

--Ted

RELATED STORIES

Bypassing a Locked Record, Take Two

Be Specific When Updating With SQL Cursors

Updating through an SQL Cursor

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot