28

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.

Matt Fenwick
  • 48,199
  • 22
  • 128
  • 192
svk
  • 4,513
  • 18
  • 63
  • 100
  • 7
    [Only InnoDB has row locking; MyISAM, MEMORY and MERGE are table level](http://dev.mysql.com/doc/refman/5.1/en/internal-locking.html) – OMG Ponies Oct 07 '10 at 04:36

5 Answers5

26

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

belwood
  • 3,320
  • 11
  • 38
  • 45
  • Does that lock work only for that transaction or is it permanent for the whole database? – Juan Carlos Apr 27 '17 at 17:19
  • Sometimes for transaction, sometimes global. You should read and understand locking carefully before using it. For example, there are different types of locks, like "global read lock". Sometimes locks are released automatically on commit, others might be released at the start of a transaction. Your requirements should dictate what type of locking you should use and how to use it. – belwood May 01 '17 at 13:11
5

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

DontVoteMeDown
  • 21,122
  • 10
  • 69
  • 105
Aleksandr Guidrevitch
  • 418
  • 6
  • 7
  • 1
    This is a good suggestion as it allows you to update the row, and read the value (though as currently written it reads the updated value, rather than the old one). What it doesn't mention is that as it's a write to the table, it locks the whole table whilst it updates the row, as that's what MyISAM does - it can't be avoided. – davidsheldon Apr 13 '15 at 09:41
0

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.

oyvey
  • 609
  • 1
  • 9
  • 20
0

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
  • 4,289
  • 8
  • 42
  • 59
  • 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? – svk Oct 07 '10 at 05:35
  • 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 Oct 07 '10 at 13:35
  • 15
    But this is like a bad semaphore that doesn't eliminate race conditions. If 2 concurrent queries think `LOCK=0`, then they both proceed into the critical section, _both_ set `LOCK=1`, and you have a hidden race condition bug. – bobobobo Jun 06 '13 at 16:20
  • 4
    updates and writes lock internally in MySQL. Only one will actually write at a time, so if your UPDATE has a where clause for lock=0, it shouldn't create a race condition. – JRL Jun 09 '14 at 17:28
  • 1
    @JRL that is not true. I've experienced this. – majidarif Jan 17 '17 at 00:46
-2

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
  • 29
  • 1
  • 4
    No good - since checking and updating is not a single operation, this allows two connections to lock the same row without being aware of each other. (There's probably a way to do this right, but it would be complex and easy to mess up.) – Brilliand May 05 '14 at 20:10
  • 2
    Never EVER use timing as a substitution for locking. – y o May 08 '15 at 09:15