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

MySQL Database Advanced (7)-- transaction and Lock 1, transaction introduction 1, transaction introduction

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

2. Characteristics of transactions

A, atomicity (Atomicity)

Indicates that the multiple database operations that make up a transaction are an inseparable atomic unit, the entire transaction commits only if all operations are executed successfully, any database operation in the transaction fails, and any operation that has been performed must be undone to return the database to its initial state.

B, consistency (Consistency)

After the transaction operation is successful, the state of the database is consistent with its business rules, that is, the data will not be destroyed.

C, isolation (Isolation)

In concurrent data operations, different transactions have their own data space, and their operations will not interfere with each other. The database prescribes a variety of transaction isolation levels, and different isolation levels correspond to different degrees of interference. The higher the isolation level, the better the data consistency, but the weaker the concurrency.

D, persistence (Durabiliy)

Once the transaction is committed successfully, all data operations in the transaction must be persisted to the database. Even if the database crashes immediately after the transaction is committed, the data must be able to be recovered through some mechanism when the database is restarted.

3. Transaction type

A. automatically commit transactions

By default, each TRANSACT-SQL command is a transaction, which starts and commits automatically by the system.

B, implicit transaction

You don't need to show the start transaction, you need to show the commit, and the implicit transaction is made up of any separate INSERT, UPDATE, or DELETE statements. It starts automatically when a large number of DDL and DML commands are executed and remains until the user explicitly submits them.

SHOW VARIABLES looks at the variables.

SET AUTOCOMMIT=0, turn off the autocommit function.

Need to show commit or rollback.

Update tablename set sname=' Sun WuKong 'where studentid='000000000000003';commit

Or

Rollback

C, display transactions

Shows that the transaction is a user-defined transaction, starting with START TRANSACTION (transaction start) and ending with COMMIT (transaction commit) or ROLLBACK (rollback transaction) statement.

Start transaction update tablename set sname=' Sun WuKong 'where studentid='000000000000003';commit

Or

Rollback

D, distributed transaction

Transactions that span multiple servers are called distributed transactions. Distributed transactions are supported starting with MySQL5.03.

4. Transaction control

A. Start a transaction

Marks the starting point of an explicit transaction, that is, the start of the transaction. The syntax is as follows:

START {TRAN | TRANSACTION}

B. Commit transaction

Marks the end of a successful implicit or explicit transaction, that is, transaction commit. The syntax is as follows:

COMMIT

C, roll back the transaction

Rollback an explicit or implicit transaction to the starting point of the transaction or to a SavePoint within the transaction. The syntax is as follows:

ROLLBACK

D, transaction settings

SET AUTOCOMMIT can modify the way the current connection transaction is committed.

SET AUTOCOMMIT=0, you need an explicit command to commit or roll back.

5. Problems caused by transaction concurrency

Dirty reading (Dirty Read) means that one transaction (A) reads the changed data that has not been committed by another transaction (B) and operates on the basis of the read data. If the B transaction happens to be rolled back, the data read by the A transaction is not recognized at all.

Unrepeatable read (Unrepeatable Read) means that A transaction reads the change data that B transaction has committed.

Phantom Reading (Phantom Read)

The A transaction reads the new data committed by the B transaction, and the A transaction will have the problem of phantom reading.

The first category is missing updates

When the A transaction is undone, the updated data of the committed B transaction is overwritten.

The second category is missing updates.

A transaction overwrites the committed data of B transaction, resulting in the loss of B transaction operation.

Transaction isolation level 1. Brief introduction of transaction isolation level

The SQL standard defines four types of isolation levels, including specific rules that define which changes inside and outside the transaction are visible and which are not. Low-level isolation levels generally support higher concurrent processing and have lower system overhead.

Read Uncommitted (read uncommitted)

At this isolation level, transactions can read the execution results of other uncommitted transactions. Reading uncommitted data is also known as Dirty Read.

Read Committed (read submission)

The default isolation level for most database systems (but not the default for MySQL). 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 a new commit during the processing of this instance, so the same select may return different results.

Repeatable Read (reread)

The default transaction isolation level of MySQL takes a snapshot of the query record until the transaction ends. Make sure that when multiple instances of the same transaction read data concurrently, they will see the same data row, which will result in Phantom Read. Phantom reading means that when a user reads a range of data rows, another transaction inserts a new row in that range, and when the user reads the range of data rows, it will find a new "phantom" row. InnoDB and Falcon storage engines solve the problem of phantom reading through multi-version concurrency control (MVCC,Multiversion Concurrency Control) mechanism.

Serializable (serializable)

At the highest isolation level, multiple transactions read and modified to the same record can only end one before starting the next one.

The problem of illusory reading can be solved by forcing the ordering of transactions to make it impossible for them to conflict with each other. Adding a shared lock to each read data row can lead to a large number of timeouts and lock competition.

2. Transaction isolation level setting

Users can use SET TRANSACTION statements to change the isolation level for a single session or for all new 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 newly created, which requires SUPER permission. Use the SESSION keyword to set the default transaction level for future transactions executed on the current connection. Any client is free to change the session isolation level or set the isolation level for the next transaction.

Query 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 and take effect.

Setting the 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 verification 1. Isolation levels of different sessions

Transaction isolation levels vary from session to session

View the transaction isolation level of the current session on the session 1 terminal

Select @ @ tx_isolation

The query result is: readable 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 of the current session

Select @ @ tx_isolation

The query result is: readable REPEATABLE-READ

Create a table with ID, name, and age fields to verify different transaction isolation levels.

CREATE TABLE ta (id INT NOT NULL PRIMARY KEY,name VARCHAR (10), age INT) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into ta values (1, 'Sun WuKong', 500); insert into ta values (2, 'Tang Monk', 30)

Note: due to the failure of the transaction rollback mechanism of my SQL Manager Lite client, the Navicat for MySQL client is used in the following experiment.

2. Verify the READ UNCOMMITTED isolation level

Open a session 1 and set the transaction isolation level to READ UNCOMMITTED

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Open session 2, start a transaction, and update the record with an ID of 1 with an age of 1000.

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

Look at the information in the ta table with an ID of 1 in session 1, and the age is already 1000.

Select * from ta

The transaction isolation level of session 1 allows uncommitted data to be read.

Roll back the transaction in session 2

ROLLBACK

Session 1 and session 2 query records in the ta table with ID 1, with an age of 500

3. Verify the READ COMMITTED isolation level

Open a session 1 and set the transaction isolation level to READ COMMITTED

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

Open session 2, start a transaction, and update the record with an ID of 1 with an age of 5000.

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

View the information in the ta table with an ID of 1 in session 1, with an age of 500.

Select * from ta

The transaction isolation level of session 1 does not allow reading uncommitted data.

Commit the transaction in session 2

COMMIT

Session 1 queries records in the ta table with an ID of 1, with an age of 5000

4. Verify the REPEATABLE READ isolation level

Open a session 1 and set the transaction isolation level to REPEATABLE READ

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

In session 1, start a transaction and query a record with an ID of 1 with an age of 5000.

Start TRANSACTION;SELECT * FROM ta where id = 1

In session 2, update the information in the ta table with an ID of 1 and an age of 1000.

UPDATE ta SET age=1000 WHERE id=1

Look at the information in the ta table with an ID of 1 in session 2, and the age is already 1000.

Select * from ta WHERE id=1

If you look at the information in the ta table with ID 1 again in session 1, the age is still 5000.

Select * from ta WHERE id=1

Commit the transaction at session 1

COMMIT

Session 1 queries the ta table for records with an ID of 1, and the age is already 1000.

5. Verify the SERIALIZABLE isolation level

Open a session 1 and set the transaction isolation level to SERIALIZABLE

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE

Open session 2, start a transaction, and update the record with an ID of 1 with an age of 5000.

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

Start a transaction at session 1 and view the information in the ta table with an ID of 1. Session 1 is in a waiting state.

Start TRANSACTION;select * from ta

After session 2 commits the transaction

COMMIT

Session 1 query SQL execution completed, the result is 5000.

Lock 1. Lock introduction

A lock in a database is a software mechanism used to control and prevent a user (process session) from taking up a certain data resource. Other users make means that affect their own data operations or lead to data incompleteness and inconsistency.

2. Level of lock

According to the lock level, locks can be divided into shared locks and exclusive locks.

A, shared lock (read lock)

For the same piece of data, multiple reads can be performed at the same time without affecting each other.

Shared locks are only locked for UPDATE, and other transactions can only 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 before the current write operation is completed.

3. The granularity of the lock

According to the granularity of locks, locks can be divided into table-level locks, row-level locks, and page-level locks.

A, row-level lock

High cost, slow locking, deadlock will occur, locking strength is the least, the probability of lock conflict is the lowest, and the degree of concurrency is high.

B, table level lock

The utility model has the advantages of low overhead, fast locking, no deadlock, strong locking, high probability of conflict and low concurrency.

C, page lock

The cost and locking time are between table lock and row lock, deadlock occurs, locking strength is between table and row level lock, and the concurrency is average.

4. MySQL storage engine and locking mechanism

The locking mechanism of MySQL is relatively simple, and the most prominent feature is that different storage engines support different locking mechanisms.

The MyISAM and MEMORY storage engines use table-level locks.

InnoDB supports row-level locks and table-level locks, and row-level locks are used by default.

Table-level lock 1. Introduction to table-level lock

Both the MyISAM storage engine and the InnoDB storage engine support table-level locking.

The MyISAM storage engine supports table-level locks. In order to ensure the consistency of the data, table-level locks can be added manually to prevent others from changing the data. You can use the command to unlock the table of the database, and use the command to unlock the table of the database.

The command Lock Tables for locking the table, and the command Unlock Tables for unlocking the table

The MyISAM engine automatically adds READ locks to the data read by users, and automatically adds WRITE locks to change data. Use lock Tables and Unlock Tables to explicitly lock and unlock.

2. Add table-level read locks

Open session 1 and create a table

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

Insert two records:

Insert into tc values (1,500 'Sun WuKong'); insert into tc values (3,100 'Zhu Bajie')

Add a READ lock to the table

Lock tables tc read

Only locked tables can be queried after being locked

Select * from tc

Querying unlocked tables will fail

Select * from ta

Open session 2 and query the locked table successfully.

Select * from tc

Update the 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 updating or accessing other tables will prompt an error; session 2 can query the records in the table, but the update will cause lock waiting.

The table is unlocked in session 1 and the update operation for session 2 is successful.

Unlock tables

In session 1, lock the table tc again, followed by the local parameter.

Lock tables tc read local

The Local parameter allows concurrent insertion at the end of the table, locking only the current record in the table, and other sessions can insert new records.

Insert a record in session 2

Insert into tc values (2, Tang Monk, 20)

View the records of the tc table in session 1, no inserted records

Select * from tc

3. Set table-level lock concurrency

READ locks are shared locks that do not affect the reading of other sessions, but cannot update data that is already locked by READ. The read and write of the MyISAM table is serial, but generally speaking, 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 its concurrent insertion behavior, which can be 0, 1, or 2, respectively.

0: concurrent operations are not allowed

1: if there are no holes in the MyISAM table (that is, there are no deleted rows in the middle of the table), MyISAM allows one process to read the table while another process inserts records from the footer, which is the default setting for MySQL.

2: records are allowed to be inserted concurrently at the end of the table, regardless of whether there are holes in the MyISAM table.

The MySQL configuration file adds, concurrent_insert=2, restart mySQL service settings take effect.

4. Verify 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, where the tc table is locked and goes into waiting

Insert into tc values (4, 'Sha Wujing', 30)

Unlock the table tc in session 1, and session 2 is inserted successfully

Unlock tables

Set concurrent_insert to 1

Delete a record with an ID of 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, when the tc table is locked and there is a hole in the table, entering the wait

Insert into tc values (5, 'Baigujing', 1000)

When the table tc is unlocked in session 1, session 2 is successfully inserted, and there are no holes in the table.

Unlock tables

Lock table tc in session 1

Lock tables tc read local

Insert a record in session 2 successfully. Conditional concurrent insertion is supported.

Insert into tc values (6, 'Baigujing', 1000)

Unlock table tc in session 1

Unlock tables

Set concurrent_insert to 2

Delete the record with an ID of 5 in session 1, creating a hole

Delete from tc where id=5

Lock table tc in session 1

Lock tables tc read local

Insert a record in session 2 successfully. Unconditional concurrent insertion is supported.

Insert into tc values (7, 'Spider essence', 1000)

Unlock table tc in session 1

Unlock tables

5. Add table-level write locks

The syntax for adding a table-level write lock is as follows:

LOCK TABLES tablename WRITE

Other sessions are not allowed to query, modify, or insert records.

Row-level lock 1. Introduction to row-level lock

The InnoDB storage engine implements MVCC (Multi-Version Concurrency Control), which is based on multiple versions of the concurrency control protocol. The advantage of MVCC is that there is no lock on read and no conflict between read and write. In OLTP applications that read more and write less, it is very important that there is no conflict between read and write, which greatly increases the concurrent performance of the system.

In MVCC concurrency control, read operations can be divided into two categories: snapshot read (snapshot read) and current read (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 records returned by the current read will be locked to ensure that other transactions will not be modified concurrently. Transaction locking means that other rows are not locked for the rows they operate on.

Snapshot read: simple SELECT operation, belongs to snapshot read, unlocked.

Select * from table where?

Current read: a special read operation, INSERT/UPDATE/DELETE, 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?

The above SQL statement belongs to the current read, the latest version of the read record. Moreover, after reading, you also need to ensure that other concurrent transactions cannot modify the current record and lock the read record. Among them, except for the first statement, S lock (shared lock) is added to the read record, and X lock (exclusive lock) is added to all other operations.

2. Verify snapshot read

Open session 1 and create a table with ID, name, and age

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

Inserting two records

Insert into td values (1,500 'Sun WuKong'); insert into td values (2,100 'Zhu Bajie')

Start a transaction at session 1

Start transaction

Query the record information of ID bit 1 in session 1

Select * from td where id = 1

Open session 2 and update age with ID 1 to 1000

Update td set age=1000 where id=1

Check in session 2 that the age with ID 1 has been updated to 1000.

Select * from td where id = 1

Check the age with ID 1 in session 1, which is still 500.

Select * from td where id = 1

Commit the transaction at session 1

COMMIT

Check the age with ID 1 in session 1, which is already 1000.

3. Verify the current read

Start a transaction at 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, enter the lock and wait

Update td set age=100 where id=1

Commit the transaction at session 1

COMMIT

The update operation for session 2 was successful.

4. Verify that the transaction locks the record

Start a transaction at session 1

Start transaction

Update the value of age with ID 1 in session 1 to 500.

Update td set age=500 where id=1

Start the transaction at session 2

Start transaction

When session 2 updates the value of age with ID 2 as 1000, enter the lock and wait.

Update td set age=1000 where id=2

The td table does not specify a primary key, and transactions do not support row-level locks. The transaction of session 1 locks the entire table.

The transaction is committed in session 1, and the modification of session 2 is successful

COMMIT

Commit the transaction in session 2 and unlock the table

COMMIT

In session 1, add a primary key to the ID of the table

Alter table td add primary key (id)

Start a transaction at session 1

Start transaction

Update the value of age with ID 1 in session 1 to 5000

Update td set age=5000 where id=1

Start a transaction on session 2

Start transaction

On session 2, the value of get with ID 2 is modified to 10000, which indicates that session 1 only locks rows with ID 1.

Update td set age=10000 where id=2

Wait occurs when the age value for updating ID is 1 on session 2 is 100. Because session 1 adds an exclusive lock to a row with an ID of 1.

Update td set age=5000 where id=1

Commit the transaction at session 1

COMMIT

Commit the transaction in session 2

COMMIT

In session 1 query, both session 1 and session 2 changes to the age column take effect

Select * from td

5. The generation of deadlock

After a transaction adds a shared lock, a B transaction can also add a shared lock. A transaction UPDATE locks the record and is waiting, and at the same time transaction B also UPDATE updates the locked record, resulting in a deadlock.

Start a transaction at session 1

Start transaction

Query session 1 for records where ID is 1 and add a shared lock.

Select * from td where id=1 lock in share mode

Start the transaction at session 2

Start transaction

Query the record where ID is 1 in session 2 and add a shared lock.

Select * from td where id=1 lock in share mode

Update the age value of ID 1 in session 1 to wait for session 2 to release the shared lock

Update td set age=200 where id=1

When session 2 updates age with ID 1, session 2 finds a deadlock and rolls back the transaction.

Update td set age=200 where id=1

Commit the transaction at session 1

COMMIT

VII. Transaction instance

Whether the transaction is committed or rolled back, you can determine whether there is an error at the end of the transaction, and if so, rollback. If there are no errors, commit the transaction.

Use custom conditions to determine whether the transaction is committed or rolled back.

1. Transaction commit or rollback is determined by error

Use a transaction in a stored procedure, determine whether there is an error at the end of the transaction, and if the insert fails, the transaction is rolled back.

Create two tables, store the ID, name, and age, and create a stored procedure to transfer the records 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, 'Sun WuKong', 500); insert into ta values (2, 'Tang Monk', 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, 'Sun WuKong', 500); insert into tb values (3, 'Zhu Bajie', 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. Transaction commit or rollback is determined by custom conditions

Create two tables, each containing account, name and balance information, create a stored procedure to transfer a certain amount from an account in Table A to an account in Table B, and roll back if the balance of the transferred 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, 'Sun WuKong', 10000); insert into accountA VALUES (2, 'Tang Monk', 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, 'Sun WuKong', 10000); insert into accountB VALUES (2, 'Tang Monk', 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

Transfer 25000 yuan from account 2 in form A to account 2 in form B.

Call transfer (2Jing 2Jing 25000)

At this time, the balance of Table An is insufficient. Roll back.

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.