Thesql transaction vs lock

MySQLtransaction

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 ?

Best Answer

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.

Related Question