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?
2 Answers 2
OMG I spent hours thinking through this issue.
I answered three very tough questions addressing this exact issue.
SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.
Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.
Jun 14, 2011: Reasons for occasionally slow queries?Jun 08, 2011: Will these two queries result in a deadlock if executed in sequence?Jun 06, 2011: Trouble deciphering a deadlock in an innodb status log
Please read them carefully so you can relive my episode of philosophical InnoDB, right or wrong.
I think this gives a good explaination of lock in share mode:
http://www.mysqlperformanceblog.com/2006/08/06/select-lock-in-share-mode-and-for-update/