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.

I have a question regarding MySQL locking and transaction management. My Question is ..does mysql takes lock on the the tuple/table on which I am doing select/update in a transaction ?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

With MyISAM, you can use the concurrent_insert=2 optimization, where an INSERT will not block existing or new SELECT statements (but will block anything else).

With InnoDB, the rules are:

  • SELECTs never block
  • An UPDATE or DELETE on a row will place a lock on said row. But this can work even while the same row is being SELECTed.
  • A second update (on concurrent transaction) on same row will block, until the first is committed or rolled back.
  • INSERTs are more complicated: if the table has an AUTO_INCREMENT, then that is locked (though other concurrent operations are allowed if not competing for AUTO_INCREMENT)
  • Some concurrent INSERTs may block one another because locking is done over the clustered index.
  • DELETEs, UPDATEs on ranges are also more complex and may involve firther blocks due to locks on clustered index.

This is probably not a thorough list, but should get you the general picture.

share|improve this answer
    
Is it correct to say that single update or delete statement e.g. update table set column1 = x, outside of transaction is/acts like a micro transaction. –  broadband Feb 20 at 8:58
    
With InnoDB - yes; no UPDATE, DELETE, INSERT, SELECT can operate outside a transaction. If not declared otherwise, they operate within their own singular transactions. –  Shlomi Noach Feb 21 at 20:40
add comment

MYISAM

Each INSERT, UPDATE, and DELETE performs a full table lock before perform in the SQL command.

InnoDB

MySQL's ACID-compliant storage engine, InnoDB performs row-level (tuple-level) locking. InnoDB performs MVCC to support transaction isolation. The level for Transaction Isolation in InnoDB are

There are other posts I have for to look over to see how InnoDB and MyISAM differ

share|improve this answer
    
Hi rolando..i have read your the wiki link in your post.. –  matang Aug 14 '12 at 12:13
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.