By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,237 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,237 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
<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

P: n/a
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

P: n/a
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

P: n/a
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.