[Solved] Block Mysql query until is updated [closed]

by


There are two cases I can see causing you to raise this question:

  1. Large batch INSERT statements, ie:

    INSERT INTO mytable (id, name, date) VALUES
  (1, 'Tom', '2013-01-31'),
  (2, 'Dick', '2013-02-28'),
  (3, 'Harry', '2013-03-31'),
  ...

    In this case MySQL does the locking internally, so you do not have to do anything. Any query that requires the use of ‘mytable’ will be deferred until the insert is completed.

  2. Repetitive single INSERT statements, ie:

    INSERT INTO mytable (id, name, date) VALUES (1, 'Tom', '2013-01-31');
INSERT INTO mytable (id, name, date) VALUES (2, 'Dick', '2013-02-28');
INSERT INTO mytable (id, name, date) VALUES (3, 'Harry', '2013-03-31');
...

    In this case the table unlocks between statements and the only way to properly “lock” the table is to use a transaction. [Note: myISAM does not support transactions, you must be using InnoDB or BDB tables. ie:

    START TRANSACTION;
INSERT INTO mytable (id, name, date) VALUES (1, 'Tom', '2013-01-31');
INSERT INTO mytable (id, name, date) VALUES (2, 'Dick', '2013-02-28');
INSERT INTO mytable (id, name, date) VALUES (3, 'Harry', '2013-03-31');
...
COMMIT;

solved Block Mysql query until is updated [closed]