CODEWITHSUNDEEP
mysql
Werner
Werner

Reputation: 1847

MySql: Lock with no effect?

I have a table "offers" which contains "orders" as well. An offer can be changed into a "order" by adding a unique incremental order-number.

I do:

$DB->Sql("SELECT GET_LOCK('ORDERNO')");
$n = $DB->resultSql("SELECT max(orderno) FROM orders");
$n += 1;
$DB->Sql("UPDATE orders SET orderno=".$n." WHERE id=".$actualId);
$DB->Sql("SELECT RELEASE_LOCK('ORDERNO')");

But resently, I got two identical order-numbers.

I want to avoid using 

SELECT max(orderno) FOR UPDATE

because this blocks the whole table, and the orderno is ONLY set in this part of the code so my idea was to use LOCKS to make everything faster.

Any idea why it was possible to get the same number twice?

Upvotes: 0

Views: 40

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31832

You can use a single update statement:

update orders t
cross join (select max(orderno) as maxno from orders) x
set t.orderno = x.maxno + 1
where id = ?;

Demo: http://rextester.com/GEJHX43916

This way you don't need to lock anything manually.

Upvotes: 2

Related Questions