Deadlock with Update Files

[Gav Participant]

We’re doing some preliminary work on a SQL migration project and came across an interesting situation.
I hope others can confirm/deny whether our analysis is correct.

Scenario:
– multiple datafiles
– a read/write file in datafile A
– a read/write file in datafile B

We noticed that after changing some code one day, we had a situation where all users were locked out from datafile A (couldn’t even open it) for about 15 mins. It transpired that someone had padlocked and decided to Ctrl-Alt_Del out of the situation. Clearly they had left a lock on the datafile that took a while to clear.

We have a theory that ‘Update Files’ is generating a deadlock if:
– one user obtains an exclusive lock on datafile A, then attempts a lock on datafile B
– another user obtains an exclusive lock on datafile B, then attempts a lock on datafile A

In such a case with SQL, the SQLServer would simply choose a victim and kill their transaction.
But with the Omnis ‘Update files’ command, this does not happen until somebody kills their Omnis task manually (Ctrl-break will probably not work).

Question 1. Why was the datafile completely locked so nobody could open it? (Datafile B was OK).

I understand record locking (Prepare for edit/insert/insert wcv).
But is the entire datafile locked momentarily when the ‘Update Files’ command is executed?
i.e. Does the PC performing the update place an exclusive lock onto the whole datafile, whilst updating the data and index blocks inside? This would make sense in order to protect the integrity of the datafile.

Question 2. If I introduce ‘Do not wait for semaphores’ before ‘Update Files’, will this command initially fail with flag false if it is unable to place that exclusive lock on all required DF1’s?

If this is the case, then it would be advisable to do this and place the ‘Update files’ command in a repeat loop until flag true, whenever multiple datafiles are in use.

Thanks v.m.
Gav

[Author]

Posts