How to lock a single row

I have a user table with field lastusedecnumber.

I need to access and increment lastusedecnumber.

During that accessing开发者_运维问答 time I need to lock that particular user row (not the entire table).

How do I do this?

The table type is MyISAM.

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 Locks set by SQL Statements for InnoDB tables. http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html

Kind of late, but hope it will help someone:

UPDATE user SET lastusedecnumber = LAST_INSERT_ID(lastusedecnumber + 1);
SELECT LAST_INSERT_ID();

Will give you atomic increment of lastusedecnumber and ability to read new value of lastusedecnumber field (after increment) using SELECT LAST_INSERT_ID().

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.

I didn't feel like converting my whole database from myisam. So I simply try to create a new table named based on the id of the record I want to lock. If create table is successful, do my work and delete the table at the end. If create table not successful, stop.

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.