CODEWITHSUNDEEP
mysqlsqldatabaselockinginnodb
Matej Ukmar
Matej Ukmar

Reputation: 2245

How to lock MySQL table(s) from update until two separate select statements execute

I need to lock and perform two select statements on two MySQL/InnoDB tables. Both tables have related row name updateId. 

SELECT ..., updateId FROM Table1 WHERE ...
SELECT ..., updateId FROM Table2 WHERE ...

I need to prevent updates, inserts or deletions (any modifications on the tables) until both SELECT statements execute. Basically I want to prevent any changes to updateId row between the two statements.

I was looking at SELECT ... FOR SHARE and SELECT ... FOR UPDATE but I'm just a bit unclear how it works.

Upvotes: 1

Views: 142

Answers (2)

Rick James
Rick James

Reputation: 142528

START TRANSACTION;
SELECT ... FOR UPDATE;
SELECT ... FOR UPDATE;
blah, blah, blah
UPDATE/INSERT/etc (if desired)
COMMIT;

FOR UPDATE means "I might change the rows in this SELECT, so keep your hands off!"

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

The other process can!! write, but the first process works wit the data at the moment from the transaction.

here is a sample with transaction

MariaDB [trans]> select * from table1;
+----+-------------+
| id | field1      |
+----+-------------+
|  1 | table 1 -1  |
|  2 | table 1 - 2 |
+----+-------------+
2 rows in set (0.001 sec)

MariaDB [trans]> start transaction;
Query OK, 0 rows affected (0.000 sec)

MariaDB [trans]> select * from table1;
+----+-------------+
| id | field1      |
+----+-------------+
|  1 | table 1 -1  |
|  2 | table 1 - 2 |
+----+-------------+
2 rows in set (0.000 sec)


                    CLIENT 2:
                    MariaDB [trans]> update table1 set field1 = 'new value' where id = 1;
                    Query OK, 1 row affected (0.003 sec)
                    Rows matched: 1  Changed: 1  Warnings: 0

                    MariaDB [trans]> 






MariaDB [trans]> select * from table1;
+----+-------------+
| id | field1      |
+----+-------------+
|  1 | table 1 -1  |
|  2 | table 1 - 2 |
+----+-------------+
2 rows in set (0.001 sec)

MariaDB [trans]> commit;
Query OK, 0 rows affected (0.001 sec)

MariaDB [trans]> select * from table1;
+----+-------------+
| id | field1      |
+----+-------------+
|  1 | new value   |
|  2 | table 1 - 2 |
+----+-------------+
2 rows in set (0.001 sec)

MariaDB [trans]>

Upvotes: 2

Related Questions