{"id":310,"date":"2017-01-10T07:05:21","date_gmt":"2017-01-09T23:05:21","guid":{"rendered":"http:\/\/vinta.ws\/code\/?p=310"},"modified":"2026-02-18T01:20:36","modified_gmt":"2026-02-17T17:20:36","slug":"locking-and-mvcc-in-mysql-innodb","status":"publish","type":"post","link":"https:\/\/vinta.ws\/code\/locking-and-mvcc-in-mysql-innodb.html","title":{"rendered":"Locking and MVCC in MySQL InnoDB \u9396\u6a5f\u5236\u8207\u591a\u7248\u672c\u4e26\u767c\u63a7\u5236"},"content":{"rendered":"<p>\u6700\u8fd1\u5728\u7814\u7a76 MySQL 5.6 \/ 5.7 InnoDB storage engine \u7684 Transaction\u3001Locking \u548c Multi-Version Concurrency Control (MVCC) \u6a5f\u5236\uff0c\u57fa\u672c\u4e0a\u53ea\u8a0e\u8ad6 Isolation level \u662f <code>REPEATABLE READ<\/code> \u6216 <code>READ COMMITTED<\/code> \u7684\u60c5\u6cc1\uff0c\u9019\u4e00\u7bc7\u662f\u904e\u7a0b\u4e2d\u8a18\u9304\u4e0b\u4f86\u7684\u7b46\u8a18\u3002<\/p>\n<p>ref:<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-storage-engine.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-storage-engine.html<\/a><\/p>\n<h2>Transaction<\/h2>\n<p>Transaction Isolation Levels<br \/>\n<a href=\"https:\/\/vinta.ws\/code\/transaction-isolation-levels-in-databases.html\">https:\/\/vinta.ws\/code\/transaction-isolation-levels-in-databases.html<\/a><\/p>\n<p>\u5728 <code>autocommit<\/code> \u555f\u7528\u7684\u60c5\u6cc1\u4e0b\uff08\u9ed8\u8a8d\uff09\uff0c\u6bcf\u4e00\u500b SQL \u8a9e\u53e5\u5c31\u662f\u4e00\u500b transaction\uff0c\u4f46\u662f\u4f60\u53ef\u4ee5\u7528 <code>START TRANSACTION<\/code> \u6216 <code>BEGIN<\/code> \u4f86\u624b\u52d5\u958b\u555f\u4e00\u500b transaction\uff0c\u7136\u5f8c\u7528 <code>COMMIT<\/code> \u6216 <code>ROLLBACK<\/code> \u4f86\u7d50\u675f\u3002<\/p>\n<p>ref:<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-autocommit-commit-rollback.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-autocommit-commit-rollback.html<\/a><\/p>\n<h2>Pessimistic Locking \u60b2\u89c0\u9396 \/ Optimistic Locking \u6a02\u89c0\u9396<\/h2>\n<p>\u66f4\u6e96\u78ba\u4e00\u9ede\u7684\u8aaa\u6cd5\u662f pessimistic concurrency control \u548c optimistic concurrency control\u3002<\/p>\n<p>\u57fa\u672c\u4e0a\u60b2\u89c0\u9396\u3001\u6a02\u89c0\u9396\u662f\u4e00\u7a2e\u65b9\u6cd5\u8ad6\uff08\u6216\u8005\u8aaa\u662f\u7b56\u7565\uff09\uff0c\u5728\u4e0d\u540c\u7684\u7cfb\u7d71\u53ef\u80fd\u6709\u4e0d\u540c\u7684\u4f5c\u6cd5\uff0c\u4e0d\u904e\u9019\u88e1\u5c31\u53ea\u8a0e\u8ad6 MySQL\u3002<\/p>\n<p>\u60b2\u89c0\u9396\u5047\u8a2d\u4f60\u5728\u5b58\u53d6\u67d0\u500b\u8cc7\u6599\u6642\uff0c\u5176\u4ed6\u4eba\u540c\u6642\u4fee\u6539\u540c\u4e00\u4efd\u8cc7\u6599\u7684\u6a5f\u7387\u5f88\u9ad8\uff0c\u4e5f\u5c31\u662f\u5c0d\u300c\u8cc7\u6599\u6703\u88ab\u4fee\u6539\u300d\u9019\u4ef6\u4e8b\u6301\u60b2\u89c0\u7684\u614b\u5ea6\uff0c\u6240\u4ee5\u8981\u5728\u5b58\u53d6\uff08\u7121\u8ad6\u8b80\u5beb\uff09\u6642\u5c31\u628a\u8a72\u8cc7\u6599\u9396\u4f4f\uff0c\u4e0d\u8b93\u5176\u4ed6\u4eba\u6709\u6a5f\u6703\u4fee\u6539\u3002\u60b2\u89c0\u9396\u901a\u5e38\u6307\u7684\u662f\u8cc7\u6599\u5eab\u672c\u8eab\u63d0\u4f9b\u7684 locking \u6a5f\u5236\uff08\u5728 application \u5c64\u7684\u9396\u6c92\u8fa6\u6cd5\u4fdd\u8b49\u4e0d\u6703\u88ab\u4fee\u6539\uff09\u3002<code>SELECT ... FOR UPDATE;<\/code> \u5c31\u662f\u4e00\u7a2e\u60b2\u89c0\u9396\u3002<\/p>\n<p>\u6a02\u89c0\u9396\u5247\u662f\u5c0d\u300c\u8cc7\u6599\u6703\u88ab\u4fee\u6539\u300d\u9019\u4ef6\u4e8b\u6301\u6a02\u89c0\u7684\u614b\u5ea6\uff0c\u901a\u5e38\u6703\u4f7f\u7528 version \u7248\u672c\u865f\u7684\u65b9\u5f0f\u4f86\u5be6\u4f5c\uff0c\u5728 InnoDB \u88e1\u5c31\u662f\u6307 Multi-Version Concurrency Control (MVCC)\u3002<\/p>\n<p>You can remember which is which by picturing it this way:<\/p>\n<ul>\n<li>The pessimistic approach is a user thinking &quot;I\u2019m sure someone will try to update this row at the same time as I will, so I better ask the database server to not let anyone touch it until I\u2019m done with it.&quot;<\/li>\n<li>The optimistic approach is a user thinking &quot;I\u2019m sure it\u2019s all good and there will be no conflicts, so I\u2019m just going to remember this row to double-check later, but I\u2019ll let the database server do whatever it wants with the row.&quot;<\/li>\n<\/ul>\n<p>ref:<br \/>\n<a href=\"http:\/\/stackoverflow.com\/questions\/129329\/optimistic-vs-pessimistic-locking\">http:\/\/stackoverflow.com\/questions\/129329\/optimistic-vs-pessimistic-locking<\/a><br \/>\n<a href=\"https:\/\/blogs.msdn.microsoft.com\/marcelolr\/2010\/07\/16\/optimistic-and-pessimistic-concurrency-a-simple-explanation\/\">https:\/\/blogs.msdn.microsoft.com\/marcelolr\/2010\/07\/16\/optimistic-and-pessimistic-concurrency-a-simple-explanation\/<\/a><\/p>\n<h2>Multi-Version Concurrency Control (MVCC)<\/h2>\n<p>\u5728 InnoDB \u88e1\u662f\u57fa\u672c\u4e0a\u662f\u8b80\u4e0d\u52a0\u9396\uff0c\u5beb\u624d\u52a0\u9396\uff0c\u800c\u4e14\u8b80\u5beb\u4e0d\u4e92\u65a5\u3002<\/p>\n<p>InnoDB \u6703\u628a\u6bcf\u500b row \u7684\u6b77\u53f2\u7248\u672c\u5132\u5b58\u5728 rollback segment \u88e1\uff0crollback segment \u7684\u8cc7\u6599\u9664\u4e86\u7528\u5728 transaction rollback \u7684 undo \u64cd\u4f5c\u5916\uff0c\u6703\u7528\u88ab\u7528\u5728 consistent read\uff0c\u4e5f\u5c31\u662f snapshot data\u3002<\/p>\n<p>ref:<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-multi-versioning.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-multi-versioning.html<\/a><\/p>\n<p>\u5728\u6240\u6709 InnoDB \u7684 tables \u88e1\uff0c\u6bcf\u500b row \u90fd\u6703\u6709\u4e09\u500b\u96b1\u85cf\u7684\u6b04\u4f4d <code>DB_ROW_ID<\/code>\u3001<code>DB_TRX_ID<\/code> \u548c <code>DB_ROLL_PTR<\/code>\uff1a<code>DB_TRX_ID<\/code> \u662f\u5efa\u7acb\u9019\u500b\u7248\u672c\u7684\u6578\u64da\u7684\u90a3\u500b transaction id\u3001<code>DB_ROLL_PTR<\/code> \u5c31\u662f rollback data pointer \u6307\u5411\u5728 undo logs \u88e1\u7684\u524d\u4e00\u500b\u7248\u672c\u7684\u6578\u64da\u3002<\/p>\n<p>TODO:<br \/>\n\u591a\u8aaa\u4e00\u9ede\u7d30\u7bc0<br \/>\n\u4f8b\u5982 SELECT\u3001INSERT\u3001UPDATE \u548c DELETE \u6642\u7684\u884c\u70ba<\/p>\n<h2>Consistent (Nonlocking) Reads<\/h2>\n<p>\u6240\u8b02\u7684 consistent read \u662f\u6307 read \u64cd\u4f5c\u5be6\u969b\u4e0a\u662f\u53bb\u8b80 snapshot data\uff08\u8a72 row \u5728\u4e0d\u540c\u6642\u9593\u9ede\u7684\u6578\u64da\u3001\u7248\u672c\uff09\uff0c\u6240\u4ee5 read \u64cd\u4f5c\u4e0d\u6703\u56e0\u70ba\u8a72 row \u6b63\u5728\u88ab\u5176\u4ed6 transaction \u4fee\u6539\u800c\u88ab block \u4f4f\uff0csnapshot data \u5c31\u662f\u7576\u524d\u6578\u64da\u7684\u6b77\u53f2\u7248\u672c\u3002<\/p>\n<p>\u5982\u679c isolation level \u662f <code>REPEATABLE READ<\/code> \u6216 <code>READ COMMITTED<\/code> \u7684\u8a71\uff0c<code>SELECT<\/code> \u64cd\u4f5c\u9ed8\u8a8d\u5c31\u662f consistent read\uff0c\u6240\u4ee5\u4e0d\u6703\u52a0\u4e0a\u4efb\u4f55 lock\u3002\u9664\u975e\u4f60\u986f\u5f0f\u5730\u52a0\u4e0a <code>SELECT ... LOCK IN SHARE MODE<\/code> \u6216 <code>SELECT ... FOR UPDATE<\/code>\uff08\u7a31\u70ba locking reads\uff09\u3002<\/p>\n<p>ref:<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/glossary.html#glos_consistent_read\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/glossary.html#glos_consistent_read<\/a><br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/glossary.html#glos_snapshot\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/glossary.html#glos_snapshot<\/a><br \/>\n<a href=\"https:\/\/blogs.oracle.com\/mysqlinnodb\/entry\/repeatable_read_isolation_level_in\">https:\/\/blogs.oracle.com\/mysqlinnodb\/entry\/repeatable_read_isolation_level_in<\/a><\/p>\n<p>\u81f3\u65bc <code>INSERT<\/code>\u3001<code>UPDATE<\/code> \u548c <code>DELETE<\/code> \u64cd\u4f5c\uff0cMySQL \u5247\u6703\u81ea\u52d5\u5c0d\u727d\u6d89\u5230\u7684\u6578\u64da\u52a0\u4e0a lock\u3002<\/p>\n<p>ref:<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-locks-set.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-locks-set.html<\/a><\/p>\n<p>\u4e0d\u540c\u7684 isolation level \u5728\u8b80 snapshot data \u7684\u884c\u70ba\u4e0a\u662f\u4e0d\u540c\u7684\u3002<\/p>\n<p><code>REPEATABLE READ<\/code> \u5728\u540c\u4e00\u500b transaction \u5167\uff0c\u5c0d\u540c\u4e00\u500b row \u7684\u6240\u6709 consistent read \u8b80\u5230\u7684\u5167\u5bb9\u90fd\u6703\u662f\u7b2c\u4e00\u500b consistent read \u6642\u5efa\u7acb\u7684\u90a3\u500b snapshot data\uff0c\u5373\u4f7f\u5176\u4ed6 transaction \u5df2\u7d93 commit \u4e86\u65b0\u7684\u8cc7\u6599\uff0c\u6240\u4ee5\u624d\u53eb repeatable read\u3002\u4e0d\u904e\u5982\u679c\u662f\u5728\u540c\u4e00\u500b transaction \u5167\u6240\u505a\u7684\u4fee\u6539\u5247\u9084\u662f\u6703\u53bb\u8b80\u90a3\u500b\u4fee\u6539\u904e\u7684\u5167\u5bb9\uff0c\u56e0\u70ba\u662f\u53bb\u8b80 snapshot\u3002\u800c\u4e14 MySQL \u7684 REPEATABLE READ \u9084\u984d\u5916\u5730\u907f\u514d\u4e86 phantom read\u3002<\/p>\n<p><code>READ COMMITTED<\/code> \u5728\u540c\u4e00\u500b transaction \u5167\uff0c\u6bcf\u6b21\u53bb\u8b80\u540c\u4e00\u500b row \u90fd\u6703\u5efa\u7acb\u4e26\u4e14\u8b80\u6700\u65b0\u7684\u4e00\u4efd snapshot data\uff08\u7576\u7136\u53ea\u6703\u8b80\u5df2\u7d93 commit \u7684\u8cc7\u6599\uff09\uff0c\u5148\u5f8c\u7684 read \u53ef\u80fd\u6703\u8b80\u5230\u4e0d\u540c\u7684\u5167\u5bb9\uff0c\u6240\u4ee5\u624d\u6703\u6709 non-repeatable read \u7684\u554f\u984c\u3002<\/p>\n<p><code>SERIALIZABLE<\/code> \u662f\u8b80\u52a0\u8b80\u9396\uff0c\u5beb\u52a0\u5beb\u9396\uff0c\u800c\u4e14\u8b80\u5beb\u4e92\u65a5\u3002<\/p>\n<p>ref:<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-consistent-read.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-consistent-read.html<\/a><br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-transaction-isolation-levels.html#isolevel_repeatable-read\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-transaction-isolation-levels.html#isolevel_repeatable-read<\/a><br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-transaction-isolation-levels.html#isolevel_read-committed\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-transaction-isolation-levels.html#isolevel_read-committed<\/a><br \/>\n<a href=\"https:\/\/blog.gslin.org\/archives\/2015\/09\/27\/6007\/%E7%B9%BC%E7%BA%8C%E5%AD%B8-isolation\/\">https:\/\/blog.gslin.org\/archives\/2015\/09\/27\/6007\/%E7%B9%BC%E7%BA%8C%E5%AD%B8-isolation\/<\/a><\/p>\n<p>\u96d6\u7136 MySQL \u9ed8\u8a8d\u7684 isolation level \u662f <code>REPEATABLE READ<\/code>\uff0c\u4f46\u662f\u5982\u679c\u4f60\u7684\u5c08\u6848\u5176\u5be6\u4e0d\u592a\u9700\u8981 repeatable reads \u7684\u8a71\uff0c\u7528 <code>READ COMMITTED<\/code> \u53ef\u80fd\u66f4\u5408\u9069\uff0c\u81f3\u5c11 lock \u6703\u6bd4\u8f03\u5c11\u3002\u4e0d\u904e <code>REPEATABLE READ<\/code> \u548c <code>READ COMMITTED<\/code> \u5728\u6548\u80fd\u4e0a\u7684\u5dee\u7570\u4f3c\u4e4e\u4e5f\u6c92\u6709\u60f3\u50cf\u4e2d\u90a3\u9ebc\u5927\u5c31\u662f\u4e86\u3002<\/p>\n<p>ref:<br \/>\n<a href=\"https:\/\/www.percona.com\/blog\/2015\/01\/14\/mysql-performance-implications-of-innodb-isolation-modes\/\">https:\/\/www.percona.com\/blog\/2015\/01\/14\/mysql-performance-implications-of-innodb-isolation-modes\/<\/a><br \/>\n<a href=\"https:\/\/smalldatum.blogspot.tw\/2016\/11\/sysbench-innodb-transaction-isolation.html\">https:\/\/smalldatum.blogspot.tw\/2016\/11\/sysbench-innodb-transaction-isolation.html<\/a><\/p>\n<h3>Snapshot Read \/ Current Read<\/h3>\n<p>\u4e5f\u6709\u4e00\u7a2e\u8aaa\u6cd5\u662f\u628a\u300c\u8b80\u300d\u5206\u6210 snapshot read \u548c current read\uff0c\u4e0d\u904e\u9019\u7a2e\u8aaa\u6cd5\u4f3c\u4e4e\u53ea\u6709\u5728\u4e2d\u570b\u7a0b\u5e8f\u54e1\u5beb\u7684\u6587\u7ae0\u88e1\u6d41\u50b3\uff0c\u81f3\u5c11\u6211\u6c92\u6709\u5728\u5b98\u65b9\u7684\u6587\u4ef6\u88e1\u627e\u5230\u9019\u5169\u500b\u540d\u8a5e\u3002<\/p>\n<p>snapshot read\uff0c\u5c31\u662f\u53bb\u8b80 snapshot data\uff1a<\/p>\n<ul>\n<li><code>SELECT<\/code><\/li>\n<\/ul>\n<p>current read\uff0c\u5c31\u662f\u53bb\u8b80\u6700\u65b0\u7248\u672c\u7684\u6578\u64da\uff1a<\/p>\n<ul>\n<li><code>SELECT ... LOCK IN SHARE MODE<\/code><\/li>\n<li><code>SELECT ... FOR UPDATE<\/code><\/li>\n<li><code>INSERT<\/code><\/li>\n<li><code>UPDATE<\/code><\/li>\n<li><code>DELETE<\/code><\/li>\n<\/ul>\n<p>ref:<br \/>\n<a href=\"http:\/\/hedengcheng.com\/?p=771\">http:\/\/hedengcheng.com\/?p=771<\/a><br \/>\n<a href=\"http:\/\/tech.meituan.com\/innodb-lock.html\">http:\/\/tech.meituan.com\/innodb-lock.html<\/a><\/p>\n<h2>Lock Modes<\/h2>\n<h3>Shared Locks \u5171\u4eab\u9396 \/ Exclusive Locks \u6392\u4ed6\u9396<\/h3>\n<p>shared lock (S lock)\uff0c\u4e5f\u7a31\u70ba\u300c\u5171\u4eab\u9396\u300d\u3001\u300c\u8b80\u9396\u300d\u3002\u88ab\u52a0\u4e0a\u8b80\u9396\u7684\u6578\u64da\uff0c\u5176\u4ed6 transaction \u53ef\u4ee5\u8b80\uff0c\u4e5f\u53ef\u4ee5\u518d\u52a0\u4e0a\u8b80\u9396\uff0c\u4f46\u662f\u4e0d\u80fd\u5beb\uff08\u56e0\u70ba\u4e0d\u80fd\u52a0\u4e0a\u5beb\u9396\uff09\u3002<\/p>\n<p>\u53ef\u4ee5\u7528 <code>SELECT ... LOCK IN SHARE MODE;<\/code> \u986f\u5f0f\u5730\u52a0\u4e0a shared lock\u3002MySQL \u6703\u5c0d\u88ab SELECT \u5230\u7684\u6bcf\u500b row \u52a0\u4e0a shared lock\uff0c\u7576\u7136\u524d\u63d0\u662f\u9019\u4e9b row \u6c92\u6709\u88ab\u52a0\u4e0a exclusive lock\u3002<\/p>\n<p>ref:<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/glossary.html#glos_shared_lock\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/glossary.html#glos_shared_lock<\/a><\/p>\n<p>exclusive lock (X lock)\uff0c\u4e5f\u7a31\u70ba\u300c\u6392\u4ed6\u9396\u300d\u3001\u300c\u5beb\u9396\u300d\u3002\u88ab\u52a0\u4e0a\u5beb\u9396\u7684\u6578\u64da\uff0c\u5176\u4ed6 transaction \u53ef\u4ee5\u8b80\uff08\u56e0\u70ba\u8b80\u662f\u8b80 snapshot data\uff09\uff0c\u4f46\u662f\u4e0d\u80fd\u518d\u52a0\u4e0a\u4efb\u4f55\u9396\uff0c\u6240\u4ee5\u4e0d\u80fd\u5beb\uff08\u56e0\u70ba\u4e0d\u80fd\u52a0\u4e0a\u5beb\u9396\uff09\uff0c\u53ea\u6709\u52a0\u4e0a\u5beb\u9396\u7684\u90a3\u500b transaction \u624d\u80fd\u5beb\u3002<\/p>\n<p>\u53ef\u4ee5\u7528 <code>SELECT ... FOR UPDATE;<\/code> \u986f\u5f0f\u5730\u52a0\u4e0a exclusive lock\uff0cMySQL \u6703\u5c0d\u88ab SELECT \u5230\u7684\u6bcf\u500b row \u52a0\u4e0a exclusive lock\u3002<\/p>\n<p>ref:<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/glossary.html#glos_exclusive_lock\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/glossary.html#glos_exclusive_lock<\/a><\/p>\n<h3>Intention Locks \u610f\u5411\u9396<\/h3>\n<p>\u5206\u6210 intention shared locks (IS) \u548c intention exclusive locks (IX)\uff0c\u7528\u4f86\u8868\u793a\u67d0\u500b transaction \u6253\u7b97\u5c0d\u67d0\u500b table \u88e1\u7684\u67d0\u500b row \u52a0\u4e0a shared lock \u6216 exclusive lock\u3002\u66f4\u660e\u78ba\u5730\u8aaa\uff0c\u67d0\u500b transaction \u5fc5\u9808\u5148\u53d6\u5f97 table \u7684 intention lock\uff0c\u5b83\u624d\u80fd\u5c0d\u8a72 table \u88e1\u7684 row \u52a0\u4e0a shared lock \u6216 exclusive lock\u3002<\/p>\n<p>intention locks \u662f MySQL \u81ea\u52d5\u52a0\u7684\uff0c\u4f60\u4e0d\u9700\u8981\u4e5f\u6c92\u8fa6\u6cd5\u81ea\u5df1\u52a0\u4e0a\u9019\u7a2e\u9396\u3002<\/p>\n<p>ref:<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-locking.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-locking.html<\/a><\/p>\n<p>\u53e6\u5916\u9084\u6709\u4e00\u7a2e table-level locks\uff0c\u53ef\u4ee5\u900f\u904e <code>LOCK TABLE<\/code> \u986f\u5f0f\u5730\u52a0\u4e0a\uff1a<\/p>\n<pre class=\"line-numbers\"><code class=\"language-sql\">LOCK TABLES t1 READ;\nLOCK TABLES t1 WRITE;<\/code><\/pre>\n<p>ref:<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/lock-tables.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/lock-tables.html<\/a><\/p>\n<h3>Online DDL (Data Definition Language)<\/h3>\n<p><code>ALTER TABLE<\/code> \u64cd\u4f5c\u53ef\u80fd\u4e5f\u6703\u6709 table-level locks\uff0c\u5728 <code>ALTER TABLE<\/code> \u7684\u904e\u7a0b\u4e2d\u53ea\u80fd\u8b80\uff0c\u5beb\u5247\u6703\u88ab block \u76f4\u5230 <code>ALTER TABLE<\/code> \u5b8c\u6210\u3002\u4e0d\u904e\u9019\u9ede\u5728 MySQL 5.6 \/ 5.7 \u4ee5\u5f8c\u6539\u5584\u4e86\u4e0d\u5c11\uff0c\u4f8b\u5982\u53ef\u4ee5\u7528 <code>ALTER TABLE ... ALGORITHM=INPLACE LOCK=NONE;<\/code> \u4f86\u6307\u5b9a\u4e0d\u540c\u7684\u884c\u70ba\u3002<\/p>\n<p>ref:<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/alter-table.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/alter-table.html<\/a><br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-online-ddl.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-online-ddl.html<\/a><\/p>\n<p>Summary of Online Status for DDL Operations<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-create-index-overview.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-create-index-overview.html<\/a><\/p>\n<h2>Lock Types<\/h2>\n<p>Locks Set by Different SQL Statements in InnoDB<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-locks-set.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-locks-set.html<\/a><\/p>\n<h3>Record Locks<\/h3>\n<p>record lock \u5c31\u662f\u52a0\u5728 index \u4e0a\u7684 lock\uff0cInnoDB \u88e1\u7684 row-level lock \u5be6\u969b\u4e0a\u662f index \u7684 record lock\u3002\u4f7f\u7528 <code>READ COMMITTED<\/code> \u7684\u6642\u5019\uff0clocking read\uff08\u5373 <code>LOCK IN SHARE MODE<\/code> \u548c <code>FOR UPDATE<\/code>\uff09\u3001<code>UPDATE<\/code> \u548c <code>DELETE<\/code> \u7684 lock \u90fd\u662f record lock\u3002<\/p>\n<p>TODO:<br \/>\n\u5982\u679c\u8a72 table \u6c92\u6709\u4efb\u4f55 index<\/p>\n<p><code>READ COMMITTED<\/code> \u53ea\u6709 record lock\uff0c\u6c92\u6709 gap lock \u548c next-key lock\uff0c\u4e0d\u904e foreign-key constraint \u548c duplicate-key \u6aa2\u67e5\u9084\u662f\u6703\u7528\u5230 gap lock\u3002<\/p>\n<p><code>REPEATABLE READ<\/code> \u6709 record lock\u3001gap lock \u548c next-key lock\u3002<\/p>\n<p>ref:<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-locking.html#innodb-record-locks\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-locking.html#innodb-record-locks<\/a><br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-transaction-isolation-levels.html#isolevel_read-committed\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-transaction-isolation-levels.html#isolevel_read-committed<\/a><\/p>\n<h3>Gap Locks<\/h3>\n<p>gap lock \u662f\u52a0\u5728 index records \u4e4b\u9593\u7684 gap \u4e0a\u7684 lock\u3002<\/p>\n<p>ref:<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-locking.html#innodb-gap-locks\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-locking.html#innodb-gap-locks<\/a><\/p>\n<h3>Next-Key Locks<\/h3>\n<p>next-key lock \u662f record lock + gap lock\uff0cnext-key lock \u53ef\u4ee5\u7528\u4f86\u907f\u514d phantom read\u3002<\/p>\n<p>ref:<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-locking.html#innodb-next-key-locks\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-locking.html#innodb-next-key-locks<\/a><br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-next-key-locking.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-next-key-locking.html<\/a><\/p>\n<h3>Insert Intention Locks<\/h3>\n<p>insert intention lock \u662f gap lock \u7684\u4e00\u7a2e\uff0c<code>INSERT<\/code> \u4e4b\u524d\u6703\u5148\u52a0\u4e00\u500b insert intention lock\uff0c\u4f46\u662f\u53ea\u8981\u4e0d\u662f\u8981 <code>INSERT<\/code> \u5230\u540c\u4e00\u500b\u4f4d\u7f6e\u4e0a\u5c31\u4e0d\u6703 block \u5176\u4ed6 transaction\u3002<\/p>\n<p>ref:<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-locking.html#innodb-insert-intention-locks\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-locking.html#innodb-insert-intention-locks<\/a><\/p>\n<h3>AUTO-INC Locks<\/h3>\n<p>auto-inc lock \u662f\u4e00\u7a2e table-level lock\uff0c\u4e0d\u540c\u7a2e\u985e\u7684 insert-like \u8a9e\u53e5\u6703\u6709\u4e0d\u540c\u7684\u884c\u70ba\u3002\u53ef\u4ee5\u900f\u904e <code>innodb_autoinc_lock_mode<\/code> \u8a2d\u5b9a\u4f86\u6539\u8b8a\u9019\u4e9b\u884c\u70ba\u3002<\/p>\n<p>\u5728 <code>innodb_autoinc_lock_mode = 1<\/code>\uff08\u9810\u8a2d\u503c\uff09\u7684\u60c5\u6cc1\u4e0b\uff0csimple inserts\uff08\u5c31\u662f\u90a3\u7a2e\u5728\u57f7\u884c\u4e4b\u524d\u5c31\u77e5\u9053\u6700\u5f8c\u6703\u7522\u751f\u5e7e\u7b46\u65b0\u8cc7\u6599\u7684 inserts\uff09\u4e0d\u6703\u6709 table-level \u7684 AUTO-INC lock\uff0c\u53ea\u6709\u5728\u53d6\u5f97 auto-increment \u503c\u7684\u90a3\u77ac\u9593\u6703\u6709 lock\u3002<\/p>\n<p>ref:<br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-locking.html#innodb-auto-inc-locks\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-locking.html#innodb-auto-inc-locks<\/a><br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-auto-increment-handling.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-auto-increment-handling.html<\/a><br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u6700\u8fd1\u5728\u7814\u7a76 MySQL 5.6 \/ 5.7 InnoDB storage engine \u7684 Transaction\u3001Locking \u548c Multi-Version Concurrency Control (MVCC) \u6a5f\u5236\uff0c\u57fa\u672c\u4e0a\u53ea\u8a0e\u8ad6 Isolation level \u662f REPEATABLE READ \u6216 READ COMMITTED \u7684\u60c5\u6cc1\uff0c\u9019\u4e00\u7bc7\u662f\u904e\u7a0b\u4e2d\u8a18\u9304\u4e0b\u4f86\u7684\u7b46\u8a18\u3002<\/p>\n","protected":false},"author":1,"featured_media":311,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22],"tags":[23,92],"class_list":["post-310","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-about-database","tag-mysql","tag-transaction"],"_links":{"self":[{"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/posts\/310","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/comments?post=310"}],"version-history":[{"count":0,"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/posts\/310\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/media\/311"}],"wp:attachment":[{"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/media?parent=310"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/categories?post=310"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/tags?post=310"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}