"Failed to acquire exclusive lock" on insert
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.
Comments
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 INSERTand don't forget
do ##class(my.class).%UnLockExtent(0,1)after your insert.
I got the same error occasionally when parsing X12 files. Lock table got filled up when saving a transaction that contains hundreds of objects.
Thanks Robert. So am I wrapping those two things around my object.%Save()?
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?
Yes.
it prevents an error message if there is a collision.
if nobody else is around it just falls through without loop.
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
If parallel insertion is not happening you can use ,
INSERT %NOLOCK INTO SOMETABLE (COL1) VALUES (:VALUE1)