howto add locks into mysql table
Hi experts,
I want to prevent that two people edit the same data record simultaneously.
One idea is to make a mysql table 'locks' with fields
tablename, entryid, userid, timestamp
and to add an entry whenever a user starts editing a record (and remove it when the edited form is submitted, also each time remove all entries that are older than one hour).
But I'm uncertain what's precisely the best method do do this:
I could make 'tablename,entryid' the primary key and try to add an entry.
If this fails then this entry is already locked (or some other error happened).
Or I could append another entry and then count if the number of entries with
this combination of tablename,entryid is 1.
Is there a reason to prefer one method over the other? Race conditions possible?
Or is there a completely different, better method to handle this?
I want to prevent that two people edit the same data record simultaneously.
One idea is to make a mysql table 'locks' with fields
tablename, entryid, userid, timestamp
and to add an entry whenever a user starts editing a record (and remove it when the edited form is submitted, also each time remove all entries that are older than one hour).
But I'm uncertain what's precisely the best method do do this:
I could make 'tablename,entryid' the primary key and try to add an entry.
If this fails then this entry is already locked (or some other error happened).
Or I could append another entry and then count if the number of entries with
this combination of tablename,entryid is 1.
Is there a reason to prefer one method over the other? Race conditions possible?
Or is there a completely different, better method to handle this?
ASKER
Is it ok to use the locking of the engine for locks that could last for an hour?
Also I read that these locks are automatically released when the connection is closed. But I do this through an apache web server with php, so I fear that apache might close the connection when it's idle for a while.
Also I read that these locks are automatically released when the connection is closed. But I do this through an apache web server with php, so I fear that apache might close the connection when it's idle for a while.
Hi!
Transaction locks are automatically released when the transaction is finished and/or commit is issued. So if an transaction lasts for an hour then I would see if you can do things a little more quicker. :)
>Is it ok to use the locking of the engine for locks that could last for an hour?
That depends on your application requirements and what is acceptable lock-wait time for your application.
As I said before use appropriate ENGINE that meets your application requirements.
Regards,
Tomas Helgi
Transaction locks are automatically released when the transaction is finished and/or commit is issued. So if an transaction lasts for an hour then I would see if you can do things a little more quicker. :)
>Is it ok to use the locking of the engine for locks that could last for an hour?
That depends on your application requirements and what is acceptable lock-wait time for your application.
As I said before use appropriate ENGINE that meets your application requirements.
Regards,
Tomas Helgi
ASKER
I'm not trying to do transactions.
I want to prevent two people from editing the same record at the same time.
When one person opens it, and then the phone rings, it can take a while until the person clicks on the save-button. Meanwhile somebody else could edit the same record, applying another change, that is later overwritten when the first person clicks save.
I want to prevent two people from editing the same record at the same time.
When one person opens it, and then the phone rings, it can take a while until the person clicks on the save-button. Meanwhile somebody else could edit the same record, applying another change, that is later overwritten when the first person clicks save.
And that is what I call a transactional behaviour.
Each user has his/hers own web session which can include one or more database transactions depending on what the user is doing.
If one user is updating a record and has write lock on it other users should not be able
to update it as well only read the current uncommited values.
If a person requires a lock and then gets on the phone the lock and the transaction should be rolled back after a certain time (timeout) to prevent deadlock.
You could also have an history-table that keeps track of the changes made to a table using triggers between the table and the history table.
Regards,
Tomas Helgi
Each user has his/hers own web session which can include one or more database transactions depending on what the user is doing.
If one user is updating a record and has write lock on it other users should not be able
to update it as well only read the current uncommited values.
If a person requires a lock and then gets on the phone the lock and the transaction should be rolled back after a certain time (timeout) to prevent deadlock.
You could also have an history-table that keeps track of the changes made to a table using triggers between the table and the history table.
Regards,
Tomas Helgi
ASKER
Ok, I will use InnoDB, because I will certainly need some row locking and transactions for other parts of the task.
But I have a very bad feeling with using mysql locks, or even transactions, for this specific purpose, for such long time, and through apache and php.
What if apache closes the connection to mysql when it got no new connections from its clients for a while?
What if apache never closes the connection to mysql, and a user closes the browser window while the lock is set?
Can mysql even know which request comes from which user, when they all go through the same apache?
I expect problems when a lock or a transaction is active over several http request.
With a locking table, I would know exactly what's going on, and wouldn't be affected if future versions of apache talk differently with mysql.
But I have a very bad feeling with using mysql locks, or even transactions, for this specific purpose, for such long time, and through apache and php.
What if apache closes the connection to mysql when it got no new connections from its clients for a while?
What if apache never closes the connection to mysql, and a user closes the browser window while the lock is set?
Can mysql even know which request comes from which user, when they all go through the same apache?
I expect problems when a lock or a transaction is active over several http request.
With a locking table, I would know exactly what's going on, and wouldn't be affected if future versions of apache talk differently with mysql.
Hi!
This is where the web sessions and their session-ids can help you distinguish each user as well as user-ids (if your user are logged in to your website) and even IP-addresses can help you. Look at the HTTP Request and Response methods and the HTTP Session in the PHP manual for further info.
And also you will need to have some session timeout set to appropriate value that gives each users session of the web application ability to stay alive even though the user stays for an hour on the phone. :)
But as I said before. If one of the users locks the table or rows for to long you can end up with a deadlock on your table and that is something you will not want to happen.
Regards,
Tomas Helgi
This is where the web sessions and their session-ids can help you distinguish each user as well as user-ids (if your user are logged in to your website) and even IP-addresses can help you. Look at the HTTP Request and Response methods and the HTTP Session in the PHP manual for further info.
And also you will need to have some session timeout set to appropriate value that gives each users session of the web application ability to stay alive even though the user stays for an hour on the phone. :)
But as I said before. If one of the users locks the table or rows for to long you can end up with a deadlock on your table and that is something you will not want to happen.
Regards,
Tomas Helgi
ASKER
Using table locks for this purpose doesn't work.
As soon as the php script ends, the locks go away.
As soon as the php script ends, the locks go away.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I first tried with semaphore locks, because this seemed to be the case with highest procability of success. These locks could have been depend only on the name of the lock. Later I tried also with table locks. In both cases the locks go away when the php script ends. In hindsight this is not too surprising, because mysql has no way to detect when a user closes the browser window, these lock would last forever.
With record locks I expect it to be worse, not better, because this additionally would require a transaction to remain intact over several http requests. For this to work apache would have to always give the same user the same mysql connection. But apache has only one mysql connection per apache thread, and the same thread can receive http requests from several users. Thus I cannot see how this could possibly work. Apache would have to tell mysql which user has triggered the current mysql command, but this doesn't happen, all connections run with the same mysql-userid, mysql doesn't know about different people logging in on the web page.
With record locks I expect it to be worse, not better, because this additionally would require a transaction to remain intact over several http requests. For this to work apache would have to always give the same user the same mysql connection. But apache has only one mysql connection per apache thread, and the same thread can receive http requests from several users. Thus I cannot see how this could possibly work. Apache would have to tell mysql which user has triggered the current mysql command, but this doesn't happen, all connections run with the same mysql-userid, mysql doesn't know about different people logging in on the web page.
ASKER
I've requested that this question be deleted for the following reason:
I didn't get an anwer to my question, and the suggested alternative doesn't work.
I didn't get an anwer to my question, and the suggested alternative doesn't work.
Hi!
I believe that my comments gives you the answers you need to solve this.
Particularly this one ID: 39329466
Regards,
Tomas Helgi
I believe that my comments gives you the answers you need to solve this.
Particularly this one ID: 39329466
Regards,
Tomas Helgi
It depends on what ENGINE you are using.
MyISAM, MERGE and MEMORY uses table level locking and InnoDB uses row level locking.
http://dev.mysql.com/doc/refman/5.6/en/internal-locking.html
http://dev.mysql.com/doc/refman/5.6/en/locking-issues.html
So if you want row level locking (traditional transactional behaviour ) you will need to use InnoDB otherwise if you use MyISAM you will need to lock the entire table which is not a good idea on a high traffic tables.
For a record to be as unique as possible and eliminating the possibility of users updating, deleting or insert the same record simultaneously you should use timestamp and some other columns that make a unique primary key and use InnoDB engine.
Note that you can't achieve that 100% so you will have to have some method of transactional rollback in your app.
Regards,
Tomas Helgi