Detect record lock

[Gary 18]

Hello all<

 

I didn't realize this was such a difficult problem.

 

I am using Devart UniDac and MariaDB (MySQL). I simply want to check in the OnBeforeEdit event of a dataset if the record is locked and display a message to my users. The UniTable has LockMode set to lmPessimistic. I have scoured Google for hours and there is no direct answer.

 

What I have tried is to add an OnEditError handler to deal with it, however it just hangs with a SQL cursor and finally gives the timeout error. 

 

Expecting users to wait for a timeout error is not acceptable. How can I check if the record is locked in the BeforeEdit event handler??

 

Thanks in advance

Gary

[programmerdelphi2k 237]

in fact, if your Dataset try "POST" a record (that is in Edition for another user), will be enough to show a error message!

• example in FireDAC: all options in default usage!!! just change the options below...
  • FDconnection = regular setup,nothing special here!
  • FDQuery with "select * from tablex" (with 2 transactions components) -- you can use a FDUpdateSQL if necessary.
    • LOCKMode=Pessimist, LOCKPoint=imediatly, LOCKWait=false
    • FDTransaction1 = READ-ONLY, READ-COMMITED
    • FDTransaction2 = READ-WRITE, SNAPSHOT
    • you want use the "FDQuery" events: OnError, OnEditError, OnPostError, OnExecuteError etc... to do something, do it.

now run 2 copy of your EXE and try "Edit" same record.

1. exe1, Edit a record and dont post ... wait
2. exe2, Edit  and post same record
3. now, on exe1 try post the changes and see the error message.

 

[programmerdelphi2k 237]

using Devart UniDAC you can see this post on official Devart forum 

https://forums.devart.com/viewtopic.php?t=3089

[Gary 18]

Thank You for your response.

 

More info: I am not using transactions. The Uni example uses the Connection level lock and edits/Posts in code. That post had the same problems of waiting for a time out. They tried setting the lock type (lrimmedialty  or IrDelayed) and still had problems. I would like to handle this at the DataSet level.

 

I'm using data aware components, so for example (the real life big one) one user starts to edit a DBMemo component(They use this to log call conversations and service calls), another user try's to edit the same customer's memo. I want to notify the second user the record is locked for editing, not wait until they have added  conversation notes and try to post the edit.

[programmerdelphi2k 237]

A long time ago, there was a trick that was done to cause a "deadlock"...
You would "EDIT" the desired record, post it in the database but "DON'T COMMIT"...
So, that made the other users of the table know that the record was edited, however, it was not posted in the database... and, if someone tried to do the same, they would get a "deadlock"

[Gary 18]

Unlike Firebird/Interbase there is no Commit in MySQL(MariaDB), I think?

 I just tried editing a memo, then tried with a second connection - Time out started - then quickly canceled first edit and lock released - second edit good to go.

 

Is it just not possible to do the following:

 

procedure TdmPoolDetails.tblPoolsBeforeEdit(DataSet: TDataSet);
begin

 if DataSet.Locked then

begin

  Post record locked Message;

  Abort;

end;

end;

[Gary 18]

More correctly the DataSet could have other record locks, but the BeforeEdit would know the record in question. If not then instead of is DataSet.locked it would be if CurrRecord.Locked or the substance of.

[Stano 143]

Record locking is one of the reasons why many do not use "DBEdit" type components. You definitely didn't want to read that.
It leads me to think of using TMemo. Unfortunately, I can't think of a proper way to resolve the conflicting record access situation. It would have to be server side.
Maybe someone knows.
I personally use Firebird and it does not use record locking in normal mode.
OT: in that case, how do you handle a situation where someone starts editing a recording and goes to lunch? Will you shut down all users for that time?

[Gary 18]

Agreed, however this is a small company with 4 users in 2 offices next to each other over a LAN connection. Leaving for lunch has happened 🙂. When they return their co-worker looks across the desk and says "Quit doing that". Handled!

Seriously though I also read posts about "Ghost" locks, not sure what they are, but sounds as if a problem occurs while the record is locked it could leave the lock in place indefinably.

 

Recently they did a price increase, several times they ended up editing the same record in Quickbooks (much bigger program than mine). When they would try to post the record they would immediately get a message telling them to try again. They have asked me to do the same. Simply checking if the record is being edited by someone else, either before editing or posting seems like an easy fix, until you try to implement it.

 

  

[Anders Melander 1783]

13 hours ago, Gary said:

I simply want to check in the OnBeforeEdit event of a dataset if the record is locked and display a message to my users.

Forget about that. It will never work in a multi-user environment.

You cannot "check for locks" without acquiring a lock. See race condition.

 

10 hours ago, Gary said:

Unlike Firebird/Interbase there is no Commit in MySQL(MariaDB), I think?

Yes, there is. All statements are executed in a transaction.

Your DevArt connection settings are probably set to use implicit auto transaction so this is hidden from you but I'm positive that you can configure it so transactions must be made explicitly.

 

2 hours ago, Gary said:

seems like an easy fix, until you try to implement it.

It's actually not that hard once you understand the different transaction isolation levels. I suggest you start there: https://mariadb.com/kb/en/mariadb-transactions-and-isolation-levels-for-sql-server-users/

[Gary 18]

Thanks Anders