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

Title index

Transactional role

Transaction flow

Isolation level

Example verification

Transactional role

Transaction is a function to ensure the data integrity of the database system. For example, in the payment business of the Internet industry, no matter what reason the server is abnormally interrupted, the customer either pays successfully or does not pay successfully. The amount of the database will change if the payment is successful, and the amount of the unsuccessful customer will not change, ensuring the stability of the transaction business. The engine that supports things must satisfy ACID and ACID before they can satisfy things. The rollback or recovery of other things mainly depends on the log of things. The meanings of ACID are as follows:

A:atomicity (atomicity): all operations in the whole thing are the smallest unit of command execution, all execution, half of the failed rollback or failed rollback

C:consistency (consistency): the database changes from one state to another, and the state is consistent before and after the transition.

I:isolation (isolation): actions done by one thing cannot be seen by others until committed, so isolation has a variety of isolation levels, including read-uncommitted read as commit, read-committed read commit, repeatable-read repeatable read, and serializable serialization.

D:durability (persistence): once a thing is committed, what is done is permanently saved in the database.

Transaction flow

The workflow of the transaction is shown in the following figure

Figure 1-1 work flow of things

As can be seen from the above figure, when the database starts a thing through start transaction, a series of operations are carried out on the database after starting the transaction, and finally there are two kinds of submitted transactions, the first is commit submission, and the second is rollback rollback. Once the transaction is submitted, the database is in a new state to maintain persistence. In addition, in order to prevent the database from writing data from memory to disk after the submission of things, the abnormal power failure of the operating system leads to unable to save, and the log function is enabled. As long as the transaction log function is enabled, things first write logs in the continuous space of the disk. Then synchronize to the disk through memory to ensure that in case of memory synchronization of the disk, the machine is abnormally powered down, and the database data is recovered through the transaction log.

Isolation level

Isolation level (INNODB default isolation level is repeatable read):

READ UNCOMMITTED (read not submitted): this isolation level causes problems such as dirty reading and non-repeatable reading.

READ COMMITTED (read commit): this isolation level solves dirty reads, but still has unrepeatability.

REPEATABLE READ (rereadable): this isolation level solves dirty reading and unrepeatable reading, causing problems with phantom reading.

SERIALIZABLE (serializable): this isolation level addresses dirty reads, unrepeatability, and phantom reads, but the problem is locked reads.

Explanation of the question:

Dirty reading: when user A modifies the data but does not submit it, user B reads the data modified by A, but user A rolls the data back to rollback, so user B sees the wrong data.

Can not be read repeatedly: if user A starts a transaction and sets a certain value to ON, the query has already set the value to ON, but user B modifies the value to OFF and submits, and user A finds that the value is OFF again, or there is another statement in the database, which shows that the database is always inconsistent every time it is queried in the same transaction.

Illusory reading: when user A user sees a value of ON in the same transaction, user B has changed the value to OFF and committed, the value seen by user B is OFF, but what user An always sees in this transaction is ON, and the underlying data is indeed modified to OFF, so it reflects illusory reading. Unless a transaction view value is generated again after commit, it will be OFF.

Locked read: lock the data when you read it, so that others can no longer read it.

Example verification

According to the theoretical overview, verify the workflow and isolation level of things to ensure a thorough understanding of the principles of things, the specific operations are as follows

MariaDB [(none)] > show processlist # View the process list of the database Shows that there are two terminals connected +-+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-- + | 2 | root | localhost | NULL | Sleep | 23 | | NULL | 0.000 | | 10 | | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 | +-+ 2 rows in set (0.00 sec) MariaDB [ (none)] > show global variables like 'tx_isolation' # verify the isolation level of things +-+-+ | Variable_name | Value | +-+-+ | tx_isolation | REPEATABLE-READ | +-+ -+ 1 row in set (0.00 sec)

Verify isolation level READ UNCOMMITTED (dirty read, non-repeatable read)

Step 1: create a table and insert data

MariaDB [test] > create table employee (id int,name varchar (20), age char (3)); # create table Query OK, 0 rows affected (0.00 sec) MariaDB [test] > insert into employee values (1 minute tangsengli pr. 38). # add user Query OK to the table, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0

Step 2: set the isolation level to READ-UNCOMMITTED in the session variables of the two database connection threads

Session 1MariaDB [(none)] > set tx_isolation='READ-UNCOMMITTED';Query OK, 0 rows affected (0 sec) MariaDB [test] > start transaction; # Open thing Query OK, 0 rows affected (0 sec) session 2MariaDB [(none)] > set tx_isolation='READ-UNCOMMITTED';Query OK, 0 rows affected (0 sec) MariaDB [(none)] > MariaDB [test] > start transaction # enable Query OK for things, 0 rows affected (0.00 sec)

Step 3: enable things on both sides at the same time, where session 1 adds bailongma but does not submit, view authentication on session 2

Session 1 # inserts data but is not submitted. Session 2 verifies MariaDB [test] > insert into employee values after query on session 2; Query OK, 1 row affected (0.00 sec) session 2 # can be read after query verification that session 1 has not been submitted. If session 1 rolls back, session 2 reads dirty data MariaDB [test] > select * from employee +-+ | id | name | age | 1 | tangseng | 38 | 2 | sunwukong | 505 | 3 | zhubajie | 485 | 4 | shaheshang | 408 | 5 | bailongma | 300 | +-- -- +-+ 5 rows in set (0.00 sec)

Verify isolation level READ-COMMITTED (resolve dirty reading problem, there are non-repeatable reads)

Step 1: create a table and insert data

MariaDB [test] > create table employee (id int,name varchar (20), age char (3)); # create table Query OK, 0 rows affected (0.00 sec) MariaDB [test] > insert into employee values (1 minute tangsengli pr. 38). # add user Query OK to the table, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0

Step 2: set the isolation level to READ-COMMITTED in the session variables of the two database connection threads

Session 1MariaDB [(none)] > set tx_isolation='READ-COMMITTED';Query OK, 0 rows affected (0.00 sec) MariaDB [test] > start transaction; # Open transaction Query OK, 0 rows affected (0.00 sec) session 2MariaDB [(none)] > set tx_isolation='READ-COMMITTED';Query OK, 0 rows affected (0.00 sec) MariaDB [test] > start transaction; # Open transaction Query OK, 0 rows affected (0.00 sec)

Step 3: add bailongma to two threaded processes that connect to the database, but do not submit for verification

Session 1MariaDB [test] > insert into employee values; Query OK, 1 row affected (0.00 sec) session 2 # when session 1 is not committed, session 2 cannot read data MariaDB [test] > select * from employee +-+ | id | name | age | +-+ | 1 | tangseng | 38 | 2 | sunwukong | 505 | 3 | zhubajie | 485 | 4 | shaheshang | 408 | +-+ 4 rows in set (0.00 sec)

Step 4: commit on session 1, and then verify on session 2

Session 1MariaDB [test] > commit;Query OK, 0 rows affected (0.00 sec) session 2 MariaDB [test] > select * from employee +-+ | id | name | age | 1 | tangseng | 38 | 2 | sunwukong | 505 | 3 | zhubajie | 485 | 4 | shaheshang | 408 | 5 | bailongma | 305 | +-- -- + 5 rows in set (0.00 sec) # indicates that read submission can solve the problem of dirty reading

Verify isolation level REPEATABLE READ (solve the problem of dirty reading and repeated reading, bring new problem phantom reading)

Step 1: create a table and insert data

MariaDB [test] > create table employee (id int,name varchar (20), age char (3)); # create table Query OK, 0 rows affected (0.00 sec) MariaDB [test] > insert into employee values (1 minute tangsengli pr. 38). # add user Query OK to the table, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0

Step 2: set the isolation level to REPEATABLE-READ in the session variables of the two database connection threads

Session 1MariaDB [(none)] > set tx_isolation='REPEATABLE-READ';Query OK, 0 rows affected (0.00 sec) MariaDB [test] > start transaction; # Open transaction Query OK, 0 rows affected (0.00 sec) session 2MariaDB [(none)] > set tx_isolation='REPEATABLE-READ';Query OK, 0 rows affected (0.00 sec) MariaDB [test] > start transaction; # Open transaction Query OK, 0 rows affected (0.00 sec)

Step 3: first open a transaction query table in session 2, then add bailongma users in session 1, and view the data in the table again in the same transaction in session 2 (it is found that the data in session 1 has changed, and the data in session 2 is still the previous data in the same thing, so it solves the problem of repeatable readability.)

Session 2 # Open a transaction and query the data in the table MariaDB [test] > select * from employee +-+ | id | name | age | 1 | tangseng | 38 | 2 | sunwukong | 505 | 3 | zhubajie | 485 | | 4 | shaheshang | 408 | + -- + 4 rows in set (0.00 sec) session 1 # after adding bailongma data Submit and query MariaDB [test] > insert into employee values. Query OK, 1 row affected (0.00 sec) MariaDB [test] > commit;Query OK, 0 rows affected (0.00 sec) MariaDB [test] > select * from employee +-+ | id | name | age | 1 | tangseng | 38 | 2 | sunwukong | 505 | 3 | zhubajie | 485 | 4 | shaheshang | 408 | 5 | bailongma | 305 | +-- -- + + 5 rows in set (0.00 sec) session 2 # query again on session 2 The result is still 4 pieces of data because the things on session 2 are not committed and are readable again, so you can only see 4 pieces of data unless you query again after submitting the item MariaDB [test] > select * from employee +-+ | id | name | age | 1 | tangseng | 38 | 2 | sunwukong | 505 | 3 | zhubajie | 485 | | 4 | shaheshang | 408 | + -- + 4 rows in set (0.00 sec) MariaDB [test] > select * from employee +-+ | id | name | age | 1 | tangseng | 38 | 2 | sunwukong | 505 | 3 | zhubajie | 485 | 4 | shaheshang | 408 | 5 | bailongma | 305 | +-- -- +-+ 5 rows in set (0.00 sec)

Verify isolation level SERIALIZABLE (to solve the problem of repeated reads, it is important to note that each operation needs to restart new things and commit, because with locks, a thing can only be a set of statements)

Step 1: create a table and insert data

MariaDB [test] > create table employee (id int,name varchar (20), age char (3)); # create table Query OK, 0 rows affected (0.00 sec) MariaDB [test] > insert into employee values (1 minute tangsengli pr. 38). # add user Query OK to the table, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0

Step 2: set the isolation level to SERIALIZABLE in the session variables of the two database connection threads

Session 1MariaDB [(none)] > set tx_isolation='SERIALIZABLE';Query OK, 0 rows affected (0.00 sec) MariaDB [test] > start transaction; # Open thing Query OK, 0 rows affected (0.00 sec) session 2MariaDB [(none)] > set tx_isolation='SERIALIZABLE';Query OK, 0 rows affected (0.00 sec) MariaDB [test] > start transaction; # Open thing Query OK, 0 rows affected (0.00 sec)

Step 3: before inserting bailongma users on session 1, query and submit on session 2, add bailongma and submit on session 1, and then query on session 2 again

Session 1 # does not submit MariaDB [test] > insert into employee values after adding users; Query OK, 1 row affected (0.00 sec) session 2 # locks the table when session 1 is not submitted, so it cannot be queried on session 2, so solve the phantom reading MariaDB [test] > select * from employee;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

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.