Database Transaction "Isolation Level"

In order to quickly synchronize data needs, I staged two Python scripts, that is, two process synchronization data opened, the results of the server reported the database deadlock exception, through the troubleshooting Code and database log discovery, is caused by long transactions concurrency. There are two methods of accounting and transfers in the code, which involves more operations, adds a transaction for it, automatically rolls back when an exception is thrown, and uses the default isolation level (repeatable Read) of the database (MySQL). Referring to concurrency, generally think of the method of using synchronous code block processing, but because the project is distributed, a common library, only in the code lock is not guaranteed the accuracy of data, it can only be considered at the database level lock. Since the amount of data is temporarily small, my solution at the outset is to adjust the isolation level to the highest (Serializable), which is expensive, but can guarantee the accuracy of the data, the database locks will wait for each other, but when I resynchronize the data, still reported a large number of deadlock exceptions ( Com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:Deadlock found when trying to get lock; Try restarting transaction), carefully tracking the database log, found that there are two locks on two update statements waiting for each other (operation is a table), but these two update statements do not fall on the same line, because the query conditions are not the same. That's strange, in general, the problem of locking, the final troubleshooting is the reason why the table is not indexed, causing the transaction to lock the table, and because multiple transactions are simultaneously executed here, causing the lock to wait for each other, the final database selection rollback one of the transactions. After I found the problem, I indexed the table and then ran it without reporting the error (the subquery would also cause the lock table). To keep the code running efficiently, I set the database level to repeatable read, but then I found some data that came with a phantom read, and finally I added a Beforemoney filter to the UPDATE statement (update Wallet_stat set Money = Aftermoney WHERE id = xxx and money = Beforemoney), which guarantees the accuracy of the data, but what is insufficient here is that although the data is guaranteed to be correct, the concurrent transaction must be rolled back. It is also possible to consider the use of queues or optimistic locks to solve this problem, not detailed.

This is the whole process of dealing with this problem, I find a lot of articles in order to find the solution. Here's what I think is the better information:

What is a transaction:

There are 4 isolation levels for database transactions, from low to high to read uncommitted, Read committed, Repeatable read, and Serializable, which can solve the problems of dirty reading, non-repeatable reading, and Phantom reading one by one.

√: May appear x: does not appear

Note: We discuss the isolation level scenario, mainly in the case of multiple transactions concurrency, so the next explanation is around the transaction concurrency.

READ UNCOMMITTED not submitted

The company paid, the leader of the 5000 yuan to the Singo account, but the transaction did not submit, and Singo just to check the account, found that the salary has been to the account, is 5000 yuan whole, very happy. Unfortunately, the leadership found that the amount of wages issued to Singo is not correct, is 2000 yuan, and then quickly rolled back to business, modify the amount, the transaction will be submitted, and finally singo the actual salary of only 2000 yuan, Singo empty joy a game.

The above situation, that is what we call dirty Read, two concurrent transactions, "transaction A: lead to Singo payroll", "Transaction B:singo query Payroll account", transaction B read the transaction A has not yet committed data.

When the isolation level is set to read UNCOMMITTED, dirty reads can occur and how to avoid dirty reads, see the next isolation level.

Read Committed reading Commit

Singo take the payroll card to spend, the system read to Cary really have 2000 yuan, and at this time her wife also just in the online transfer, the Singo Pay card of 2000 yuan to another account, and before Singo submitted the business, when Singo deduction, System Check to Singo's payroll card has no money, deduction failure, Singo very puzzled, obviously card money, why ...

The above situation, that is what we call non-repeatable read, two concurrent transactions, "transaction A:singo consumption", "Transaction B:singo wife online transfer", transaction A in advance read the data, transaction B immediately updated the data, and committed the transaction, and transaction a read the data again, The data has changed.

When the isolation level is set to read Committed, dirty reads are avoided, but may cause non-repeatable reads.

The default level for most databases is read committed, such as SQL Server, Oracle. To resolve the issue of non-repeatable reads, see the next isolation level.

REPEATABLE READ repeat

You can avoid non-repeatable reads when the isolation level is set to repeatable read. When Singo took the payroll card to spend, once the system began to read the Payroll card information (that is, the start of the transaction), Singo's wife could not change the record, that is Singo wife can not be transferred at this time.

Although repeatable read avoids non-repeatable reads, it is possible to have phantom reads.

Singo's wife works in the banking department, and she often views Singo's credit card consumption records through the internal banking system. One day, she was inquiring into the total consumption amount of credit card in Singo month (select SUM (amount) from transaction where month = this month) was $80, and Singo at this time was good to eat outside the sea plug at the cashier to pay, spend 1000 yuan , which adds a $1000 consumption record (insert transaction ... ), and submitted a transaction, then Singo's wife will singo the current month credit card consumption details printed to A4 paper, but found that the total consumption of 1080 yuan, Singo wife is very surprised, thought there was an illusion, the illusion of such a generation.

Note: The default isolation level for MySQL is repeatable read.

Serialization of Serializable

Serializable is the highest transaction isolation level, with the highest cost and low performance, which is rarely used at this level, where the transaction sequence executes not only to avoid dirty reads, non-repeatable reads, but also to avoid Phantom reads.

Note: The article originates from http://blog.csdn.net/fg2006/article/details/6937413

MySQL Lock blocking analysis:

View Lock blocking thread information
Here are some methods for analysis:
Use show processlist to view

MySQL [(None)]> show processlist;
+----+------+-----------+------+---------+------+--------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+--------------+------------------------------------------+
| 2 | Root | localhost | NULL | Query | 0 | init | Show Processlist |
| 3 | Root | localhost | Test | Query | 70 | Sending Data | Select COUNT (*) from T3 a,t3 B |
| 4 | Root | localhost | Test | Query | 65 | Updating | Delete from emp where empno=7788 |
| 7 | Root | localhost | Test | Query | 68 | Updating | Update emp set sal=3500 where empno=7788 |
+----+------+-----------+------+---------+------+--------------+------------------------------------------+
4 rows in Set (0.00 sec)
If there are more threads in the database, this method is really not very good to confirm.

Directly using Show engine InnoDB status View

------------
Transactions
------------
Trx ID Counter 4131
Purge done for Trx's N:o < 4119 undo N:o < 0 state:running but idle
History list Length 126
LIST of transactions for each SESSION:
---TRANSACTION 0, not started
MySQL thread ID 2, OS thread handle 0x7f953ffff700, query ID + localhost root init
Show Engine InnoDB Status
---TRANSACTION 4130, ACTIVE-SEC starting index Read
MySQL tables in use 1, locked 1
Lock WAIT 2 lock struct (s), heap size 1 row lock (s)
MySQL thread ID 4, OS thread handle 0x7f953ff9d700, query ID updating
Delete from EMP where empno=7788
-------TRX has BEEN waiting $ SEC for this LOCK to be granted: # # wait 41s
RECORD LOCKS Space ID-page No 3 n bits the index ' PRIMARY ' of table ' test '. ' EMP ' TRX ID 4130 lock_mode X LOCKS rec But n OT Gap waiting
Record Lock, Heap No 9 physical record:n_fields 10; Compact format; Info bits 0 # # Thread 4 waits for an X lock on the primary key in Test.emp, page num=3
0:len 4; Hex 80001e6c; ASC L;;
1:len 6; Hex 000000001018; ASC;;
2:len 7; Hex 91000001420084; ASC B;;
3:len 5; Hex 53434f5454; ASC SCOTT;;
4:len 7; Hex 414e414c595354; ASC ANALYST;;
5:len 4; Hex 80001d8e; ASC;;
6:len 4; Hex 208794f0; ASC;;
7:len 4; Hex 80000bb8; ASC;;
8:sql NULL;
9:len 4; Hex 80000014; ASC;;

------------------
---TRANSACTION 4129, ACTIVE sec starting index Read
MySQL tables in use 1, locked 1
Lock WAIT 2 lock struct (s), heap size 1 row lock (s)
MySQL thread ID 7, OS thread handle 0x7f953ff6c700, query ID 111 localhost root updating
Update emp set sal=3500 where empno=7788
-------TRX have BEEN waiting SEC for this LOCK to be granted: # # Wait 45s
RECORD LOCKS Space ID-page No 3 n bits the index ' PRIMARY ' of table ' test '. ' EMP ' TRX ID 4129 lock_mode X LOCKS rec But n OT Gap waiting
Record Lock, Heap No 9 physical record:n_fields 10; Compact format; Info bits 0 # # Thread 7 waits for an X lock on the primary key in Test.emp, page num=3
0:len 4; Hex 80001e6c; ASC L;;
1:len 6; Hex 000000001018; ASC;;
2:len 7; Hex 91000001420084; ASC B;;
3:len 5; Hex 53434f5454; ASC SCOTT;;
4:len 7; Hex 414e414c595354; ASC ANALYST;;
5:len 4; Hex 80001d8e; ASC;;
6:len 4; Hex 208794f0; ASC;;
7:len 4; Hex 80000bb8; ASC;;
8:sql NULL;
9:len 4; Hex 80000014; ASC;;

------------------
---TRANSACTION 4128, ACTIVE sec
2 lock struct (s), heap size, 1 row lock (s)
MySQL thread ID 3, OS thread handle 0x7f953ffce700, query ID. localhost Root cleaning up

We know that the main root cause is still thread=3, but the result cannot be analyzed from the InnoDB status.

From the above, thread 4 and thread 7 are waiting for the primary key to Test.emp in the X lock, page num=3, but thread 7 waits for 45s, and thread 4 waits for 41s, is the lock requested after the thread 7, so it can be determined that thread 7 is blocking thread 4. As for why thread 7 waits, there is no root cause analysis.

use mysqladmin debug to view
# mysqladmin-s/tmp/mysql3306.sock Debug

Then in the error log, you will see:

Thread database.table_name locked/waiting Lock_type

3 TEST.T3 locked-read Low Priority read lock
7 Test.emp Locked-write High priority write lock
In this method, can find the thread id=3 and 7 is the block, but still not very accurate, not to judge the thread 7 is also blocked by thread id=3.

Using Innodb_lock_monitor to get a blocking lock thread

MySQL [test]> CREATE TABLE innodb_lock_monitor (a INT) Engine=innodb; # # Create this table in a database and you'll open the lock monitor
Query OK, 0 rows affected, 1 warning (0.07 sec)

MySQL [test]> Show Warnings\g
1. Row ***************************
Level:warning
code:131
Message:using the table name Innodb_lock_monitor to enable diagnostic output are deprecated and may are removed in future R Eleases. Use INFORMATION_SCHEMA or Performance_schema tables or SET GLOBAL innodb_status_output=on.
1 row in Set (0.00 sec)

Description: This has a warning in 5.6, but does not affect the use.

Then use show engine InnoDB status to view:

------------
Transactions
------------
Trx ID Counter 4667
Purge done for Trx's N:o < 4659 undo N:o < 0 state:running but idle
History list Length 138
LIST of transactions for each SESSION:
---TRANSACTION 0, not started
MySQL thread ID 9, OS thread handle 0x7f813c5f7700, query ID--localhost root init
Show Engine InnoDB Status
---TRANSACTION 4663, ACTIVE sec starting index Read
MySQL tables in use 1, locked 1
Lock WAIT 2 lock struct (s), heap size 1 row lock (s)
MySQL thread ID 4, OS thread handle 0x7f813c628700, query ID 149 localhost root updating
Delete from EMP where empno=7788
-------TRX has BEEN waiting-SEC for this LOCK to be granted: # # wait 78s
RECORD LOCKS Space ID-page No 3 n bits the index ' PRIMARY ' of table ' test '. ' EMP ' TRX ID 4663 lock_mode X LOCKS rec But n OT Gap waiting
Record Lock, Heap No 9 physical record:n_fields 10; Compact format; Info bits 0 # # Thread 4 waits for an X lock on the primary key in Test.emp, page num=3
0:len 4; Hex 80001e6c; ASC L;;
1:len 6; Hex 000000001018; ASC;;
2:len 7; Hex 91000001420084; ASC B;;
3:len 5; Hex 53434f5454; ASC SCOTT;;
4:len 7; Hex 414e414c595354; ASC ANALYST;;
5:len 4; Hex 80001d8e; ASC;;
6:len 4; Hex 208794f0; ASC;;
7:len 4; Hex 80000bb8; ASC;;
8:sql NULL;
9:len 4; Hex 80000014; ASC;;

------------------
Table lock table ' test '. ' EMP ' TRX ID 4663 Lock Mode IX # # before adding X locks to the primary key line, first add intent lock IX on the table
RECORD LOCKS Space ID-page No 3 n bits the index ' PRIMARY ' of table ' test '. ' EMP ' TRX ID 4663 lock_mode X LOCKS rec But n OT Gap waiting
Record Lock, Heap No 9 physical record:n_fields 10; Compact format; Info bits 0
0:len 4; Hex 80001e6c; ASC L;;
1:len 6; Hex 000000001018; ASC;;
2:len 7; Hex 91000001420084; ASC B;;
3:len 5; Hex 53434f5454; ASC SCOTT;;
4:len 7; Hex 414e414c595354; ASC ANALYST;;
5:len 4; Hex 80001d8e; ASC;;
6:len 4; Hex 208794f0; ASC;;
7:len 4; Hex 80000bb8; ASC;;
8:sql NULL;
9:len 4; Hex 80000014; ASC;;

---TRANSACTION 4662, ACTIVE bayi sec starting index Read
MySQL tables in use 1, locked 1
Lock WAIT 2 lock struct (s), heap size 1 row lock (s)
MySQL thread ID 7, OS thread handle 0x7f813c5c6700, query ID 148 localhost root updating
Update emp set sal=3500 where empno=7788
-------TRX has BEEN waiting Bayi SEC for this LOCK to be granted: # # wait 81s
RECORD LOCKS Space ID-page No 3 n bits the index ' PRIMARY ' of table ' test '. ' EMP ' TRX ID 4662 lock_mode X LOCKS rec But n OT Gap waiting
Record Lock, Heap No 9 physical record:n_fields 10; Compact format; Info bits 0 # # Thread 7 waits for an X lock on the primary key in Test.emp, page num=3
0:len 4; Hex 80001e6c; ASC L;;
1:len 6; Hex 000000001018; ASC;;
2:len 7; Hex 91000001420084; ASC B;;
3:len 5; Hex 53434f5454; ASC SCOTT;;
4:len 7; Hex 414e414c595354; ASC ANALYST;;
5:len 4; Hex 80001d8e; ASC;;
6:len 4; Hex 208794f0; ASC;;
7:len 4; Hex 80000bb8; ASC;;
8:sql NULL;
9:len 4; Hex 80000014; ASC;;

------------------
Table lock table ' test '. ' EMP ' TRX ID 4662 lock Mode IX # # before adding X locks to the primary key line, first add intent lock IX on the table
RECORD LOCKS Space ID-page No 3 n bits the index ' PRIMARY ' of table ' test '. ' EMP ' TRX ID 4662 lock_mode X LOCKS rec But n OT Gap waiting
Record Lock, Heap No 9 physical record:n_fields 10; Compact format; Info bits 0
0:len 4; Hex 80001e6c; ASC L;;
1:len 6; Hex 000000001018; ASC;;
2:len 7; Hex 91000001420084; ASC B;;
3:len 5; Hex 53434f5454; ASC SCOTT;;
4:len 7; Hex 414e414c595354; ASC ANALYST;;
5:len 4; Hex 80001d8e; ASC;;
6:len 4; Hex 208794f0; ASC;;
7:len 4; Hex 80000bb8; ASC;;
8:sql NULL;
9:len 4; Hex 80000014; ASC;;

---TRANSACTION 4615, ACTIVE 1579 sec, thread declared inside InnoDB 1222
MySQL tables in use 2, locked 0
2 lock struct (s), heap size, 1 row lock (s)
MySQL thread ID 3, OS thread handle 0x7f813c659700, query ID 147 localhost root sending data
Select COUNT (*) from T3 a,t3 B # # This is the SQL that thread 3 is currently executing
Trx read view won't see Trx with ID >= 4662, sees < 4659
Table lock table ' test '. ' EMP ' TRX ID 4615 lock Mode IX # # thread 3 is owning the intent IX lock on the table, and there is a row-level X lock on the primary key on the Test.emp table, page num=3
RECORD LOCKS Space ID-page No 3 n bits the index ' PRIMARY ' of table ' test '. ' EMP ' TRX ID 4615 lock_mode X LOCKS rec But n OT Gap
Record Lock, Heap No 9 physical record:n_fields 10; Compact format; Info bits 0
0:len 4; Hex 80001e6c; ASC L;;
1:len 6; Hex 000000001018; ASC;;
2:len 7; Hex 91000001420084; ASC B;;
3:len 5; Hex 53434f5454; ASC SCOTT;;
4:len 7; Hex 414e414c595354; ASC ANALYST;;
5:len 4; Hex 80001d8e; ASC;;
6:len 4; Hex 208794f0; ASC;;
7:len 4; Hex 80000bb8; ASC;;
8:sql NULL;
9:len 4; Hex 80000014; ASC;;

Why is thread 3 currently performing a select T3 table operation, but locking the page num=3 on the test.emp table?
It is possible that the operation of the Test.emp table before thread 3 was not committed in a timely manner.
So it concludes that thread 3 is blocking thread 7, and thread 7 is blocking thread 4, so the root cause is thread 3, so that thread 3 commits as soon as possible or kill.

Conclusion
In the analysis of InnoDB lock blocking, the comparison of several methods:

(1) Use show processlist to view the non-reliable;
(2) Direct use of Show engine InnoDB status View, unable to determine the root cause of the problem;
(3) Using Mysqladmin Debug view, can see all the threads that generate the lock, but can not determine which is the root cause;
(4) After opening innodb_lock_monitor, then use show engine InnoDB status View, can find the root cause of lock blocking.

Note: The article originates from http://blog.csdn.net/hw_libo/article/details/39080809

Database Transaction "Isolation Level"