MySQL InnoDB engine lock Mechanism (i)--row and table locks

As we all know, the MyISAM engine uses a table lock, while the InnoDB minimum granularity is a row lock. In practice, however, we sometimes find that even if we are working with data that is not in the same row, a lock table will occur. Let's look at an example first.

session1 Open the transaction and update the id=1 data:

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/7F/39/wKiom1cXFPLQ06bWAAAgwAz71Jw721.png "style=" float: none; "title=" 1.PNG "alt=" Wkiom1cxfplq06bwaaagwaz71jw721.png "/>

Session2 opens the transaction and updates the id=2 data, but the Session2 is blocked:

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/7F/39/wKiom1cXFZHgjW-TAAALsd29rWo848.png "title=" 2.PNG " alt= "Wkiom1cxfzhgjw-taaalsd29rwo848.png"/>

Not to say that InnoDB support row lock, we are clearly updated here is not the same piece of data, why is also blocked. In fact, this is because MySQL InnoDB to lock the data in the same way as Oracle does. Oracle is locking this data row, and InnoDB is implemented by locking the index entries on the index. Simply put: If our statement fails to hit the index, INNODB will lock the table. We index the ID column of the Innodb_lock table:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/7F/39/wKiom1cXGNXDjn6gAAAK2QMVP2U594.png "title=" 3.PNG " alt= "Wkiom1cxgnxdjn6gaaak2qmvp2u594.png"/>

session1 Open the transaction and update the id=1 data:

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/7F/39/wKiom1cXGRTRHLdXAAARm0Aju2Y514.png "title=" 4.PNG " alt= "Wkiom1cxgrtrhldxaaarm0aju2y514.png"/>

session2 Open the transaction and update the id=2 data at this point, the update succeeds:

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/7F/37/wKioL1cXGkWCJt0AAAATUASIqGI518.png "title=" 5.PNG " alt= "Wkiol1cxgkwcjt0aaaatuasiqgi518.png"/>

It says that InnoDB's row locks are for index locking instead of specific records. So if even a different record, as long as the access to the same index will cause blocking. In the example above, we first insert a data with the same ID.

session1 Open the transaction and update the data for id=1 and name=my1:

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/7F/39/wKiom1cXHdXSrbVFAAAwcLnYi-c972.png "title=" 6.PNG " alt= "Wkiom1cxhdxsrbvfaaawclnyi-c972.png"/>

Session2 opens the transaction and updates the data for id=1 and name=my5, but Session2 is blocked:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/7F/37/wKioL1cXHueCTyqBAAANmh1EdQo713.png "title=" 7.PNG " alt= "Wkiol1cxhuectyqbaaanmh1edqo713.png"/>

We try to update the NAME=MY5 data again, but without the ID to match, thesession2 is still blocked. This blocking is because the name column is not indexed, so the update tries to lock the table, but because a row lock already exists, the lock table is blocked:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/7F/38/wKioL1cXLiuTKf_GAAAHmQgYi_8712.png "title=" 1.PNG " alt= "Wkiol1cxliutkf_gaaahmqgyi_8712.png"/>

As seen from the above, the InnoDB lock is the index, not the specific data in the table. Because Session1 locks the index of the id=1, and because the name is not indexed, they are not the same record, but their ID is the same and will cause blocking.

It's all about a single-column index, what if there are multiple columns on the table that have indexes. When a table has multiple indexes, you can use different indexes to lock different rows. We modify the following table structure, add a column of addr, and then index both the ID and name.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/7F/3A/wKioL1cXQb_BQV3jAAAU5IWIuKQ227.png "title=" 2.PNG " alt= "Wkiol1cxqb_bqv3jaaau5iwiukq227.png"/>

Let's take a look at the use of a column index. session1 update id=1 and ADDR=ADDR1 rows:

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/7F/3C/wKiom1cXQZbwLWzsAAAT7sN8kJI521.png "title=" 3.PNG " alt= "Wkiom1cxqzbwlwzsaaat7sn8kji521.png"/>

session2 to update the NAME=MY4 line and be blocked. Because although MySQL tries to lock the line, the index of the row id=1 is locked:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/7F/3C/wKiom1cXQo3BK64iAAAGByJTHg4852.png "title=" 4.PNG " alt= "Wkiom1cxqo3bk64iaaagbyjthg4852.png"/>

Session2 again with ADDR=ADDR4 to update, is blocked. The block here is the statement that attempts to lock the table because there is no index on the addr column:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/7F/3D/wKiom1cXQ2aCpMoXAAAFcHBcYXk792.png "title=" 5.PNG " alt= "Wkiom1cxq2acpmoxaaafchbcyxk792.png"/>

Let's take a look at the use of multi-column indexes. Session1 update id=1 and name=my1 rows:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/7F/3B/wKioL1cXRYnCRFxUAAATuDyg-Lk576.png "title=" 6.PNG " alt= "Wkiol1cxryncrfxuaaatudyg-lk576.png"/>

session2 to update the NAME=MY4 line, success. Because both the ID and the name are indexed, when we conditionally use them as the equivalent of a federated index, only the union index of Id=1 and NAME=MY1 is locked exactly.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/7F/3B/wKioL1cXRlqCfUAvAAAfuHe3hpg377.png "title=" 7.PNG " alt= "Wkiol1cxrlqcfuavaaafuhe3hpg377.png"/>

Session2 again with ADDR=ADDR4 to update, or is blocked. The block here is the statement that attempts to lock the table because there is no index on the addr column:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/7F/3D/wKiom1cXRgejVuCSAAAIc5_DrmI969.png "title=" 8.PNG " alt= "Wkiom1cxrgejvucsaaaic5_drmi969.png"/>

Summary: MySQL InnoDB to lock data in the same way as Oracle does. Oracle is locking this data row, and InnoDB is implemented by locking the index entries on the index. Simply put: If our statement fails to hit the index, INNODB will lock the table. There are a few simple steps we can take to determine which tables InnoDB will lock: 1. Remove columns that do not have an index in the Where Condition 2. The records that are queried with the remaining conditions are the rows that the InnoDB locks.

This article is from the "Bronze Gong" blog, please be sure to keep this source http://jaeger.blog.51cto.com/11064196/1765906

MySQL InnoDB engine lock Mechanism (i)--row and table locks