Devart Forums

Is there any documentation on exactly how mydac handles recordlocking?

There are three modes :
TLockMode = (lmNone, lmPessimistic, lmOptimistic);

And as I understand it, when i perform a "edit" on a TMyquery, "lmPessimistic" mode there will be a automatic refresh and the new values are compared to what s already loaded so that if any changes have occurred after i executed the first select, the "edit" will fail.. or?

Sorry, i posted this in wrong forum 2...

MyDAC help says:

lmNone - No locking is performed. This should only be used in single user applications. The default value.
lmOptimistic - Locking is performed when user posts an edited record. After this the lock is released. Locking is performed by the RefreshRecord method.
lmPessimistic - Locking is performed when the user starts editing a record. The lock remains until the user posts or cancels the changes.

If LockMode is set to lmPessimistic, TMyQuery performs locking when user starts editing a record. In this case another user can not change the record until the first user posts or cancels editing.

Dimon wrote:If LockMode is set to lmPessimistic, TMyQuery performs locking when user starts editing a record. In this case another user can not change the record until the first user posts or cancels editing.

First i select a record, then i lock it and change one row. Problem is that many clients will do the same thing. I have a field named "client". If that field is empty its only supposed to be changed one time, bu the first client who access it. There is a gap between the select and the edit. So two clients (or more) can select the same row and change it two times (or more).

I can think of two solutions to this:

(1) The (read) lock will be enforced when the record is selected - but i think mydac can not do this.

(2) After the lock is enforced, i refresh the record and check manually that no other client have changed it. This I also cant do - i get error that i cant refresh a closed dataset.

I try to show the steps i make:

First i do a select:

Select * from MyQuery where length(computer)=0 limit 1;

// Now i have one record that no client have edit.

MyQuery.Edit;
.... I change field computer to the name of the clients computer.
MyQuery.Post;

Problem is, that if two clients select the same row before any of them have changed it, they can't know this and will both perform the change. It will be changed two times.

You can solve the problem by two ways:

1. Pessimistic locking (LockMode=lmPessimistic) - when you call the TDataSet.Edit method, MyDAC locks this record on server side and another user can not change the record until the first user posts or cancels editing. In this case when another user calls TDataSet.Edit an exception will be raised.

2. Optimistic locking (LockMode=lmOptimistic) - when user tries to post an edited record the RefreshRecord method is called and if any field of the record is changed an exception will be raised.

Dimon wrote:You can solve the problem by two ways:

1. Pessimistic locking (LockMode=lmPessimistic) - when you call the TDataSet.Edit method, MyDAC locks this record on server side and another user can not change the record until the first user posts or cancels editing. In this case when another user calls TDataSet.Edit an exception will be raised.

2. Optimistic locking (LockMode=lmOptimistic) - when user tries to post an edited record the RefreshRecord method is called and if any field of the record is changed an exception will be raised.

No I don't think so. There is a gap between the select and the edit. If two clients makes the select almost simultaneously, both can select the same record. Then if both update the record one at a time no exception will be raised. If the lock occurred at the same time as a record was selected, it could not happen - but when there is this gap between select and edit(lock) it can. If i do this in SQL code - it will work because there I can select and lock in the same operation - but with Mydac i have to first select and then lock(edit) so i crate a gap where two clients can select the same record.

Yes, looks like it works. So:

(1) the locking does compare before it saves?

(2) When the record is locked, i still get an exception even if i run "without debugging...". My exception block gets hold of it - but after that i still get a dialog i really don't need. How to get rid of that?

kaffeburk wrote:Yes, looks like it works. So:

(1) the locking does compare before it saves?

(2) When the record is locked, i still get an exception even if i run "without debugging...". My exception block gets hold of it - but after that i still get a dialog i really don't need. How to get rid of that?

Number 2, pls forget it - just to of debug...

The Gap:

I have the program running with pessimistic lock. One record in 1000 get altered twice. Not to bad, but not perfect either. The small gap between a select and a edit does matter. Is there any workaround?

I have one examle here:

mysql> LOCK TABLES items READ, temp_report WRITE;
mysql> SELECT sum(price) FROM items WHERE cust_id=1;

mysql> UPDATE temp_report SET purchases=438.65
WHERE cust_id=1;
mysql> UNLOCK TABLES;

Above the row locks BEFORE the first select is executed.

With Mydac:

Select * from items where cust_id=1; // No lock - just a select
MyQuery.edit // Here the lock is activated

So there is a small gap between row 1 and 2. It only affect one transaction of 1000, but that is not good enough

To solve the problem use the TCustomMyDataSet.LockTable method.
You can find more detailed information about this in the MyDAC help.