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

[misterutterbag]

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

[Mark 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.

[Paul Dembry]

Q1: Will the last close() release the pessimistic lock?
No you need to either commit, rollback, or release to release locks.
Paul

[Serge Rielau]

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/

[misterutterbag]

Thanks very much.

That was a big help.

-Joel