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 ?
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
DELETEon a row will place a lock on said row. But this can work even while the same row is being
- 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
- Some concurrent
INSERTs may block one another because locking is done over the clustered index.
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.
Each INSERT, UPDATE, and DELETE performs a full table lock before perform in the SQL command.
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?