Graham, I suspect, if you are getting deadlocks, it may be down to the now infamous next-key locking on the index. Up until V8, DB2 used to take out next key locks on an index for a table which is deleted, updated or inserted in to. This was to prevent another application, inserting a row into a recordset which might then invalidate the Repeatable Read isolation level. Most applications do not use Repeatable Read, however, so you can overcome this behaviour by setting the following DB2 registry variable:-
"db2set DB2_RR_TO_RS=yes" Since V8, there DB2 uses type 2 indexes which apparantly don't have this problem. James -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: 29 July 2003 17:52 To: [EMAIL PROTECTED] Subject: [DB2EUG] Row Intent Locks DB2 V7.2 FP 7 I have an index XX_I1 on Column AA of Table XX. The table (xx) looks something like the following but with 300 values of BB per value of AA and many many AA's AA BB CC == == == 1 7 0 1 8 0 1 9 0 1 10 0 I issue (via a trigger) Update table XX set CC = CC + 1 Where AA = 1 and BB = 9 Access Table Name = XX ID = 5,40 | #Columns = 3 | Index Scan: Name = DB2INST1.xx_I1 ID = 1 | | Index Columns: | | | 1: AA (Ascending) | | #Key Columns = 1 | | | Start Key: Inclusive Value | | | | 1: ? | | | Stop Key: Inclusive Value | | | | 1: ? | | Data Prefetch: None | | Index Prefetch: None | Lock Intents | | Table: Intent Exclusive | | Row : Update | Sargable Predicate(s) | | #Predicates = 1 Update: Table Name = DB2INST1.XX ID = 5,40 Will I acquire U locks on all the data rows identified by the Index with a value of AA=1 until I find the data row with BB = 9? If the row with value AA=1 and BB=7 has an X lock will I wait to acquire a U lock before proceeding to my qualifying row? If I change the Index to be AA,BB will I get my U lock and update the row without clashing with the X lock on row AA=1 and BB=7? Thank you in advance for any advice, Graham. - ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] *** For more information, check http://www.db2eug.uni.cc - ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] *** For more information, check http://www.db2eug.uni.cc
