I'm just starting to look into SQL transactions and would appreciate some advice on my approach.
My transaction will start like this:
begin;
SELECT `posts`.* FROM `posts` WHERE (thread_id = '1004983') FOR UPDATE;
<-- the rest of my transaction goes here -->
commit;
From what I undestand the transaction above will lock all the rows with a tread_id of 1004983. I should then be able to update values within these rows without having to worry about other connections modifying the values before I have a chance to amend them.
Can I run additional SELECT statements within this transaction which query the locked rows and if I can do I have to use FOR UPDATE again?
My transaction will need to be able to insert a new record and then amend any records with the same tread_id to rebuild a tree structure.
The tree structure is using the Modified Preorder Tree Traversal technique descibed in the following article with tree_left and tree_right values.
http://www.sitepoint.com/article/hierarchical-data-database/
I have a function that uses a post_id and parent_post_id column to rebuild the tree_left and tree_right columns when a new post is added.