We have an table called account_user_device_tree that holds a nested set model in mysql. When we try to add a node to the table, we execute the queries listed below. The issue I'm having is that when we have 2 separate processes updating the thread simultaneously we end up with a "corrupted" tree due to the fact that the SELECT query executed right after the SELECT FOR UPDATE seems to be executed in both sessions without being blocked by the SELECT FOR UPDATE.
Does the SELECT FOR UPDATE block other sessions from proceeding until the 1st session does a commit or rollback? If so what else could be causing this tree issue?
Sample set of queries executed are listed below:
START TRANSACTION
SELECT tree_id FROM account_user_device_tree WHERE tree_id = 1 FOR UPDATE;
SELECT `tree_id`,`tree_left`,`tree_right` FROM `account_user_device_tree` WHERE `tree_id` = 9146; (this returns 'tree_id' = '9146', 'tree_left' = '20695', 'tree_right' = '20708')
// Updating the tree and add the new node at the end
UPDATE account_user_device_tree SET tree_right = tree_right + 2 WHERE tree_right >= 20708
UPDATE account_user_device_tree SET tree_left = tree_left + 2 WHERE tree_left > 20708
INSERT INTO account_user_device_tree (tree_left,tree_right) VALUES ('20708','20708' + 1)
COMMIT
The account_user_device_tree table is in InnoDB engine. The transaction isolation level is READ_COMMITTED. AutoCommit is set to ON in the global variables.