5

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 ?

5

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.

  • 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 '14 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 '14 at 20:40
3

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

  • Hi rolando..i have read your the wiki link in your post.. – matang Aug 14 '12 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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