hi i know that i mysql i can lock a racord/row like this :
select * from invoice where invoice.id=10 for update
this must lock record invoice where id=10…
i don’t understand how, from inside rs/xojo code (on other instance of app) , check if record that i want edit is locked from other user/app instance …
there is a system to make this check without use error/exception interception ??
If you do the SELECT FOR UPDATE a second time then I would expect you get a DB error. I’m not aware that MySQL provides another way to check for locks.
I’m not very familiar with MySQL, but why do you need to lock the record in the first place?
i must lock record or records to make updates …
and there are two ways, the first is lock table, the second is lock record… , i prefer lock records
but my problem is… .when i have locked records with mysql syntax : “select * from invoice where idInvoce=1 for update”
if i try to manipulate these records from other client, i would like have a system to check if these records are locked from other users.
at now i know only one way, and this is try to update record and check if db.error is true … but this is not easy…
I don’t know if this works on MySQL, but I expect it would…
I always have a version number on the record and so my update statement would be:
UPDATE … SET …, VerNo = VerNo + 1 WHERE … AND (VerNo = CurVer)
Where CurVer is the number you read when you retrieved the record.
If the record changed since you loaded it, your update will fail and you can tell the user the update failed as the record was changed by another user.
And of course add transactions when necessary.
Locking and transactions are the ways to control concurrency and atomicity.
Your pseudo versioning of the record will silent fail and destroy the data integrity.
Use BEGIN your operations COMMIT
A SELECT inside a transaction locks (shared lock) the table ( Transaction )
Unfortunately SQLITE (at least <=3.7) uses table locking, not record locking.
The second concurrent transaction racing for change should fail and you can treat the situation inside a Xojo TRY CATCH END
Do some research. Read and try.
http://www.sqlite.org/lockingv3.html
http://www.sqlite.org/atomiccommit.html
http://www.sqlite.org/wal.html
[quote=61315:@Rick Araujo]Locking and transactions are the ways to control concurrency and atomicity.
Your pseudo versioning of the record will silent fail and destroy the data integrity.
[/quote]
Well first of all it will not destroy data integrity because nothing gets written to the database unless the version number is the same. Secondly you check the number of rows affected after executing your update to discover if your call failed. You leave all of the locking to the database engine itself, because there is less chance of deadlocks than when application programmers start doing it. Not to mention poor performance. This is the usually way we squeeze performance of out of the DB server when building trading systems and the likes where there can be thousands of transactions hitting the database every second.
And as I already said you can wrap it in a transaction if necessary.
Ok, checking for no updates and redoing the entire job, will make it work. But this way you will mimic, in a expensive way (storage and probably speed) locking mechanisms already there.
The system currently can be very responsive avoiding disk usage and unnecessary locks. Avoiding some locks can be a question of design. But having locks is a question of necessity.
thanks Rick, i use mysql server and i know that i can begin transaction the make updates and commit…
and i know i other client try to updates these record receives an error/exception and i can check the property db.error but
i would like know if with db class there are a system to know in a record is locked by other users without try to update the record…
sorry in last post i have make a lot of error…
thanks Rick, i use mysql server and i know that i can begin transaction then make updates and commit…
and i know if other client try to updates these record receives an error/exception and the property db.error is true , but
i would like know if record is locked before that i try to update this
Exactly how??? You are making simple calls to the database which the optimiser should be able to benefit from, you allowing the database server and the DBA to make full use of caching etc… you are getting in and out as quickly as possible and in most cases you are going to succeed in executing the query.
I’m not sure what you mean by having to redo the whole job, if your update fails you inform the user, perhaps show him the updated record and ask if he wants to continue, if so you update the version number execute the command and continue.
Where exactly does the unnecessary locks and disk usage come in?
As I have said I’m not very familiar with MySQL, but a few comments from their manual, would suggest one needs to be extremely careful about row locking, especially if you are trying to support large numbers of transactions:
This does not sound too good as a starting point for pushing say 500 or 1000 transactions per second!
Someone here told somenting about missing a cup of coffee before writing. I did the same about a part. I was talking about SQLite and the questions were about MYSQL. 
But, anyway, for those interested, read this: http://dev.mysql.com/doc/refman/5.7/en/locking-issues.html
No, you can not check to see if a record is locked for update with standard SQL. If you issue a second update, it will simply wait for the lock on the record to become available and then do the update. The second update might timeout if it has to wait longer than the system timeout setting, but that might indicate other issues in the system. You want to make the time that you hold exclusive update locks as short as possible to keep the system responsive.
[quote=61300:@Jury Buono]i must lock record or records to make updates …
and there are two ways, the first is lock table, the second is lock record… , i prefer lock records
but my problem is… .when i have locked records with mysql syntax : “select * from invoice where idInvoce=1 for update”
if i try to manipulate these records from other client, i would like have a system to check if these records are locked from other users.
at now i know only one way, and this is try to update record and check if db.error is true … but this is not easy…[/quote]
I guess the only way is to create another column tinyint(1) or boolean to your table. And use that as a flag for your records. and remove the flag when you have a success commit
yes John, i can add another column to manage locking manually, but because the locking tecnology is integrated in database engine i would like use this and not manually locking.
The problem of use db engine locking is that if wont know if a record is locked i must intercept a db.error and this is not friendly.
I would avoid locking records like this
Its a sure fire way to write a system that experiences deadlocks
Use transactions & versioning like James outlined
Imagine you write a data entry panel & someone starts to edit a record
So you select for update
Then they go to lunch
Now no one else could touch that record - even if they wanted to (and if it was time sensitive like getting payroll done that could be a problem)
But in the scenario James outlined someone else could update the record & when the per on came back from lunch and tried to save the changes they’d get a notification that “your change could not be saved as the record is newer than the one you were editing” or something.
no i think to a system where i show the record, then users can makes changes (only to screen) and then tries to update the record.
the update of record is only when users clicks on SAVE button.
the update record must check if records is changed by other users, checking if a filed like timestamp is changed.
then the procedure tries to lock original record then if there is no db.error update record then relases lock.
[quote=61577:@Jury Buono]
then the procedure tries to lock original record then if there is no db.error update record then relases lock.[/quote]
Yes, but there is no need to lock the record at all, simply use the timestamp, along with the primary key in the where clause and check the number of rows affected.
‘FOR UPDATE’ is something DBAs may use from time to time, but it not something application programmers should be using on a regular basis for updating records.
‘FOR UPDATE’ is one of those things that DBA’s tend to beat application programmers for using 
I’ve been on both sides of this
i would prefer to have a class.proprierties to know if record is locked … 
and would like have the peace in the world, more money for everyone, no disease, no hunger in the world
ahahahahaha
thanks to all, i very much appreciate everyone’s help.