phpmyadmin Question posted in
The official documentation can be found here.

MyISAM racing conditions / LOCK TABLES – Phpmyadmin

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.
Tags:

2

Answers


  1. 0

    Please note that the lock only lasts for the duration of your database session – in this case the duration of your script call.

    Login or Signup to reply.
  2. 0

    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?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search