If I understand SELECT ... LOCK IN SHARE MODE correctly, you can place it into a mysql transaction to select the rows you will be working with during that transaction.
This is done in order to "lock out" those selected rows from other session's writing/deleting actions (but other sessions can still read the rows) until your transaction completes.
From that point, the rows that were locked with the SELECT LOCK IN SHARE MODE statement are released so other sessions can access them for writing, deleting, etc.
This is exactly what I want for my comments table. Whenever a comment is added to a post on my site, I need to lock all the comment rows tied to that post while I update some meta data on all the locked rows. Additionally, if two comments get submitted at the same time, I don't want them to both have access to the relevant comment rows at the same time because they will basically screw each other (and the meta data) up.
So, I want to incorporate SELECT LOCK IN SHARE MODE into the comment upload script so the first session to run the lock in query gets complete control over the comment rows until it finishes the entire transaction (and the script that was slightly slower has to wait until the entire transaction from the first script executes).
I am a but concerned about creating deadlocks where script A locks data that script B needs, and script B locks data that script A needs.
- How can I get around this in my application?
- I am using only innodb in my website database, so do I need to worry about table locks?
