RE: [DB2EUG] locks during inserts

[Sinha, Abhijit]

Title: Message

If you do not care about the data being selected, i.e. the SELECT may contain the rows being inserted, you can use "WITH UR" option in your SELECT sql. The disadvantage is that if a transaction has inserted say, 1000 rows and meanwhile your SELECT with UR has selected data from among these 1000 rows, and then the insert txn rolls back, you end up with non-existing rows in your resultset.

 

Otherwise, try issuing frequent & intermittent commits during your insert.

 

Third, along with option 2, you can try setting the DB2 registry variable DB2_RR_TO_RS=YES (turning it ON). This will reduce next key locking during the inserts. For more details, read the V7 Administration Guide: Performance. Appendix A - DB2 Registry and Environment variables.

 

Also check your LOCKTIMEOUT database parameter and see if lowering or increasing it improves your situation.

 

Abhijit

 

-----Original Message-----
From: steve shapero [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 19, 2002 4:47 PM
To: [EMAIL PROTECTED]
Subject: [DB2EUG] locks during inserts

hi group--

 

i am doing some large inserts into a UDB 7.2 db from an Access client.  during this time, if  i try to run a select on this table, i get

 

SQL0911N  The current transaction has been rolled back because of a deadlock
or timeout.  Reason code "2".  SQLSTATE=40001

 

should i be using a different isolation level?  i need to be able to do the selects so my website doesn't go down when i am doing updates...

 

sorry if this is a stupid question...

 

thanks

steve