469,604 Members | 2,376 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,604 developers. It's quick & easy.

Does DB2 v 8.1 need UPDATE to get a pessimistic lock?

Hi.

Given the following:

----------------
String lockSubscriptionQuery =
"SELECT subscriptionId FROM subscription WHERE subscriptionId = ?
FOR UPDATE";

pStmt = conn.prepareStatement(lockSubscriptionQuery);
pStmt.setLong(1, a_Conference.getSubscriptionId());
SqlHelper.executePreparedQuery(pStmt);
pStmt.close();
-----------------

Q1: Will the last close() release the pessimistic lock?

Q2: My understanding was that SELECT...for UPDATE got a pessimistic
lock. But my tests show I need to UPDATE the row just after to get the
lock.

Q: Should we close the statement later if we want to hold the lock for
longer?

The following URL
http://java.sun.com/j2se/1.4.2/docs/...nt.html#close()

Says "Releases this Statement object's database and JDBC resources
immediately...."

Wondering what DB2 will do. How to get a pessimistic lock? etc....

Any help would be greatly appreciated. Thanks.

Joel

Sep 8 '06 #1
4 5482
<mi************@yahoo.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
Hi.

Given the following:

----------------
String lockSubscriptionQuery =
"SELECT subscriptionId FROM subscription WHERE subscriptionId = ?
FOR UPDATE";

pStmt = conn.prepareStatement(lockSubscriptionQuery);
pStmt.setLong(1, a_Conference.getSubscriptionId());
SqlHelper.executePreparedQuery(pStmt);
pStmt.close();
-----------------

Q1: Will the last close() release the pessimistic lock?

Q2: My understanding was that SELECT...for UPDATE got a pessimistic
lock. But my tests show I need to UPDATE the row just after to get the
lock.

Q: Should we close the statement later if we want to hold the lock for
longer?

The following URL
http://java.sun.com/j2se/1.4.2/docs/...nt.html#close()

Says "Releases this Statement object's database and JDBC resources
immediately...."

Wondering what DB2 will do. How to get a pessimistic lock? etc....

Any help would be greatly appreciated. Thanks.

Joel
A "select for update" will take a lock on the row, but that kind of lock
does not prevent selects from other applications. It will only prevent other
select for updates, updates, or deletes of that row by other applications.
To get an exclusive lock that will prevent even reads from other
applications you need to update it or delete it without a commit.

All locks are released when a commit or rollback happens. I don't know
enough about Java to say it the pStmt.close() issues a commit.

Remember that for locking purposes, only other applications are locked out,
and other SQL statements in the same application are not locked out by their
own previous SQL statements.
Sep 8 '06 #2
Q1: Will the last close() release the pessimistic lock?
No you need to either commit, rollback, or release to release locks.
Paul
Sep 9 '06 #3
Check if your version of the USE AND KEEP UPDATE/EXCLUSIVE LOCKS in the
isolation clause. It was introduced in DB2 V8.2 I believe
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 9 '06 #4
Thanks very much.

That was a big help.

-Joel

Sep 11 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by G.W. Lucas | last post: by
4 posts views Thread by pike | last post: by
1 post views Thread by contactrajib | last post: by
3 posts views Thread by Michel Esber | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.