mysql transaction vs lock

Question

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 ?

Answer 1

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.

Answer 2

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

• READ-UNCOMMITTED
• READ-COMMITTED
• REPEATABLE-READ (default)
• SERIALIZABLE

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

• Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
• Sep 14, 2011 : Benefits of table level locking
• Sep 20, 2011 : Best of MyISAM and InnoDB
• Feb 28, 2012 : read before write transaction
• May 02, 2012 : Which mysql storage engine to choose?
• May 03, 2012 : Which is faster, InnoDB or MyISAM?
• May 16, 2012 : Why doesn't InnoDB store the row count?