The situation is we have to do massive Deletes on a PRINT****NTIME table. A simple "delete" causes an Exclusive X lock on the table. While we're doing the delete, there are programs that need to insert rows to the end of this table. I say "end of the table" because the index is sequential and we're deleting lower index rows in the table at the same time that we're adding to the end (new, higher sequential indexes).

Currently we are getting deal this error: SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "68".

Is there a way around this problem? We have to do deletes on most of the table rows (at the beginning, not at the end) but there will always be something inserting at the same time.

Do you have any suggestions or has anyone encountered this issue?
Thank you.

CC

Here are a few items to think of.
how are you doing the deletes, a single row, multiple rows? delete where time <= host-var? delete where time = host-var?
are the deletes using an index or tablespace scan?
do they have to be done right away or can you perform the deletes at end of day/week/month when there is a period of time when you are not as busy? and can you issue commits between deletes?

Dave

So, what is timing out: inserts or deletes? Inserts should not be affected, unless there is lock escalation during the delete, for which there are only two solutions: either increase the lock list size (and MAXLOCKS) to accommodate large transactions, or make your transactions smaller. You should also consider table partitioning, if it is available in your version of DB2.

You didn't mention what version/OS you are on. Z/OS you can use reorg with discard option, I don't remember a similar command on LUW. If on LUW or Z/OS there are scripts/programs that you can google and copy that turn a single delete statement into a delete x number of rows and issue commits process.

Dave

Try setting these environment variables:

db2set DB2_SKIPDELETED=ON
db2set DB2_SKIPINSERTED=ON
db2set DB2_EVALUNCOMMITTED=ON

then stop and start instance.

But you should also use a cursor to do the deletes and commit after every 100 or 1000, or so deletes). Define the cursor WITH HOLD to keep it open even after the program does a commit. This would have to be done in an application program or Stored Procedure.