Hi Friends,
I am having the table called user and In that table I am having the field called lastusedecnumber.I need to access that lastusedecnumber and I will add one to that.During that accessing time I need to lock that particular user row.Not the entire table.How can I achive this?Table Type is MyISAM.
views:
49answers:
3
+2
A:
MySQL uses only table-level locking from MyISAM tables. If you can, switch to InnoDB for row-level locking.
Here's a link to the MySQL site describing a Locks set by SQL Statements for InnoDB tables. http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html
belwood
2010-10-07 04:39:47
A:
As a workaround you could add a column to your table, like locked TINYINT(1) - whenever you want the row to be locked you set it to 1. When you now try to access this row, the first thing you do is check if the locked fields is set.
To unlock the row, simply set it to 0 again. Not nice but a really simple workaround.
dhh
2010-10-07 04:58:13
If the connection is closed like close the Browser or etc.If it automatically unlock for others?I think it wont do.Am I correct?
vinothkumar
2010-10-07 05:35:06
No, of course it won't. You could provide a kind of job which periodically checks if there's an open session for the user who triggered the row lock. Of course you must then add some user-information to the table.
dhh
2010-10-07 13:35:25
A:
A better workaround is to create a column containting a timestamp. Whenever you want to lock the row you update it to the current time. To unlock update to a time at least x minutes in the past. Then to check if its locked check that the time stamp is at least x minutes old.
This way if the process crashes (or the user never completes their operation) the lock effectively expires after x minutes.
Barnaby Dawson
2010-10-13 15:29:47