Search code examples
phpmysqlmysqlimyisam

MyISAM racing conditions / LOCK TABLES

My 'invoices' table:

CREATE TABLE `invoices` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `invoice_id` int(11) NOT NULL,
 PRIMARY KEY (`id`,`invoice_id`),
 UNIQUE KEY `invoice_id` (`invoice_id`),
 KEY `order_id` (`order_id`)
) ENGINE=MyISAM AUTO_INCREMENT=115 DEFAULT CHARSET=utf8

When I try the query :

mysqli_query($conn, "LOCK TABLES 'invoices' WRITE");

in a php script, it doesn't work as I can insert a new row in the "locked" table using phpMyAdmin's SQL console during lock time.

May I be totally confident that a query like this

INSERT INTO `invoices` (`invoice_id`) SELECT MAX(`invoice_id`)+100 FROM `invoices`

can successfully prevents race conditions so to use this instead of a LOCK TABLES query;

NOTES:

  • I did not create this table.
  • I may not alter the table.
Solution

  • When you write an sql query you should be wrap table|column names with back ticks but not single quotes.

    In your case 

     mysqli_query($conn, "LOCK TABLES `invoices` WRITE");

    Note But I would recommend you to stop trying to "resolve" racing condition. Why did you decide that it is a problem in your case?

    Racing condition could be a big problem for some projects. But I doubt that it is your case. I would support @Dave comment, you already have auto incremented index. That is more than enough in many cases.

    Imho you don't need this "locks".

    INSERT INTO `invoices` (`invoice_id`) SELECT MAX(`invoice_id`)+100 FROM `invoices`

    This query has almost no sense. Could you explain why are you trying to do this weird insert?