Shulou(Shulou.com)06/01 Report--

Basic concept

Current read and snapshot read

In MVCC, read operations can be divided into two categories: snapshot read and current read. Snapshot reading, read the visible version of the record (it may be the historical version), without locking. The current read is the latest version of the record, and the returned record will be locked to ensure that the data is up-to-date before the end of the transaction.

Snapshot read: a simple select operation that belongs to snapshot read and is unlocked (except Serializable).

Select * from table where?

Current read: special read operation, insert / update / delete operation, belongs to the current read and needs to be locked.

Select * from table where? Lock in share mode;select * from table where? For update;insert into table values (); update table set? Where; delete from table where?

Isolation level and locking mechanism

Dirty reading will occur in Read Uncommitted, regardless of the consideration. Read Committed (RC) for the current read, the RC isolation level ensures that the read record is locked (Gap Locking), resulting in the phenomenon of phantom reading. Repeatable Read (RR) for the current read, the RR isolation level ensures that the read records are locked (Record Locking), while the read range is locked, new records that meet the query conditions can not be inserted (Gap Locking), and there is no phantom reading. All the read operations of Serializable degenerate to the current read, read-write conflict, so the degree of concurrency decreases sharply, regardless of.

Test script

-basic operation-query transaction isolation level. Default is RRshow variables like'% isolation%';--. Set transaction isolation level to RCset session transaction isolation level read committed;-- data initialization-- begin;drop table if exists user CREATE TABLE `user` (`id` bigint (20) unsigned NOT NULL AUTO_INCREMENT, `email` varchar (64) NOT NULL, `age` int (11) NOT NULL, `address` varchar (64) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq_ email` (`email`), KEY `idx_ age` (`age`); insert into user (email, age, address) values ("test1@elsef.com", 18, "address1"); insert into user (email, age, address) values ("test2@elsef.com", 20, "address2") Insert into user (email, age, address) values ("test3@elsef.com", 20, "address3"); commit;select * from user;---1, trx_id example begin;SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID (); select * from user;SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID (); SHOW ENGINE INNODB STATUS;update user set age = 22 where id = 3 -- query transaction idSELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID ();-- INNODB engine status SHOW ENGINE INNODB STATUS;commit;-- II, repeatable and non-repeatable example-- session1set session transaction isolation level read committed;begin;-- session2set session transaction isolation level repeatable read;begin;-- session1select * from user;-- session2select * from user;-- session3begin Insert into user (email, age, address) values ("test4@elsef.com", 30, "address4"); commit;-- session1, because it is RC, you can read the new data submitted by trx3. Here, if it is proved to be unrepeatable, you should use update instead of insertselect * from user;commit;-- session2. Because it is RR, you will not read the new data submitted by trx3 select * from user;commit. -- third, snapshot read magic reading example-- session1set session transaction isolation level repeatable read;begin;-- here use snapshot to read select * from user;-- session2begin;insert into user (email, age, address) values ("test4@elsef.com", 30, "address4"); commit;select * from user;-- session1select * from user -- the data of test4@ cannot be read here, because the phantom reading of insert into user (email, age, address) values ("test4@elsef.com", 30, "address4") occurred here by RR--;-- insertion failed because of email unique index conflict commit;-- 4. Current reading illustration example-RC-- session1set session transaction isolation level read committed;begin -- all age=20 records that meet the conditions are locked. Because it is RC, there is no GAP lock delete from user where age=20; select * from user;-- session2set session transaction isolation level read committed;begin;--, because trx1 does not have a GAP lock, the like can be inserted into age=20 records insert into user (email, age, address) values ("test4@elsef.com", 20, "address4"); select * from user -- 4 pieces of data can be found, and the deleted data of trx1 can be read. Because RC,trx1 is not submitted, it does not affect trx2commit;-- session1select * from user;-- can read the data newly inserted by trx2. Although trx1 is currently read, it does not add a corresponding next-key lock, and does not prevent the new data of trx2 from being inserted into commit;--RR-- session1set session transaction isolation level repeatable read;begin;delete from user where age = 20 security select * from user;-- session2begin. -- this will block because trx1 adds a GAP lock around the age=20-- a non-unique index. First, locate the first record that meets the query criteria through the index, add the X lock on the record, add the GAP lock on the GAP, and then add the record X lock on the primary key cluster index. Then read the next one and repeat it. Until the first record that does not meet the condition is reached, there is no need to add record X lock, but still need to add GAP lock, and finally return to the end. Insert into user (email, age, address) values ("test4@elsef.com", 20, "address4");-- until timeout, ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction-- if the query can see three records, commit;-- session1-- can only see one record at this time, and the other two have been deleted select * from user;commit;-- unique index + RC-- session1set session transaction isolation level read committed;begin Delete from user where email = "test3@elsef.com";-- session2begin;-- can read because trx1 is RCselect * from user where email = "test3@elsef.com" -- the age that attempts to update this record will block until it times out, because email is the only index that has been locked by trx1, and will also be locked on the corresponding primary key index-- Note that the id=3 operated here is the same row record update user set age = 40 where id=3 of the email operated in trx1;-- session1commit;-- session2commit;-- No Index + RC-- session1set session transaction isolation level read committed;begin -- because the address field has no index, Innodb locks all rows, and MySQL server determines and releases the lock delete from user where address = "address3";-- the session2set session transaction isolation level read committed;begin;-- line succeeds because it is not locked (first added and then released) update user set age = 10 where address = "address2" -- this line will also be blocked because it has been locked by trx1 statements, and all those that meet the criteria are locked update user set age = 10 where address = "address3";-- session1commit;-- session2commit;-- non-unique index + RR-- session1set session transaction isolation level repeatable read;begin;delete from user where age = 20 politics-session2set session transaction isolation level repeatable read;begin -- there will be congestion here, because age=20 records are locked and GAP locks are added in trx1, so 18 has fallen into the lock interval insert into user (email, age, address) values ("test4@elsef.com", 18, "address4");-- session1commit;-- session2commit;-- indexed RR-- session1set session transaction isolation level repeatable read;begin -- if there is no index, all records in the table will be locked and all GAP on the primary key index will be locked, eliminating all concurrent update operations delete from user where address = "address3";-- session2set session transaction isolation level repeatable read;begin;-- will block here because the primary key is already locked with GAP, so the new insert cannot execute successful insert into user (email, age, address) values ("test4@elsef.com", 18, "address4"). -- simple example of session1commit;-- session2commit;-- deadlock-- session1begin;delete from user where id = 1 insert-session2begin;delete from user where id = 3 insert-session1delete from user where id = 3 scape-seession2-- where MySQL judges that a deadlock has occurred, interrupting a deadlock 1213 (40001): Deadlock found when trying to get lock; try restarting transactiondelete from user where id = 1 insert-session1rollback;-- session2;rollback;-- V, deadlock insert example Create table T1 (`id` bigint not null auto_increment, primary key (`id`)); insert into T1 values (1); insert into T1 values (5); commit;select * from T1 session1begin;insert into session1begin;insert into T1 values (2);-- sessioin2begin;-- blocks insert into T1 values (2);-- session3begin;-- blocks insert into T1 values (2);-- session1 -- when rollback occurs, trx2 and trx3 are notified that MySQL automatically interrupts a trx because of a deadlock-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionrollback;--session2;rollback;--session3;rollback

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.