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 ?

score 6 · Accepted Answer · answered Aug 15 '12 at 14:34

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 08: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

score 3 · Answer 2 · edited Jun 15 '20 at 09:05

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

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?

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

mysql transaction vs lock

2 Answers2

MYISAM

InnoDB