What kind of lock will be employed if I do select ..... for update? say, for example, table employee contain the following data:

id Name
-- -----
1 John
2 Mary
3 Peter

If I do "select * from employee where id=2 for update"

What lock(s) will be employed on the row with id=2?

Other programs can select the row, but no other program can do another select for update, or update, or delete on the row.

Thx Marcus_A,
From documentation, it say that other txn can not do select... for update, but I did the test, and find that other txn can do the select .... for update.

I opened 3 CLPs, Windows A, B and C.

Step 1) Windows A:
db2 +c "connect to db1"
db2 +c "select * from test1 for update"
INT1 INT2
----------- -----------
1 2

1 record(s) selected.


Step 2) Windows B:
db2 +c "connect to db1"
db2 +c "select * from test1 for update"

INT1 INT2
----------- -----------
1 2

1 record(s) selected.

By documentation, it should be locked by txn of Windows A, but in fact it doesn't


Step 3) Windows C:

db2 get snapshot for locks on db1

Database Lock Snapshot

Database name = DB1
Database path = D:\DB2\NODE0000\SQL00001\
Input database alias = DB1
Locks held = 4
Applications currently connected = 2
Agents currently waiting on locks = 0
Snapshot timestamp = 11/17/2005 14:21:11.189036

Application handle = 201
Application ID = *LOCAL.DB2.051117061930
Sequence number = 0001
Application name = db2bp.exe
CONNECT Authorization ID = MATTHEWLAU
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1252
Locks held = 2
Total wait time (ms) = Not Collected

List Of Locks
Lock Name = 0x53514C4332453036BD4A32C841
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Plan Lock
Mode = S

Lock Name = 0x53514C4445464C5428DD630641
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Plan Lock
Mode = S


Application handle = 200
Application ID = *LOCAL.DB2.051117061925
Sequence number = 0001
Application name = db2bp.exe
CONNECT Authorization ID = MATTHEWLAU
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1252
Locks held = 2
Total wait time (ms) = Not Collected

List Of Locks
Lock Name = 0x53514C4332453036BD4A32C841
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Plan Lock
Mode = S

Lock Name = 0x53514C4445464C5428DD630641
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Plan Lock
Mode = S


All all Internal Plan Lock, no update lock

Use cursors. Because what I think is the case here is that when you execute just a SELECT statement, it will open a cursor, fetch and close.
But the locks remain only if you either specify the cursor WITH HOLD or you don't close and then the second comes in in parallel.
See also: http://publib.boulder.ibm.com/infoce...n/r0000937.htm

Also: when using a cursor in CS only the current row will be locked. If you would like to lock every row in the resultset, you will have to use RS isolation level.

Would you care to quote that documentation?

The one I read says that "SELECT ... FOR UPDATE" only affects subsequent positioned updates and row _blocking_ (this is not the same as "locking").

"Use the FOR UPDATE OF clause when performing a select. This clause ensures that a U lock is imposed when process A attempts to read the data. Row blocking, however, is disabled. " Quote from Admin. Guide: Performance

thx,
We are using jdbc, how can I code the jdbc that specifying the cursor WITH HOLD. thx.

I think what actually happens to the rows selected with the FOR UPDATE option depends on the isolation level currently in effect.

If I run a query (with a FOR UPDATE) using CS isolation a row is only locked with U lock while the cursor is positioned on that row. After the last row is fetched by the internal cursor created for that dynamic SELECT statement no U locks remain in effect. Below are two snapshots; one is taken while the query runs:

the other after the last row has been retrieved:
If I run the same query using RR isolation the entire table is locked with a U lock and it remains in effect until the transaction is committed. Here's a snapshot taken while the query runs: