1. Transaction characteristics
Atomicity: Emphasizes the indivisibility of transactions
Consistency: Emphasizes that the integrity of the data must be consistent before and after the execution of the transaction
Isolation: the execution of a transaction Should not be interfered by other transactions
Persistence: Once the transaction ends (commit/rollback), the data is persisted to the database
Two. If isolation is not considered, some security will be triggered Sexual issues
Read issues
Dirty read: A transaction reads data that has not yet been committed by another transaction Data inconsistency in multiple queries in the current transaction
Virtual read/phantom read: One transaction reads that another transaction has inserted data, resulting in inconsistent query results in the current transaction.
Write problems
cause two types Lost update
Three. Solve the read problem caused by
Set the isolation level of the transaction
read uncommitted: read uncommitted. Dirty reads, non-repeatable reads, and virtual reads may occur.
read committed: Read committed. Avoid dirty reads, non-repeatable reads and wasted reads may occur
repeatable read: repeatable reads. Avoid dirty reads and non-repeatable reads, virtual reads may occur
serializable: serialized. Avoid dirty reads, non-repeatable reads, and false reads.
select @@tx_isolation; view the isolation level
set session transaction isolation level; set the isolation level
4. Demo Reading problems
Demonstration of dirty reading
Open two dos windows AB
First check the isolation level of the two windows select @@tx_isolation;
Set the isolation level of window A to uncommitted read set session transaction isolation level read uncommitted;
Start transaction start transaction in two windows separately;
Open two dos windows respectively AB
update account set money = money-1000 where name =’Zhang Sen’;
update account set money = money + 1000 where name =’Sister Feng’;
Query data in window A select * from account; – Transaction A has read data that has not yet been submitted by Transaction B;
Demonstration avoidance Dirty read, demonstrating non-repeatable read sending
Open two windows separately, AB
Set the isolation level of window A: read committed set session transaction isolation level read committed;
Start transaction start transaction in two windows separately ;
Complete the transfer in window B
update account set money = money-1000 where name =’张森’;
update account set money = money + 1000 where name =’Fengjie’;
In A window for query select from account; – avoid dirty reading
Submit transaction commit in window B;
Query select again in window A from account; – transfer is successful. (Do not read repeatedly : One transaction reads the submitted update data in another transaction, resulting in inconsistent query results.)
Avoid dirty reads and non-repeatable reads, and demonstrate virtual reads
Open two windows separately , AB
Set A window Isolation level: repeatable read set session transaction isolation level repeatable read;
Start transaction start transaction in two windows separately;
Complete the transfer operation in window B
update account set money = money-1000 where name =’Zhang Sen’;
update account set money = money + 1000 where name =’Fengjie’;
Query select from account; in window A;-the transfer was not successful: avoid dirty reading.
Submit the transaction commit in the B window;
Query the select again in the A window from account; – The transfer is not successful: avoid non-repeatable reading.
Avoid false readings
open the two separately Window, AB
set the isolation level of window A: repeatable read set session transaction isolation level repeatable read;
start transaction start transaction in two windows respectively;
insert into account in window B values (null,’Mr. Wang’,10000);
Query operation in A select * from account; – No results are found in the query
Submit the transaction in the B window commit; – The data will be displayed in the A window immediately
If you like this article, you can give the author a point of approval, pay attention to it, and share Java-related articles every day! There are also occasional welfare gifts, including organized study materials, interview questions, source code, etc.~~