Ray wrote:
I have a list of about 20,000 rows that I am updating. I loop through
each row in my program and basically do the following
(1) select * from TABLE where SID=[currSID] for update /*lock the
row*/
(2) Update TABLE set ACCESS_DATE=current date where SID=[currSID]
(3) Do not commit
(4) Next row
You select the row for update then issue a separate SQL statement to
update the row?? Did you ever hear of "update ... where current of ..."?
After each SID in the list is finished being updated I want to commit
all the updates. If any update fails I want to rollback the entire
list.
SID is primary key - How can you commit "all the updates"? Performance
oriented coding (minimize logging) is to update all data in a row with a
single update statement. Perhaps you mean that you are updating multiple
columns in the row; each with its own update statement?
You want to rollback the updates after issuing a COMMIT??!! Commit means
that the updates are good and will not have to be backed out.
My problem: At some point during the loop (different row each time) I
get the following SQL error: "40001:[IBM][CLI Driver][DB2/NT] SQL0911N
The current transaction has been rolled back because of a deadlock or
timeout. Reason code "68". SQLSTATE=40001" when attempting to select
a row from the table.
I know each SID is only in the list once and it is the primary key of
the table. Why would I be getting a deadlock on a row I know I have
not locked or attempted to update? I thought that using the "for
update" only locked the row I was updating. Can someone help me?
Ray
You don't indicate what percentage of the table is being updated. If
enough of it is being updated that you expect to perform an update on
every page of data then your approach will most likely maximize I/O.
You want to undo all updates as soon as one fails. Are you meaning a
database update failure or an application failure? Application failure
would be that something else that was being updated is invalid or
perhaps a request to update a nonexisting SID.
If you need to verify update data other than SID then it should be done
before starting the updates. If you need to potentially back out 20k
updates then I'd change the application logic to the following:
1. Sort the list of SIDS to be updated into SID sequence.
2. Lock the table in exclusive mode. This will guarantee that you will
not have to wait for someone else to release a lock to allow your updates.
3a. Use a cursor (order by SID) and "update ... where current of ..." to
process all rows and programatically match/merge with the list of SIDS
to be updated.
OR
3b. Use individual update statements to update the rows that match the
list of SIDs.
4. Commit or rollback as necessary.
Choose 3a or 3b depending on which will yield the best performance. An
alternative, if supported by the code level you are using, would be to
put all of the updates into a work table and use the MERGE statement to
update your table. I'd suggest verifying that MERGE will do exactly what
your application requires.
Phil Sherman
