By Andrew Harper - 6/6/2016
Hi,
I am wondering if/how other users have implemented a logical lock mechanism in their applications to prevent more than 1 user accessing a business process at the same time? We did this in VFP by locking a record with a process specific key value in a designated table. If the lock succeeded we recorded the user name and datetime the lock was placed and proceeded with the process. If the lock failed we showed the user who had the lock, when it was placed and prevented the user from proceeding with the process. I realise that SQL does not have the same locking mechanism as VFP. SAP has a locking mechanism to handle this sort of situation but we do not need that level of functionality. Our immediate requirement is to prevent an business process being started if it has already been started by someone else. I will probably go down a similar route as we did with VFP but just wondering if there was a better way to do this? |
By Edhy Rijo - 6/7/2016
Hi Andrew,
I am not aware of any way of locking a record in the BO as in VFP, probably because the BO sessions are executed locally in each workstation, so there will be no way from user A on one workstation to know if user B in another workstation is locking a record.
I believe your best option is for you to use a flag Bit field ex "Locked" that your process will set to True and avoid any other process until it is released, just keep in mind that you will actually have to save the record either via the BO or a TSQL UPDATE command so other users will know if the record has been locked.
I still believe that this method is not a 100% reliable, but that would depend on how many user may you have that will try to lock the record at the same time.
|
By Andrew Harper - 6/7/2016
Thanks Edhy,
That is what we are planning to do - just wondering if there were alternatives.
regards, Andy
|
By Ivan George Borges - 6/8/2016
Hi Andy.
Don't forget that your process may crash and leave the record in a Locked situation forever. In our applications, we also have some places where an exclusive use of a record is needed. I will try to summarize what we do. We have a table called LockedRecords which will have, amongst others, fields to store the user, time stamp, table name and table record primary key, which will always be an integer for us. Every time I need to have a record locked, first we will check in that table if it is not there already, which will tell us it has been locked by someone else. If not, we create an entry in that table with information about our current record. So, now, it is locked. We also have an Application Service running on the server which our Client Application talks to via TCP Sockets. The Client Application will send regular information (in a BackgroundWorker process) to this Service while the record is locked. If the Service stops getting information from the Client about this specific record, it will automatically delete the locked record's entry from the LockedRecords table, releasing it to be used by another user, even if the Client crashes. Also, once the Client application doesn't need the exclusive use of the record, it will delete that entry from its side, leaving it free to be used by someone else. There are some other bits and pieces to the process, allowing us even to inform a user that has tried to edit a locked record that it has been released when it happens, as the Client will keep talking to the server Service querying this information. Hope I could help some more giving you ideas about it. |
By Andrew Harper - 6/8/2016
Hi Ivan,
Thanks for your detailed response, much appreciated.
We are proposing to go along a very similar route as you describe but had yet to come up with a solution that did not require operator intervention if a client crashed with a lock in place.
In VFP if the application crashed the lock was released. Your method where the client polls a server app while the lock is in place is a great idea, thanks.
best regards, Andy
|
By Ivan George Borges - 6/9/2016
Glad it helped, Andy. 
|
|