MySQL database Advanced (vii)--Transactions and locks

MySQL database Advanced (vii)--Transaction and lock, Transaction introduction 1, Transaction introduction

A transaction (Transaction) is a series of operations performed as a single logical unit of work.

2, the characteristics of the transaction

A, atomicity (atomicity)
Multiple database operations that make up a single transaction are non-delimited atomic units, and only all operations succeed, the entire transaction is committed, and any one of the database operations in the transaction fails, and any operations that have already been performed must be revoked to return the database to its original state.
B, Consistency (consistency)
After the transaction operation succeeds, the database is in a state that is consistent with its business rules, that is, the data is not destroyed.
C, Isolation (isolation)
In concurrent data operations, different transactions have their own data space, and their operations do not interfere with each other. The database specifies a variety of transaction isolation levels, which correspond to different levels of interference, the higher the isolation level, the better the consistency of data, but the weaker the concurrency.
D, Persistence (durabiliy)
Once the transaction commits successfully, all data operations in the transaction must be persisted to the database, even if the database crashes immediately after committing the transaction, and when the database restarts, it must be able to recover the data through some mechanism.

3. Transaction type

A. Auto COMMIT Transaction
system default each Transact-SQL command is a transaction that is automatically started and committed by the system.
B, implicit transactions
You do not need to display the start transaction, you need to display the commit, and the implicit transaction is any separate insert, UPDATE, or DELETE statement. When a large number of DDL and DML commands are executed, they start automatically and persist until the user explicitly commits.
Show VARIABLES view variables.
SET autocommit=0, turn off the auto-commit feature.
You need to show the commit or rollback.

update tablename set sname=‘孙悟空‘ where studentid=‘000000000000003‘;commit;

Or
rollback;
C, display transactions
Shows that the transaction is a user-defined transaction, beginning with start TRANSACTION (transaction start) and ending with a commit (transaction commit) or ROLLBACK (ROLLBACK TRANSACTION) statement.

start transaction update tablename set sname=‘孙悟空‘ where studentid=‘000000000000003‘;commit

Or
rollback
D, distributed transactions
Transactions that span multiple servers are called distributed transactions. Supports distributed transactions starting from MySQL5.03.

4. Transaction control

A. Start A Transaction
Marks the start point of an explicit transaction, which is the start of the transaction. Its syntax is as follows:
START { TRAN | TRANSACTION }
B. Commit a transaction
Marks the end of a successful implicit transaction or an explicit transaction, that is, a transaction commit. Its syntax is as follows:
COMMIT
C. Rolling back a transaction
Rolls back an explicit or implicit transaction to the start of a transaction or to a savepoint within a transaction. Its syntax is as follows:
ROLLBACK
D, transaction settings
SET Autocommit can modify the current connection transaction submission method.
SET autocommit=0, a clear command is required to commit or rollback.

5. Problems caused by transaction concurrency

Dirty Reads (Dirty read) refers to a transaction (a) that reads a different transaction (B) uncommitted change data and operates on the basis of the data being read. If the B transaction happens to be rolled back, then the data read by a transaction is not recognized at all.
Non-repeatable read (unrepeatable read) refers to a transaction that reads the change data that the B transaction has committed.
Phantom Read (Phantom Read)
A transaction reads the new data submitted by the B transaction, at which point a transaction will have phantom read.
First category missing updates
When a transaction is revoked, the updated data of the committed B transaction is overwritten.
Category two missing updates
A transaction overwrites the data already submitted by the B transaction, causing the operation of the B office to be lost.

II. Introduction to Transaction ISOLATION Level 1, transaction ISOLATION LEVEL

The SQL standard defines a Class 4 isolation level, which includes specific rules to define which changes within and outside the transaction are visible and which are not. Low-level isolation levels generally support higher concurrency processing and have lower system overhead.
READ UNCOMMITTED (Read UNCOMMITTED content)
This isolation level, transactions can read the execution results of other uncommitted transactions. Reading uncommitted data is also known as Dirty reading (Dirty read).
Read Committed (read submit content)
The default isolation level for most database systems (but not MySQL default). Transactions can only read execution results that have been committed by other transactions. This isolation level supports so-called non-repeatable reads (nonrepeatable read), because other instances of the same transaction may have new commits during the instance processing, so the same select may return different results.
Repeatable Read (can be reread)
MySQL default transaction isolation level, which will take a snapshot of the query's records until the end of the transaction. Ensure that multiple instances of the same transaction will see the same data row when concurrently reading the data, resulting in a phantom read (Phantom read). Phantom reading refers to when a user reads a range of data rows, another transaction inserts a new row within that range, and when the user reads the data row of that range, a new phantom row is found. The InnoDB and Falcon storage engines solve the Phantom read problem through the multi-version concurrency control (mvcc,multiversion Concurrency control) mechanism.
Serializable (Serializable)
The highest isolation level, multiple transactions that are read and modified on the same record can end only one to begin the next.
Solve the Phantom reading problem by forcing the transaction to sort, making it impossible to conflict with each other. Adding a shared lock on each row of data that is read can result in a large number of timeouts and lock contention.

2. Transaction ISOLATION Level setting

The user can use the SET TRANSACTION statement to change the isolation level of a single session or all new incoming connections. The syntax is as follows:
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
The default behavior (without session and global) is to set the isolation level for the next (not started) transaction. If you use the Global keyword, the statement sets the default transaction level globally for all new connections that are created at the new start, requiring super permissions. Use the session keyword to set the default transaction level for future transactions performed on the current connection. Any client can freely change the session isolation level or set the isolation level for the next transaction.
To query the global and session transaction isolation levels:

SELECT @@global.tx_isolation; SELECT @@session.tx_isolation; SELECT @@tx_isolation;

Modify the global transaction isolation level through the MySQL configuration file and set the global session default transaction isolation level.

[mysqld]xxxxxxxtransaction-isolation=read-committed

Restart the MySQL service to take effect.
Set Current isolation Level

SET  SESSION  TRANSACTION ISOLATION LEVEL  READ UNCOMMITTEDSET  SESSION  TRANSACTION ISOLATION LEVEL  READ COMMITTEDSET  SESSION  TRANSACTION ISOLATION LEVEL  REPEATABLE READSET  SESSION  TRANSACTION ISOLATION LEVEL  SERIALIZABLE

III. Transaction Isolation Level validation 1, isolation level for different sessions

Different transaction isolation levels for different sessions
View the transaction isolation level for the current session in Session 1 terminal
select @@tx_isolation
Query result is: Repeatable read Repeatable-read
Set the current session transaction isolation level to READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Open another SQL Manager terminal as session 2 to view the transaction isolation level for the current session
select @@tx_isolation
Query result is: Repeatable read Repeatable-read
Create a table with ID, name, and age fields to validate different levels of transaction isolation.

CREATE TABLE ta(id INT NOT NULL PRIMARY KEY,name VARCHAR(10),age INT)ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into ta values(1, ‘孙悟空‘, 500);insert into ta values(2, ‘唐僧‘, 30);

Note: Due to the failure of the transaction rollback mechanism of my SQL Manager Lite client, the following experiment uses the NAVICAT for MySQL client.

2. Verify the READ UNCOMMITTED isolation level

Open a session 1, set the transaction isolation level to READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Open Session 2, start a transaction, update the record with ID 1 to age 1000.

start TRANSACTION;update ta set age=1000 where id =1;

In Session 1, the information for the ID 1 in the TA table is viewed, and age is already 1000.
select * from ta;
The transaction isolation level of Session 1 allows you to read uncommitted data.
Rolling back transactions in session 2
ROLLBACK;
Conversation 1 and Session 2 query the TA table for a record with ID 1, age 500

3. Verify the Read Committed isolation level

Open a session 1, set the transaction isolation level to read COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
Open Session 2, start a transaction, update the record with ID 1 to age 5000.

start TRANSACTION;update ta set age=5000 where id =1;

In session 1, look at the information in the TA table with ID 1, age 500.
select * from ta;
The transaction isolation level of Session 1 does not allow the READ uncommitted data.
Commit a transaction in session 2
COMMIT;
Session 1 Query The TA table for a record with ID 1, age 5000

4. Verify REPEATABLE READ isolation LEVEL

Open a session 1, set the transaction isolation level to repeatable READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
In Session 1, start a transaction with an age of 5000 for a record with a query ID of 1.

start TRANSACTION;SELECT * FROM ta where id =1;

In Session 2, update the information in the TA table with ID 1, age 1000.
UPDATE ta SET age=1000 WHERE id=1;
In Session 2, the information for the ID 1 in the TA table is viewed, and age is already 1000.
select * from ta WHERE id=1;
In session 1, look again at the information in the TA table with ID 1, and age is still 5000.
select * from ta WHERE id=1;
Commit a transaction in session 1
COMMIT;
Session 1 queries the TA table for a record with ID 1, and age is already 1000.

5. Verify the Serializable isolation level

Open a session 1, set the transaction isolation level to Serializable
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE
Open Session 2, start a transaction, update the record with ID 1 to age 5000.

start TRANSACTION;update ta set age=5000 where id =1;

Start a transaction in Session 1, view the information for ID 1 in the TA table, and Session 1 is in a wait state.

start TRANSACTION;select * from ta;

After the transaction is committed in Session 2,
COMMIT;
Session 1 Query SQL execution completes with a result of 5000.

Four, lock 1, Lock Introduction

A lock in a database is a software mechanism that controls the means by which a user (a process session) is prevented from taking on a certain data resource, and other users are doing things that affect the operation of the user's data or cause data non-integrity and non-conformance problems to occur.

2, the level of the lock

According to the lock level, the lock can be divided into shared lock, exclusive lock.
A, shared lock (read lock)
For the same piece of data, multiple read operations can be performed simultaneously without affecting each other.
Shared locks are only locked for update, and other transactions are only able to get the latest records but not update operations until the update operation is committed.
B, exclusive Lock (write lock)
Block other write and read locks until the current write operation is complete.

3, the size of the lock

According to the granularity of the lock, the lock can be divided into table level lock, row level lock, page level lock.
A, row-level lock
Overhead, locking slow, there will be deadlock, locking force is minimal, the probability of a lock collision is the lowest, high concurrency.
B, table-level lock
Small overhead, lock fast, no deadlock, locking force, the probability of conflict is high, low concurrency.
C, page lock
Overhead and lock time between table and row locks, deadlock occurs between table and row row level locks, and concurrency is common.

4. mysql storage engine and lock mechanism

MySQL's locking mechanism is relatively simple, the most notable feature is that different storage engines support different locking mechanisms.
The MyISAM and memory storage engines use table-level locks.
InnoDB supports row-level and table-level locks, and row-level locks are used by default.

Five, table level lock 1, table-level lock Introduction

Table-level locks are supported by both the MyISAM storage engine and the InnoDB storage engine.
The MyISAM storage engine supports table-level locks, which allow you to manually add table-level locks in order to ensure data consistency and prevent others from changing data when changing data. You can use a command to unlock a table in a database by using a command for the table lock on the database.
The command to lock the table lock Tables, the command to unlock the table unlock Tables
The MyISAM engine automatically adds a read lock to the user's read data and changes the data to automatically write the lock. Use lock tables and unlock tables to explicitly lock and unlock.

2. Add table-level read lock

Open Session 1, CREATE table

CREATE TABLE tc(id INT,name VARCHAR(10),age INT)ENGINE=MyISAM DEFAULT CHARSET=utf8;

Insert two record:

insert into tc values(1, ‘孙悟空‘, 500);insert into tc values(3, ‘猪八戒‘, 100);

Add read lock to table
lock tables tc read;
Only the locked tables can be queried after locking.
select * from tc;
Querying for tables without locks will fail
select * from ta;
Open Session 2 to query the already locked table for success.
select * from tc;
Update operation on locked table TC will fail
update tc set age=100 where id=1;
Session 1 uses the lock Table command to add a read lock to the table, session 1 can query the records in the locked table, but updates or accesses other tables will prompt an error, session 2 can query the table records, but the update will appear lock wait.
The update operation for session 2 was successful when the table was unlocked in session 1.
unlock tables;
In session 1, lock the table TC again, followed by the local parameter.
lock tables tc read local;
The local parameter allows simultaneous insertion at the end of the table, locking only the current record in the table, and other sessions inserting new records
Insert a record in Session 2
insert into tc values(2, ‘唐僧‘, 20);
View the records of TC tables in session 1 without inserting records
select * from tc;

3. Set the concurrency of table-level lock

The read lock is a shared lock that does not affect the read of other sessions, but does not update data that has been added to the read lock. MyISAM table reads and writes are serial, but in general, under certain conditions, the MyISAM table also supports the concurrency of query and insert operations.
The MyISAM storage engine has a system variable Concurrent_insert that controls the behavior of its concurrent insertions, which can be 0, 1, or 2, respectively.
0: Concurrent operations are not allowed
1: If there is no hole in the MyISAM table (that is, the row in the middle of the table is not deleted), MyISAM allows a process to read the table while another process inserts records from the end of the table, which is the default setting for MySQL.
2: The record is allowed to be inserted concurrently at the end of the table regardless of whether there is an empty hole in the MyISAM table.
In MySQL config file add, concurrent_insert=2, restart the MySQL service settings to take effect.

4. Verifying the concurrency of table-level locks

Set Concurrent_insert to 0
Lock table TC in Session 1
lock tables tc read local;
Insert a record in Session 2, at which point the TC table is locked and waits
insert into tc values(4, ‘沙悟净‘, 30);
In session 1, Unlock table TC, at which session 2 is inserted successfully
unlock tables;

Set Concurrent_insert to 1
Delete a record with ID 3 in session 1
delete from tc where id=3;
Lock table TC in Session 1
lock tables tc read local;
Insert a record in Session 2, at which point the TC table is locked, and the table is empty, into the waiting
insert into tc values(5, ‘白骨精‘, 1000);
In session 1, the table TC is unlocked, and session 2 is inserted successfully, and there are no holes in the table at this time
unlock tables;
Lock table TC in Session 1
lock tables tc read local;
Insert a record in Session 2, insert successfully, support conditional concurrent insertion
insert into tc values(6, ‘白骨精‘, 1000);
Unlock table TC in Session 1
unlock tables;

Set Concurrent_insert to 2
Delete the record with ID 5 in session 1 to create an empty
delete from tc where id=5;
Lock table TC in Session 1
lock tables tc read local;
Insert a record in Session 2, insert successfully, support unconditional concurrent insertion
insert into tc values(7, ‘蜘蛛精‘, 1000);
Unlock table TC in Session 1
unlock tables;

5. Add table-level write lock

The syntax for adding table-level write locks is as follows:
LOCK TABLES tablename WRITE;
No other sessions are allowed to query, modify, or insert records.

Six, row level lock 1, row level lock Introduction

The InnoDB storage Engine implements a multi-version concurrency Control Protocol--MVCC (Multi-version Concurrency control). The advantage of MVCC is to read without locking, read and write do not conflict. In OLTP applications where read and write less, read-write conflicts are very important and greatly increase the concurrency of the system.
In MVCC concurrency control, read operations can be divided into two categories: snapshot read (snapshot read) and current read.
Snapshot reads, read the visible version of the record (possibly a historical version), without locking.
The current read, reads the latest version of the record, and the record returned by the current read is added with a lock to ensure that other transactions are no longer concurrently modified. Transactions are locked for the rows that are being manipulated and are not locked for other rows.
Snapshot read: A simple select operation, which belongs to the snapshot read, without locking.
select * from table where ?;
Current read: Special read operation, Insert/update/delete, belongs to the current read, need to lock.

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 ?;

The above SQL statement belongs to the current read and reads the latest version of the record. Also, after reading, it is necessary to ensure that other concurrent transactions cannot modify the current record and lock the read record. In addition to the first statement, the read record plus S lock (shared lock), the other operation, plus X lock (exclusive lock).

2. Verifying snapshot Read

Open Session 1, create a table with ID, name, age

CREATE TABLE td(id INT ,name VARCHAR(10),age INT)ENGINE=innoDB DEFAULT CHARSET=utf8;

After inserting two records

insert into td values(1, ‘孙悟空‘, 500);insert into td values(2, ‘猪八戒‘, 100);

Start a transaction in session 1
start transaction;
Record information for query ID bit 1 in session 1
select * from td where id =1;
Open Session 2, Update ID 1 for age 1000
update td set age=1000 where id=1;
In Session 2, view the age of ID 1 has been updated to 1000.
select * from td where id =1;
In session 1, look at age with ID 1, which is still 500.
select * from td where id =1;
Commit a transaction in session 1
COMMIT;
In Session 1, view the age of ID 1, which is already 1000.

3. Verify the current read

Start a transaction in session 1
start transaction;
Add a shared lock to the SELECT statement in Session 1.
select * from td where id=1 lock in share mode;
In Session 2, update the value of age with ID 1 to 100 and enter the lock wait
update td set age=100 where id=1;
Commit a transaction in session 1
COMMIT;
The update operation for session 2 succeeded.

4. Verify the transaction to lock the record

Start a transaction in session 1
start transaction;
The value of age in Session 1 Update ID 1 is 500.
update td set age=500 where id=1;
Start a transaction in session 2
start transaction;
In session 2, update ID 2 for age has a value of 1000, at which time the lock waits
update td set age=1000 where id=2;
The TD table does not specify a primary key and the transaction does not support row-level locks. The transaction for session 1 adds a lock to the entire table.
A transaction was committed at session 1 at which time the modification of Session 2 succeeded
COMMIT;
Commit a transaction in Session 2, unlock the table
COMMIT;
In session 1, increase the primary key for the table ID
alter table td add primary key(id);
Start a transaction in session 1
start transaction;
In session 1, update the value of age with ID 1 to 5000
update td set age=5000 where id=1;
Start a transaction on session 2
start transaction;
The value of the Get with ID 2 modified at session 2 is 10000, and the update succeeds, stating that session 1 only locks the row with ID 1.
update td set age=10000 where id=2;
On session 2, the update ID is 1 with an age value of 100, and a wait occurs. Because session 1 adds an exclusive lock to the row with ID 1.
update td set age=5000 where id=1;
Commit a transaction in session 1
COMMIT;
Commit a transaction in session 2
COMMIT;
In session 1 queries, sessions 1 and Session 2 modify the age column to take effect
select * from td;

5, the production of deadlocks

After a transaction adds a shared lock, a B transaction can also add a shared lock. A transaction update locks the record, waits, and at the same time the B transaction updates the locked record, creating a deadlock.
Start a transaction in session 1
start transaction;
In session 1, the query ID is 1 of the record and the shared lock is added.
select * from td where id=1 lock in share mode;
Start a transaction in session 2
start transaction;
In session 2, the query ID is 1 of the record and the shared lock is added.
select * from td where id=1 lock in share mode;
In session 1, Update ID 1 for the age value, wait for session 2 to release the shared lock
update td set age=200 where id=1;
In session 2, update ID 1 for age, Session 2 discovers a deadlock, and rolls back the transaction.
update td set age=200 where id=1;
Commit a transaction in session 1
COMMIT;

Vii. Examples of transactions

Transaction commit or Rollback, you can determine whether an error occurred at the end of the transaction, and if so, rollback. If there are no errors, commit the transaction.
Use a custom condition to determine whether a transaction is committed or rolled back.

1. Error-determined transaction commit or rollback

Use a transaction in a stored procedure, determine if there is an error at the end of the transaction, and then rollback the transaction if the insert fails.
Create two tables, store ID, name, age, and create a stored procedure to transfer the record of the specified ID of table A to table B.

CREATE TABLE ta(id INT NOT NULL PRIMARY KEY,name VARCHAR(10),age INT)ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into ta values(1, ‘孙悟空‘, 500);insert into ta values(2, ‘唐僧‘, 30);CREATE TABLE tb(id INT NOT NULL PRIMARY KEY,name VARCHAR(10),age INT)ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into tb values(1, ‘孙悟空‘, 500);insert into tb values(3, ‘猪八戒‘, 100);CREATE PROCEDURE move(num INT)BEGINDECLARE errorinfo INT DEFAULT 0;DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET errorinfo=1;START TRANSACTION;INSERT INTO tb SELECT * FROM ta WHERE id=num;DELETE FROM ta WHERE id=num;IF errorinfo=1    THEN ROLLBACK;ELSE   COMMIT;END IF;END

Transfer records with ID 2 from table A to table B
call move(2);

2. The custom condition determines whether the transaction commits or rolls back

Create two tables, each with account, name, balance information, create a stored procedure, transfer a certain amount from one account in table A to an account in table B, or roll back if the balance of the transferred out account is insufficient, otherwise submit.

create table accountA(account INT PRIMARY KEY NOT NULL,name VARCHAR(10),balance DOUBLE)ENGINE=innoDB default CHARSET=utf8;insert into accountA VALUES(1, ‘孙悟空‘, 10000);insert into accountA VALUES(2, ‘唐僧‘, 20000);create table accountB(account INT PRIMARY KEY NOT NULL,name VARCHAR(10),balance DOUBLE)ENGINE=innoDB default CHARSET=utf8;insert into accountB VALUES(1, ‘孙悟空‘, 10000);insert into accountB VALUES(2, ‘唐僧‘, 20000);CREATE PROCEDURE transfer(fromaccout INT,toaccount INT, num DOUBLE)BEGINDECLARE m DOUBLE;START TRANSACTION;UPDATE accountB SET balance=balance + num WHERE account=toaccount;UPDATE accountA SET balance=balance - num WHERE account=fromaccout;SELECT balance INTO m from accountA WHERE account=fromaccout;IF m < 0   THEN ROLLBACK;ELSE    COMMIT;END IF;END

From Account 2 of Table A, transfer $25000 to account 2 of table B.
call transfer(2,2,25000);
At this point the balance of a table is insufficient, rollback

MySQL database Advanced (vii)--Transactions and locks