473,372 Members | 1,059 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Row Locking Problem

Ray
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

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.

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
Nov 12 '05 #1
2 19486
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

Nov 12 '05 #2
br****@lsmp.com (Ray) wrote in message news:<f3**************************@posting.google. com>...
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

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.

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


Reason code 68 is a lock timeout (RC 2 is a deadlock). You are in
contention with some other applicaiton that is also accessing that
table.

You may be experience lock escalation to the table level which makes
contention more likely. This is usually because the locklist is too
small to store all the row locks. Increase the db cfg parm for
locklist to at least 2048 pages. You might also want to increase the
maxlocks to 35 (percent).
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Vinay Aggarwal | last post by:
I have been thinking about the lazy initialization and double checked locking problem. This problem is explain in detail here http://www.cs.umd.edu/~pugh/java/memoryModel/DoubleCheckedLocking.html...
14
by: Sin | last post by:
I've tried everything and I've come to the conclusion that I'm screwed. If anyone can help, I would be eternaly greatful. I have a solution which has 47 projects.. This is includes (very roughly)...
2
by: oscar | last post by:
I have read Tony Toews's web site from which I have deduced I have an LDB locking problem. I have tried to follow his solution as follows: 1. I have created a dummy table in the back-end app....
2
by: Shaun | last post by:
Ok here is the situation… Have an access 20002 application that I'm converting to have a SQL Server backend (2000), the application has been in use with an access backend for years, no real...
0
by: Todd Matson | last post by:
I am having a file-locking problem with an Access database. The .mdb and ..ldb files both remain locked even after all users have closed Access. The project is an Access 2000 database split into...
1
by: dabuskol | last post by:
Hi, I'm trying to run an insert statement continously for 24 hrs, data is coming from different Ip/PORT (100). Could somebody please help me by checking my codes if there's any way I can do to...
0
by: alex | last post by:
I have few web pages that allow to upload, delete, etc images (.jpg, ..bmp, .gif) Once the page is doen working with file thru Bitmap class I close file handles as: Bitmap bmp = new...
4
by: Miky | last post by:
Hi, I wrote a data-entry application that has to be used by 80 people and it is using databinding to the dataset. When we were making the test on multiple machines, the application locked...
1
by: Syn | last post by:
Hello, I have a parent table(x_orders) and a child table(x_orders_contents), with a cascade delete on the child table. In a after delete trigger of the x_orders_contents table I'm updating a...
6
by: joseph2000 | last post by:
Hi, I'd like to ask you for some ideas how to solve problem I currently have. The problem is as follows: we have a component which is integrated with w Windows Explorer. From time to time we...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.