innodb-lock-modes

This page has moved or been replaced. The new page is located here:

http://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html

Please update any bookmarks that point to the old page.


User Comments
  Posted by Dave Neary on July 19, 2005

Major gotcha: Rows are locked during updates using indexes. For example,

update tab set col1=3 where col2=17;

will lock the entire table unless col2 is indexed (in which case, only rows where col2=17 will be locked).
  Posted by Greg Kemnitz on July 24, 2009
To expand on the above comment, any operation that does a table scan for update/delete will lock all the rows in the table.
  Posted by David Tonhofer on July 18, 2010
...and use

SHOW ENGINE INNODB STATUS

to inspect any deadlock reasons!

  Posted by diao diao on March 10, 2015
mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.10 sec)

------
In this part, you'd better create table t with a index,otherwise lock share mode will lock full table
Sign Up Login You must be logged in to post a comment.