Table locking is required to ensure data integrity in the following situation

Question

In MySQL, I have a tree represented by a nested set. Manipulating a nested set requires changing multiple rows in a table. For example, adding a node to the tree will require reordering the right and left values ​​...

SELECT @myLeft := lft FROM folders WHERE ID = ?; UPDATE folders SET rgt = rgt + 2 WHERE rgt > @myLeft; UPDATE folders SET lft = lft + 2 WHERE lft > @myLeft; 

I am currently wrapping this in a transaction. However, I am wondering ... Is the transaction sufficient to ensure data integrity?

My concern is that since there are two separate UPDATE statements in a high-volume environment, this can cause problems ... Should I also lock the table to ensure that nothing changes between the two UPDATE statements?

Thank you in advance for your help.

Answer 1

You can add FOR UPDATE to the select statement to block all rows it encounters.

But this is not enough - you basically need to lock the entire table (because you are going to update the whole table), and transactions will not do this for you. InnoDB has row-level locking, and only locked blocks of the desired rows, which will not work here, because you select only one row, but update everything.

Selecting the entire FOR UPDATE table to lock all rows may block everything, but it will work best with the first SELECT. Otherwise, you read one line and freeze your opinion (read continuously), and then another transaction does the same. Now both of you have the same look - but this second transaction really needs to read data that is about to change! (The second transaction will not be blocked - you read and block different rows. The system does not know that you plan to update the entire table.) Therefore, if you lock in this way (FOR UPDATE), this statement should be one in order to “freeze” your view to the table.

After some research, I decided that the only possible solution is advisory locking.

(The normal LOCK command in MySQL does not work with transactions.)

Instead, use the GET_LOCK function - and run it before opening a transaction, not after.

i.e.

 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT GET_LOCK('folders_nested_set', <timeout>); /* remember to check to make sure the lock worked */ START TRANSACTION WITH CONSISTENT SNAPSHOT; do work on folders table here COMMIT; DO RELEASE_LOCK('folders_nested_set'); 

Make sure that all functions that work with the nested set are wrapped in a transaction. Not for writing, but to ensure consistent reading in SQL queries.

i.e.

 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION WITH CONSISTENT SNAPSHOT; do reads here COMMIT; 

If you do not know that you are reading all the data that you need in only one SQL statement, you do not need it.

(You can run SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ immediately after you connect to db, not each transaction. Be sure to include the SESSION keyword in this case.)

If I am mistaken or missed something here - or even if there is a better way, I would really like to know about it, because I am dealing with exactly the same situation.