Bypass Locked Records in SQL Queries
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
|
