Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Thanks,

Billmalarky

share|improve this question
add comment

2 Answers

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.

Please read them carefully so you can relive my episode of philosophical InnoDB, right or wrong.

share|improve this answer
add comment

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/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.