"Failed to acquire exclusive lock" on insert

Question

Question

Paul Riker · Jun 6, 2019

I have a custom process that is parsing HL7 and inserting it into a table. Periodically the inserts fail with # due to error: ERROR #5803: Failed to acquire exclusive lock on instance of....

Traditional databases would wait until the lock is removed then do the insert, but cache fails. I'm sure it's my coding approach.

How can I work around this? A Try/Catch loop?

Thanks in advance.

Discussion (7)2

Log in or sign up to continue

Robert Cemper · Jun 6, 2019

for an insert you may need and exclusive lock for your table

do { 
     set gotit=##class(my.class).%LockExtent()  
     if 'gotit hang .3 
   } while 'gotit
;;  now do your INSERT

and don't forget

do ##class(my.class).%UnLockExtent(0,1)

after your insert.

4 0

Robert Cemper · Jun 6, 2019

normally there is no need for such "wrapper"

typically %Save() returns a %Status object and then it's up to you to analyze it in case there is an error.
In the situation described here, all you can do is just a retry.
- it could be a Lock collision
- or the LockTable is full.
Instead of fiddling in system parameters requiring a restart, you just wait and retry.

if you are curious how often this happens you may add a loop counter. for further decisions

1 0

score 0 · Answer 1 · 2019-06-06T13:02:20-04:00

I got the same error occasionally when parsing X12 files. Lock table got filled up when saving a transaction that contains hundreds of objects.

score 0 · Answer 2 · 2019-06-06T13:57:46-04:00

Paul Riker · Jun 6, 2019

Thanks Robert. So am I wrapping those two things around my object.%Save()?

0 0

score 0 · Answer 3 · 2019-06-06T15:54:46-04:00

Robert, does it mean every time I have two processes accessing a table, I have to insert in this way? Or is is only about some certain scenarios?

score 0 · Answer 4 · 2019-06-06T18:27:00-04:00

Yes.

it prevents an error message if there is a collision.
if nobody else is around it just falls through without loop.

score 0 · Answer 5 · 2019-06-09T01:06:31-04:00

Suman Samanta · Jun 9, 2019

If parallel insertion is not happening you can use ,

INSERT %NOLOCK INTO SOMETABLE (COL1) VALUES (:VALUE1)

0 0