MySQL Lock and isolation mechanism

One: MySQL Lock

Two: MySQL Transactions

Three: MySQL Isolation Level

1.1 Why do you have a lock?

Because the database wants to solve concurrency control problems. At the same time, there may be multiple clients manipulating a table's data or some data, such as reading that row's data, and other attempts to remove it. In order to ensure the consistency of data, the database is to control this concurrency, so there is the concept of lock.

1.2 Classification of Locks

1.2.1 by type of operation on the data

LOCK TABLES tb_namelock_type{read| WRITE};

Unlocktables;

read lock (Shared lock): for the same piece of data, multiple read operations can be performed simultaneously without affecting each other.

Write lock (Exclusive lock): before the current write operation is completed, it blocks other write and read locks.

Example 1 : Read lock

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/44/wKiom1VC9CbSzxVtAAA6ZGUdQtI504.jpg "title=" 1.png " Style= "Float:none;" alt= "wkiom1vc9cbszxvtaaa6zgudqti504.jpg"/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/40/wKioL1VC9ZDQKiT7AADDPCge_zM471.jpg "style=" float: none; "title=" 2.png "alt=" Wkiol1vc9zdqkit7aaddpcge_zm471.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/44/wKiom1VC9Cejjw61AAA4KgRzG_s633.jpg "style=" float: none; "title=" 3.png "alt=" Wkiom1vc9cejjw61aaa4kgrzg_s633.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/40/wKioL1VC9ZGjtIayAAA6iT3Ep0I751.jpg "style=" float: none; "title=" 4.png "alt=" Wkiol1vc9zgjtiayaaa6it3ep0i751.jpg "/>

Example 2 : Write Lock

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/3F/wKioL1VC7qeCaRh_AAA6KslPDO8516.jpg "style=" float: none; "title=" 5.png "alt=" Wkiol1vc7qecarh_aaa6kslpdo8516.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/43/wKiom1VC7T7zcO48AABS8KsLa8U833.jpg "style=" float: none; "title=" 6.png "alt=" Wkiom1vc7t7zco48aabs8ksla8u833.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/3F/wKioL1VC7qjQQTZhAABBqh-35tg792.jpg "style=" float: none; "title=" 7.png "alt=" Wkiol1vc7qjqqtzhaabbqh-35tg792.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/43/wKiom1VC7T_AGMk4AACPWT0b1II094.jpg "style=" float: none; "title=" 8.png "alt=" Wkiom1vc7t_agmk4aacpwt0b1ii094.jpg "/>

1.2.2 size of Lock:MySQL The server only supports table-level, rows require the storage engine to complete

table Lock: The cost of managing locks is minimal, while the allowable concurrency is also the minimum lock mechanism. MyIsam The lock mechanism used by the storage engine. When the data is to be written, the entire table is locked, while the other read and write actions are all waiting. In MySQL , in addition to the MyIsam storage engine's use of this locking strategy,MySQL itself uses table locks to perform certain actions, such as ALTER TABLE.

row lock: A lock policy that can support maximum concurrency. This strategy is used by the InnoDB storage engine

2.1 ACID

atomicity ( Autmic): The database operations that are caused by the Office are either completed or not executed

Consistency ( Consistency): before and after completion is the same (two account money, the sum before and after the transfer is the same)

Isolation ( Isolation): Execution of one transaction cannot affect execution of another transaction

Persistence ( Durability): Once a transaction is successfully completed, the system must ensure that any failures do not cause inconsistencies in the transaction.

2.2 Status of the transaction

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6C/3F/wKioL1VC73HwsrA7AABnbwOC0Nk655.jpg "title=" 9.png "alt=" wkiol1vc73hwsra7aabnbwoc0nk655.jpg "/>

2.3 Save Point (savepoint )

a very large transaction, this transaction has a operation, the implementation of the first time to find the first one wrong, how to do?

withdrawn?? ---->80---> bad .

This leads to the save point, every ten to do a save point

Rollback Save point: ROLLBACK to Sid

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/43/wKiom1VC7mnAA8ulAAC7hysEWlQ662.jpg "title=" 10.png "alt=" Wkiom1vc7mnaa8ulaac7hysewlq662.jpg "/>

Example:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/3F/wKioL1VC7_Lj5ZSXAAGBV-cqaOk745.jpg "title=" 11.png "Style=" Float:none; "alt=" wkiol1vc7_lj5zsxaagbv-cqaok745.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/43/wKiom1VC7onwZ5aTAAEdlwyOyB8628.jpg "title=" 12.png "Style=" Float:none; "alt=" wkiom1vc7onwz5ataaedlwyoyb8628.jpg "/>

3.1 Isolation level of a transaction

read-uncommitted :  Read not submitted when someone else does, you can see it immediately. ( lowest Isolation level )

read-committed :  Read Submit If someone submits it, you can see it.

Repeatable-read : can be reread

(regardless of whether other transactions are committed, I see the same in my affairs, for example A transaction performed an UPDATE operation

Done, The B transaction performs a SELECT operation, and B executes the same result before and after the A executes.

SERIALIZABLE : Serializable

To view the isolation level of the current database:

SHOW GLOBAL VARIABLES like '%iso% ';

SELECT @ @TX_ISOLATION;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/43/wKiom1VC71iyUJXCAADDSWomHqI791.jpg "title=" 1.png " Style= "Float:none;" alt= "wkiom1vc71iyujxcaaddswomhqi791.jpg"/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/3F/wKioL1VC8MOj8aOLAACxQZSftrQ310.jpg "style=" float: none; "title=" 2.png "alt=" Wkiol1vc8moj8aolaacxqzsftrq310.jpg "/>

3.2 The impact of transaction isolation levels on transactions

3.2.1read-uncommitted Example

the client 1 "

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/3F/wKioL1VC8TWiPvY0AAD0sUUuyJs807.jpg "style=" float: none; "title=" 3.png "alt=" Wkiol1vc8twipvy0aad0suuuyjs807.jpg "/>

the client 2 "

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/3F/wKioL1VC8TbQSlh1AAD0pZ1I9Pg747.jpg "style=" float: none; "title=" 4.png "alt=" Wkiol1vc8tbqslh1aad0pz1i9pg747.jpg "/>

the client 1 "

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/43/wKiom1VC782j1ilpAABbaZUxh-4108.jpg "style=" float: none; "title=" 5.png "alt=" Wkiom1vc782j1ilpaabbazuxh-4108.jpg "/>

the client 2 "

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/3F/wKioL1VC8TeAks-kAAC_aeRDYWk151.jpg "style=" float: none; "title=" 6.png "alt=" Wkiol1vc8teaks-kaac_aerdywk151.jpg "/>

Summary: Client 2 within a transaction, the data read two times is different, resulting in a phantom read

3.2.2read-committed

the client 1 "

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/43/wKiom1VC8FGAX6NQAAEHDELqyaI109.jpg "style=" float: none; "title=" 7.png "alt=" Wkiom1vc8fgax6nqaaehdelqyai109.jpg "/>

the client 2 "

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/3F/wKioL1VC8byCR9_CAADpXbcpHcQ182.jpg "style=" float: none; "title=" 8.png "alt=" Wkiol1vc8bycr9_caadpxbcphcq182.jpg "/>

the client 1 "

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/43/wKiom1VC8FLQiiVzAABjI-fqTEU899.jpg "style=" float: none; "title=" 9.png "alt=" Wkiom1vc8flqiivzaabji-fqteu899.jpg "/>

the client 2 "

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/3F/wKioL1VC8byyc3tFAAC5K_R6pps625.jpg "style=" float: none; "title=" 10.png "alt=" Wkiol1vc8byyc3tfaac5k_r6pps625.jpg "/>

Summary: Client 1 does not perform a commit until client 2 does not see the updated data after client 1 commits the commit, client 2 See the updated data, client 2 within a transaction, the execution of two queries still see different results, there is still a phantom reading problem

3.2.3repeatable-read

the client 1 "

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/3F/wKioL1VC8jrAwMHIAADztu4tHZo856.jpg "style=" float: none; "title=" 11.png "alt=" Wkiol1vc8jrawmhiaadztu4thzo856.jpg "/>

the client 2 "

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/43/wKiom1VC8NHDz1hbAAFVJz4nLCY905.jpg "style=" float: none; "title=" 12.png "alt=" Wkiom1vc8nhdz1hbaafvjz4nlcy905.jpg "/>

the client 1 "

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/3F/wKioL1VC8jvxWBNHAABgf3i-FHM378.jpg "style=" float: none; "title=" 13.png "alt=" Wkiol1vc8jvxwbnhaabgf3i-fhm378.jpg "/>

the client 2 "

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/43/wKiom1VC8NKyFguaAADpcFW-jdk190.jpg "style=" float: none; "title=" 14.png "alt=" Wkiom1vc8nkyfguaaadpcfw-jdk190.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/3F/wKioL1VC8jyQhLR2AAC8JOvIWKk550.jpg "style=" float: none; "title=" 15.png "alt=" Wkiol1vc8jyqhlr2aac8joviwkk550.jpg "/>

Summary: Client 2 The data you see before and after submission is still different, creating a phantom read

3.2.4SERIALIZABLE

the client 1 "

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/43/wKiom1VC8VbRARFdAADyOKN1dpc115.jpg "style=" float: none; "title=" 16.png "alt=" Wkiom1vc8vbrarfdaadyokn1dpc115.jpg "/>

the client 2 "

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/3F/wKioL1VC8sDDlkdFAABbgzzpGs8240.jpg "style=" float: none; "title=" 17.png "alt=" Wkiol1vc8sddlkdfaabbgzzpgs8240.jpg "/>

the client 1 "

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/43/wKiom1VC8VbwpyY0AABBT2POcqw740.jpg "style=" float: none; "title=" 18.png "alt=" Wkiom1vc8vbwpyy0aabbt2pocqw740.jpg "/>

the client 2 "

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/43/wKiom1VC8VeSf3bQAACX2dqcDd0170.jpg "style=" float: none; "title=" 19.png "alt=" Wkiom1vc8vesf3bqaacx2dqcdd0170.jpg "/>

Summary: Although there is no problem of data phantom reading, but the execution efficiency is very low

This article is from the "Everyman" blog, please make sure to keep this source http://caoyt.blog.51cto.com/9978141/1641163

MySQL Lock and isolation mechanism