How to lock a single row

Question

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.

Answer 1

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

Answer 2

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().