This will be affected by the isolation level at which you run.
Let's presume you are using the standard default, CS for Cursor Staibilty.
Locks on table that have an " I " as in IS, IX, SIX provide row level locking.
Table: IS lock - Intent Share
Requested when any statement attempts to READ one or more row on the table.
If the result set is not materialized, the lock will be share on the row for the
short (ms) time required to pass the row to the coordinator agent. As the cursor is
moved to the next row, the row lock is released.
Table: IX lock
Requested on table if statement will modify (insert, update, delete) the row(s).
For positioned updates, the optimizer will evaluate the selectivity of the stmt.
to see if the number of update locks held on rows would fit withtin the maxlocks value
of your db cfg. If so, you get IX on table, Update on row until the update,then the
row lock is converted to exclusive or released if the update
is not done.
If maxlocks were to be estimated to be insufficient by the optimizer, you would
get Exclusive on the table and no row locks.
Given that these are estimates by the optimizer, when you run the stmt. reality
may force DB2 to escalate the IX lock and Update row locks to Exclusive on the table.
(this is bad for perf. and for deadlocks).
Row: X locks
See above.
Also, whenever you issue a stmt. like EXEC SQL UPDATE tabname SET C1= value
WHERE......
The optimizer will also estimate the selectivity and give you row locks of X
if this fits within maxlocks.
Whenever you DELETE or INSERT you also get X locks on rows.
HTH, Pierre.
KG Sivaramakrishnan wrote:
> Hi all,
>
> I would like to know when DB2 locks row in X mode , table in IX mode and Table in
>IS mode ? This is on UDB 6.1 on NT.
>
> Thanks in Advance
> Siva
>
> --
>
> This e-mail may contain confidential and/or privileged information. If you are not
>the intended recipient (or have received this e-mail in error) please notify the
>sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or
>distribution of the material in this e-mail is strictly forbidden.
>
> =====
> To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
> For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
begin:vcard
n:Saint-Jacques;Pierre
tel;cell:514-233-8679
tel;fax:514-737-1268
tel;work:514-737-4515
x-mozilla-html:FALSE
org:SES Consultants Inc.
adr:;;233 Simcoe Cr.;Mount-Royal;QC;H3P 1X1;Canada
version:2.1
email;internet:[EMAIL PROTECTED]
title:President
fn:Pierre Saint-Jacques
end:vcard
