Mysql：在更新值之前读取锁定表

Question

In my database (MySQL) I have a table (MyISAM) containing a field called number. Each value of this field is either 0 or a positive number. The non zero values must be unique. And the last thing is that the value of the field is being generated in my php code according to value of another field (called isNew) in this table. The code folows.

$maxNumber = $db->selectField('select max(number)+1 m from confirmed where isNew = ?', array($isNew), 'm');
$db->query('update confirmed set number = ? where dataid = ?', array($maxNumber, $id));

The first line of code select the maximum value of the number field and increments it. The second line updates the record by setting it freshly generated number.

This code is being used concurrently by hundreds of clients so I noticed that sometimes duplicates of the number field occur. As I understand this is happening when two clients read value of the number field almost simultaneously and this fact leads to the duplicate.

I have read about the SELECT ... FOR UPDATE statement but I'm not quite sure it is applicable in my case.

So the question is should I just append FOR UPDATE to my SELECT statement? Or create a stored procedure to do the job? Or maybe completely change the way the numbers are being generated?

Answer 1

This is definitely possible to do. MyISAM doesn't offer transaction locking so forget about stuff like FOR UPDATE. There's definitely room for a race condition between the two statements in your example code. The way you've implemented it, this one is like the talking burro. It's amazing it works at all, not that it works badly! :-)

I don't understand what you're doing with this SQL:

 select max(number)+1 m from confirmed where isNew = ?

Are the values of number unique throughout the table, or only within sets where isNew has a certain value? Would it work if the values of number were unique throughout the table? That would be easier to create, debug, and maintain.

You need a multi-connection-safe way of getting a number.

You could try this SQL. It will do the setting of the max number in one statement.

 UPDATE confirmed 
    SET number = (SELECT 1+ MAX(number) FROM confirmed WHERE isNew = ?)
  WHERE dataid = ?

This will perform badly. Without a compound index on (isNew, number), and without both those columns declared NOT NULL it will perform very very badly.

If you can use numbers that are unique throughout the table I suggest you create for yourself a sequence setup, which will return a unique number each time you use it. You need to use a series of consecutive SQL statements to do that. Here's how it goes.

First, when you create your tables create yourself a table to use called sequence (or whatever name you like). This is a one-column table.

CREATE TABLE sequence (
     sequence_id INT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (`sequence_id`)
) AUTO_INCREMENT = 990000

This will make the sequence table start issuing numbers at 990,000.

Second, when you need a unique number in your application, do the following things.

INSERT INTO sequence () VALUES ();
DELETE FROM sequence WHERE sequence_id < LAST_INSERT_ID();
UPDATE confirmed 
    SET number = LAST_INSERT_ID()
  WHERE dataid = ?

What's going on here? The MySQL function LAST_INSERT_ID() returns the value of the most recent autoincrement-generated ID number. Because you inserted a row into that sequence table, it gives you back that generated ID number. The DELETE FROM command keeps that table from snarfing up disk space; we don't care about old ID numbers.

LAST_INSERT_ID() is connection-safe. If software on different connections to your database uses it, they all get their own values.

If you need to know the last inserted ID number, you can issue this SQL:

SELECT LAST_INSERT_ID() AS sequence_id

and you'll get it returned.

If you were using Oracle or PostgreSQL, instead of MySQL, you'd find they provide SEQUENCE objects that basically do this.

Here's the answer to another similar question.

Fastest way to generate 11,000,000 unique ids