Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of transactions and locks in MySQL, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

MySQL database is a multi-user access system, so we have to face that when multiple users read and update data at the same time, the data will not be destroyed, so the lock, a concurrency control technology, is born. When a user tries to modify the records in the database, he must first acquire the lock, so while the user holding the lock is still modifying, other users can not modify these records.

Locks in MySQL

However, compared with other databases, the locking mechanism of MySQL is relatively simple. Different storage engines of MySQL have different locking mechanisms. MylSAM and MEMORY storage engines use table-level locks, BDB storage engines use page locks, and common InnoDB storage engines support row-level locks and table-level locks, and row-level locks are used by default.

The characteristics of these three locks are as follows:

Table-level lock: low overhead, fast locking, no deadlock, large lock granularity, the highest probability of lock conflict and the lowest concurrency.

Row-level lock: high overhead, slow locking, deadlock will occur, lock granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.

Page lock: overhead and locking time are between table lock and row lock, deadlock occurs, lock granularity is between table lock and row lock, and the concurrency is general.

MyISAM

MyISAM table lock

MySQL provides two types of locks for tables, which are:

READ LOCK: allows the user to read data only from the table.

WRITE LOCK: allows users to read and write to the table.

The read operation of MyISAM to the table will not block the read request of other users to the same table, but it will block the write request to the same table, the write operation of MyISAM to the table will block the read and write operation of other users to the same table, and the read operation and write operation of MyISAM table are serial.

Before executing the query statement (SELECT), MyISAM will automatically add read locks to all tables used, and automatically write locks to the tables involved before performing update operations (UPDATE, DELETE, INSERT, etc.), this process does not require our manual intervention, so we generally do not need to use the LOCK TABLE command to explicitly lock the MyISAM table, but it shows that locking is not a problem.

Also, when you explicitly add a table lock to a table with LOCK TABLES, you must obtain all the locks involving the table at the same time, because after executing LOCK TABLES, you can only access these explicitly locked tables, not the unlocked tables, otherwise you will make an error. At the same time, if you add a read lock, you can only perform query operations, not update operations, otherwise an error will be reported. This is why there is no deadlock in the MyISAM table.

Let's take a look at a column.

1. Create a table

CREATE TABLE test_table (Id INT NOT NULL AUTO_INCREMENT, Name VARCHAR (50) NOT NULL, Message VARCHAR (80) NOT NULL, PRIMARY KEY (Id)

2. Session 1 acquires a write lock

Mysql > lock table test_table write;Query OK, 0 rows affected (0.01 sec)

3. Session 2 read.

We know that when a session holds a WRITE lock, none of the other sessions can access the table's data, so the second session is waiting when it executes the following statement.

Mysql > select * from test_table

4. Session 1 unlock

Unlock table

Concurrent insertion

In MyISAM, read and write operations are serial, but you can have MyISAM support parallel queries and inserts according to the settings of concurrent_insert.

The values of concurrent_insert are as follows:

0: concurrent insertion is not allowed.

1: concurrent inserts are allowed for tables that are not empty, and the new data is at the end of the data file (the default).

2: whether the table is empty or not, concurrent insertions are allowed at the end of the data file.

Emptiness refers to rows that have not been deleted in the middle of the table.

InnoDB

InnoDB is different from MyISAM in that it supports transactions and adopts row-level locks. There are many differences between row-level locks and table locks.

Transaction characteristics

Atomicity

A transaction is an atomic operation unit, and either all or none of the changes to the data are performed.

Consistency

The data must be in a consistent state at the beginning and completion of the transaction. This means that all relevant data rules must be applied to the modification of the transaction to maintain the integrity of the data.

Isolation

The database system ensures that transactions are not affected by external concurrent operations and can be executed in an "independent" environment, which means that the intermediate state in the transaction process is invisible to the outside.

Persistence

After the transaction is completed, its modification of the data is permanent and can be maintained even in the event of a system failure.

Problems caused by concurrent transaction processing

Compared with serial processing, although it improves the resource utilization and can support more users, concurrent transaction processing also brings some problems, including the following situations.

Update lost

Because each transaction is unaware of the existence of other transactions, the problem of missing updates occurs, that is, the final update overwrites the updates made by other transactions.

Dirty reading

Dirty reading is also known as invalid data readout, when transaction 1 modifies a value, and then transaction 2 reads the value, and then transaction 1 undoes the modification of the value for some reason, which results in the invalid data read by transaction 2.

Non-repeatable

It means that after reading some data, a transaction reads the previously read data again, only to find that the read data has changed.

Illusory reading

When transaction 1 re-reads the previously queried data according to the same query condition, it is found that other transactions have inserted new data that meets this condition.

Transaction isolation level

The "update loss" mentioned above should be completely avoided, but preventing update loss cannot be solved by the database transaction controller alone. It requires the application to add the necessary locks to the data to be updated.

Dirty reading, unrepeatable reading and phantom reading are all problems of database read consistency, which must be solved by the database to provide transaction isolation mechanism. Database transaction isolation can be divided into the following two ways, one is to lock before reading the data to prevent other transactions from modifying the data, and the other does not need a lock, realized through MVCC or MCC, this technology is called data multi-version concurrency control, through a certain mechanism to generate a consistent data snapshot of a data request point in time, and use this snapshot to provide a certain level of consistency reading.

The stricter the transaction isolation of the database, the less the side effects, but the greater the cost, because the essence of transaction isolation is to serialize transactions to a certain extent.

InnoDB has four transaction isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The default isolation level is REPEATABLE READ.

Isolation level dirty read non-repeatable fantasy read uncommitted √√√ read submitted × √√ repeatable read * × √ serializable (serializable) * *

Query / change isolation level

Display isolation level show global variables like'% isolation%';select @ @ transaction_isolation; set isolation level set global transaction_isolation = 'read-committed';set session transaction isolation level read uncommitted

READ UNCOMMITTED (read unsubmitted)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications, and reading uncommitted data is also known as dirty reading.

Examples

Start two sessions and set the isolation level to READ UNCOMMITTED.

Mysql > select * from user;+-+-+ | user_name | balance | +-+-+ | Zhang San | 100 | Li Si | 100 | Wang Wu | 80 | +-+-+ time transaction 1 transaction 2T1 beginning begin T2select * from user where user_name= "Zhang San"

At this time, Zhang San has a balance of 100.

T3

Select * from user where user_name= "Zhang San"

At this time, Zhang San's balance 100T4update user set balance = 80 where user_name = "Zhang San"

T4

Select * from user where user_name= "Zhang San"

At this time, Zhang San's balance 80T5commitcommit

As you can see, at T4, transaction 1 is not committed, but transaction 2 can see the data changed by transaction 1 lock.

READ COMMITTED (read submitted)

This is the default isolation level for most database systems, but not the default level for MySQL, and it avoids dirty reads because it is invisible to any other transaction before any uncommitted transaction, that is, other transactions cannot see uncommitted data and allow non-repeatable reading.

Examples

Set the isolation level in the two sessions to read committed set session transaction isolation level read committed; time transaction 1 transaction 2T1 beginning transaction T2 select * from user where user_name= "Zhang San"

At this time, Zhang San has a balance of 100.

T3

Select * from user where user_name= "Zhang San"

At this time, Zhang San's balance 100T4update user set balance = 80 where user_name = "Zhang San"

T4

Select * from user where user_name= "Zhang San"

At this time, Zhang San's balance 100T5commit

T5

Select * from user where user_name= "Zhang San"

At this time, Zhang San's balance is 80.

As you can see, at T4, transaction 1 is not committed, but the data read by transaction 2 is still 100, and transaction 2 can only see it after transaction 1 commits.

REPEATABLE READ (repeatable)

This is the default transaction isolation level for MySQL, which ensures that when the same transaction reads data, it will see the same row of data, but there will be phantom readings. When transaction 1 queries conditionally, another transaction inserts a new data in that range, then transaction 1 will read the new data when it is read again. InnoDB and Falcon storage engines solve this problem through the mvcc (multi-version concurrency control) mechanism.

Examples

Set two session isolation levels to repeatable set session transaction isolation level repeatable read; time transaction 1 transaction 2T1 beginning transaction T2 update user set balance = 80 where user_name = "Zhang San"

T3commit

T4

Select * from user where user_name= "Zhang San"

Zhang San's balance is 100.

You can see that at T3, transaction 1 has committed the change, but in transaction 2 at T4, the original data is still read, but if transaction 2 is reduced by 10 yuan from the original, is the final balance 90 or 70? The answer is 70. .

Mysql > update user set balance=balance-10 where user_name= "Zhang San"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > select * from user where user_name= "Zhang San" +-+-+ | user_name | balance | +-+-+ | Zhang San | 70 | +-+-+ 1 row in set (0.00 sec) SERIALIZABLE (serialization)

It is the highest isolation level, and InnoDB implicitly converts all ordinary SELECT statements to SELECT. LOCK IN SHARE MODE, all transactions are executed sequentially, so dirty reads, non-repeatable reads, and phantom reads do not occur. However, because the transaction is executed serially, the efficiency will be greatly reduced

For example, set the isolation level to serialize set session transaction isolation level serializable; time transaction 1 transaction 2T1 beginistT2select * from user where user_name= "Zhang San"

T3

Update user set balance = 80 where user_name = "Zhang San"

This time, interestingly, transaction 2 updates are blocked at T3 because under the serializable isolation level, MySQL implicitly converts all normal SELECT queries to SELECT FOR SHARE, and transactions that hold SELECT FOR SHARE locks only allow other transactions to process SELECT rows, not other transactions UPDATE or DELETE them.

So with this locking mechanism, the inconsistent data scenarios we've seen before are no longer possible.

However, this lock has a timeout, and after waiting for a while, if another transaction does not commit or roll back the lock during that time, a lock wait timeout error will be thrown, as shown below:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionInnoDB row lock

InnoDB row-level locks are also divided into shared locks and exclusive locks.

Shared locks allow transactions that hold locks to read rows.

Exclusive locks allow you to update or delete rows that hold lock transactions.

In order to allow row locks and table locks to coexist and implement the multi-granularity locking mechanism, InnoDB also has two kinds of intention locks for internal use, both of which are table locks.

The intention shared lock transaction wants to acquire a shared lock for several rows in a table.

The intention exclusive lock transaction wants to acquire exclusive locks for certain rows in a table.

InnoDB row locking is achieved by locking index entries on the index, so InnoDB uses row-level locks only if it retrieves data through index conditions; otherwise, InnoDB uses table locks.

We can add locks as shown, but for update, delete and insert statements, InnoDB automatically adds exclusive locks to the datasets involved. For ordinary select statements, InnoDB does not add any locks. Here is the locking method shown:

Shared lock: SELECT FROM table_name WHERE... LOCK IN SHARE MODE

Exclusive lock: SELECT * FROM table_name WHERE... FOR UPDATE

Next-Key lock

When we use range conditions instead of equal conditions to retrieve data and request sharing or exclusive locks, InnoDB will lock the index entries of existing data records that meet the conditions. For records that are within the range of conditions but do not exist, it is called GAP, and InnoDB will also lock this "gap". This locking mechanism is the so-called Next-Key lock.

For example, if there are only 101 records in the user table, their user_id values are 1. 2. 0 respectively. .. 100. 101. use the following SQL when looking for user_id greater than 100.

Select.* from emp where user_id > 100 for update

This is a query for range conditions, and InnoDB locks not only records with user_id 101s, but also "gaps" with user_id greater than 101s, although these records do not exist.

InnoDB uses Next-Key locks to prevent phantom reading on the one hand and to meet the needs of recovery and replication on the other.

The above is all the content of the article "sample Analysis of transactions and locks in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.