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 how to lock records
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.
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).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.
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.
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.
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.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.
Number 2, pls forget it - just to of debug...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?
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
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