Progress OpenEdge – how to remove lock from table

1. Are you 100% sure that this process is escalating to a tablock? You can verify this with Adam Machanic's sp_whoisactive. Take a snapshot (execute the proc) while the process is running (and presumably blocking). Collect the locks and the query plan - this will give you information needed to fix this issue. Please note what the spids waiting in the insert are waiting on...If they are waiting on latches -we have a page split situation locks its a blocking/locking issue

2. Remember lock escalation happens when a single statement graps 5K locks on an table or index. (http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx) I have found that using the paglock hint (so you take fewer coarser locks) can be used to avoid tablock escalation.

3. Take a look at the query plan collected in step 1. What is the cardinality estimate of the row being insert (i.e. the select statement)... Is it much greater than 5K? If it is you might want to code a loop - so that you are only inserting 2500 rows at a time...

The UPDATE query has an X lock on a key on "dbo.ACCOUNTS" blocking the SELECT from getting an S lock.

The SELECT query has an S lock on a key of htt_customers_overlay_ultra. The UPDATE query has a U lock on the same key and is blocked trying to convert that to an X lock.

The execution plan for the UPDATE doesn't feature Accounts at all so there is no obvious reason for it to have a key lock on Accounts. The Update transaction starts 0.01 seconds before the batch does. 2013-01-13 08:49:30.213 vs 2013-01-13 08:49:30.223.

Perhaps there was a preceding statement in a different batch (hence not shown in the deadlock graph) that actually started the transaction and acquired the mysterious X lock.