MySQL Bugs: #6211: Problem with InnoDB and Locking on Update

Bug #6211	Problem with InnoDB and Locking on Update
Submitted:	22 Oct 2004 11:04	Modified:	1 Nov 2004 14:33
Reporter:	Ingo Fischer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.18	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
Hi !

On your mysql-documentation page about locking issues http://dev.mysql.com/doc/mysql/en/InnoDB_locking_reads.html we found the following "trick" to update a value of a table and getting the field:

> In MySQL, the specific task of generating a unique identifier actually can be accomplished using only a single access to the table:
>
> UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
> SELECT LAST_INSERT_ID();
>
> The SELECT statement merely retrieves the identifier information (specific to the current connection). It does not access any table. 

We adapted this for our use to:

UPDATE terminal SET stan = LAST_INSERT_ID(stan + 1) WHERE   terminal_id=3125;
SELECT LAST_INSERT_ID()

Now we had one mytserious effect: it happends that the Update-Query came twice at the same time. The second Update was started as the first one still was not finished.
Now the effect was that the value was incremented twice in sum (this is completely correct), BUT the value which was returned by the both "SELECT LAST_INSERT_ID()" queries was the same - this stan+2 value.
Eachof our scripts use it's own connection to the database - we have no connection-pooling or so.

I thought that this Update-command is an atomar command. Do you have any idea what happend there ?
Would it be an idea to change the query to

UPDATE terminal SET LAST_INSERT_ID(stan) = stan + 1 WHERE   terminal_id=3125;

We use Mysql 4.0.18

Ingo Fischer

How to repeat:
Create a table with a stan-field from type "int" or so.

Now use two session who uses 
UPDATE terminal SET stan = LAST_INSERT_ID(stan + 1) 
SELECT LAST_INSERT_ID()

... the problem is that the session#2 needs to send in the update at the same time as the first session ...

Suggested fix:
We expect to get the value stan+1 at the first session and stan+2 at the second

This is expected behaviour if updated column is not of the auto_increment type.

If it is of the auto_increment type, then this would be a different story.

hm ... what would be the "story" if it were an "autoincrement" field?

How we can assure that we can securly increase this field's value.
At the moment we have an "is_locked" field in this table and so the entry is locked with an update, then the counter is increased and then is is unlocked, but the performance of thsi is really bad. Any idea ?

Ingo Fischer

Hi!

Please consult the MySQL manual about AUTO_INCREMENT.

Regards,

Heikki

I don't understand your comment about auto_increment ... I have ONE existing row and I need to increase the value of one column in that existing row. I don't want to insert a new row in that table.

So how I can increase the value of an column in one row in an atomar and fast command. I thought it can be done like my first idea ...

Please give me a clue ... 

PS: why you have reopened the bug?

Ingo F

Ingo,

BEGIN;
UPDATE t SET counter = counter + 1;
SELECT counter FROM t; # retrieve the new value
COMMIT;

Regards,

Heikki