Deadlock with Update Files

Michael Mantkowski michaelj at clientrax.com
Mon Feb 27 13:14:07 EST 2017


I do this all the time.

Being in "Developer Mode" does not affect the record locking as far as I have ever seen.

Assuming all your file classes are set to Read Only and updated to Read Write at the proper time.

Both Mac and Windows.

*********************************************************************
Michael Mantkowski
ClienTrax Software
1-614-875-2245
*********************************************************************


-----Original Message-----
From: omnisdev-en [mailto:omnisdev-en-bounces at lists.omnis-dev.com] On Behalf Of Wendy
Sent: Monday, February 27, 2017 1:07 PM
To: 'OmnisDev List - English' <omnisdev-en at lists.omnis-dev.com>
Subject: RE: Deadlock with Update Files

Hi

I have just tried opening an application in Developer with it running on a different computer in Runtime..

Edit a record on the developer which locks the record - used Ctrl/alt/delete to bring up the task manager and shut the developer version and the file was unlocked.

I did the same the other way round and still the record was released.

This is running on Windows  may be different on a mac

Kind regards
Wendy Osbaldestin
Wizard Computer Services
Tel:  01260271647
Mobile: 07740541021


-----Original Message-----
From: omnisdev-en [mailto:omnisdev-en-bounces at lists.omnis-dev.com] On Behalf Of Gary Connor
Sent: 27 February 2017 16:40
To: 'OmnisDev List - English'
Subject: RE: Deadlock with Update Files

Gavin - 

If any user either (1) opens the DF1 using a developer serial number or (2) opens the file using another piece of software (test editor, etc) this will lock the file to the Omnis application, and if you are in a Windows environment (I don't know about OSX) the file will not be unlocked until the offending machine is rebooted.


Gary Connor, Ph.D., CIO, CISO
DirectLine Technologies, Inc.




-----Original Message-----
From: omnisdev-en [mailto:omnisdev-en-bounces at lists.omnis-dev.com] On Behalf Of Wendy
Sent: Monday, February 27, 2017 8:10 AM
To: 'OmnisDev List - English'
Subject: RE: Deadlock with Update Files

Hi Gavin

I have never had a file deadlocked using DF1 - the user crashing out or just shutting down their computer releases the locks and i have used multiple databases into the one application.

Kind regards
Wendy Osbaldestin
Wizard Computer Services
Tel:  01260271647
Mobile: 07740541021

-----Original Message-----
From: omnisdev-en [mailto:omnisdev-en-bounces at lists.omnis-dev.com] On Behalf Of Doug Easterbrook
Sent: 27 February 2017 15:53
To: OmnisDev List - English
Subject: Re: Deadlock with Update Files

hi Gavin.


a couple of thoughts, or maybe questions as I havn’t been in DF1 worlds for a a while.     If you put your set read/write filename in a reversible block at the and of the stack, so that killing the process will get out of the stack…. would that help??

you mention crtl-alt-delete   so maybe not-  they got stuck and used the big kill switch …  but perhaps limiting the possibility of locks in a reversible block might help.



In sql you are generally ok as anything done at the server is done in an implicit transaction (postgres it is for sure).      Deadlock can usually resolve itself — but you can plan to avoid it by being careful how you write your updates.



example two people doing:

begin tran
update file A  rec 1
update file B rec 1 and 2
end tran

and

begin tran
update file B  rec 2 and 1
update file A rec 1
end tran


will get you in trouble eventually.  There are two solutions …


1)  ensure that you update the files in the same order lessens deadlock and the update of file A rec 1 will put a lock for others that attempt to do the same thing.


2) alternately, …  in high transaction systems (we’ve done this in Theatre manager in some places like selling tickets)., you can

begin tran
update file A record 1
insert job into table telling a worker that file B needs updated with totals.
end tran



the fact that the second SQL call in the transaction is an INSERT means there is no possibility of deadlock


then the worker process happens some time later (ours is within milliseconds because we use listen notify), then the worker calculates totals into file B based on data from file A


If you are using postgres 9.5, you can use UPSERT instead of insert to save on io.   i.e.


INSERT in to jobtable ‘job to calc totals’ on constraint  failure using unique  key  do nothing.



that lest you put it into a trigger..






I know thats a bunch os pseudo code and philosophical discussion.        but I am saying we ran into limits with deadlock and updating multiple common tables — under heavy load.

And we solved it buy using a worker concept to update the second tables with data from the first.   UPSERT is an optimization on what we did if your SQL database supports it.



which is to also say — a real life use for upsert that works spiffy.


Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978




National User Conference, May 8-11, 2017
https://tickets.proctors.org/TheatreManager/95/online&perf=27956

> On Feb 27, 2017, at 8:21 AM, Gavin Foster <omnislist at dataweaver.com> wrote:
> 
> Hi all,
> 
> 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.
> 
> 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 in order to update the data and index blocks inside, so they are not corrupted by simultaneous writes from other users?
> 
> 
> Question 2. If I introduce ‘Do not wait for semaphores’, will the ‘Update Files’ command fail with flag false if it is unable to place that exclusive lock on the DF1? (In which case, I would place it into a loop until flag true.)
> 
> 
> Background to questions:
> 
> We have a theory that ‘Update Files’ is generating a cross-blocking 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).
> 
> Thanks v.m.
> Gav
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com



_____________________________________________________________
Manage your list subscriptions at http://lists.omnis-dev.com

NOTE: This email message and any attachments are for the sole use of the intended recipient(s) and may contain confidential and/or privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by replying to this email, and destroy all copies of the original message.

_____________________________________________________________
Manage your list subscriptions at http://lists.omnis-dev.com


_____________________________________________________________
Manage your list subscriptions at http://lists.omnis-dev.com





More information about the omnisdev-en mailing list