Firedac MySql pessimistic lock

[adyble 0]

Hi All

We have a specific requirement on some MySql tables using Firedac and Delphi 10.3 to lock records at the point of issuing a .Edit command for the FDQuery.

 

If we set UpdateOptions -

Lockmode = ImPessimistic

LockPoint - lpImmediate

LockWait - False

 

then all records are locked and not just the record I am editing. I cannot actually retrieve a "Lock" waring or error so having to trap this message :

 

      if E.Message = '[FireDAC][Stan]-700. Timeout expired' then //locked by a user or best guess at the moment

 

It's the only one that seems to ever get returned.

 

How does anyone else do it ?

Thanks

Andy

 

[MichaelT 6]

TFDPhysMySQLCommandGenerator.GetPessimisticLock in FireDAC.Phys.MySQLMeta

 

It seems that LockMode is not considered and in case of Lockpoint = lpImmediate FOR UPDATE is used and LOCK IN SHARE MODE lpDeferred and the WHERE clause is considered. To me this code looks like being part of preparing the select SQL for the command. I have checked the FireDac sources related to MySQL and the only line found was this one in line 679 in the code shipped with Delphi 10.4.1.

 

Since I don't use MySQL or MariaDB very often, not even rarely. I simply use AUTOCOMMIT or distinct sets of records.

 

Maybe I don't remember correctly but this behavior was and very likely still is by design. I remember a discussion long long time ago about MyISAM (extensive locking in general) and InnoDB on the other. Heaven knows if that's still (a|the) reason today. It seems that decision done these days to offer AUTOCOMMIT on one hand and the compromise found an described above.

 

I simply do it that way. I'm no fan of locking in general and indeed I prefer non-committed reads, because if my code is not dirty enough, at least the blocks read from the DB should. Kidding. Usually read committed is enough for me.

 

 

[adyble 0]

Thanks for the reply. I never thought to check firedacs sources. I'll have a look around.