2014/7/27 ------ database isolation, 201427 ------

2014/7/27 ------ database isolation, 201427 ------

Demonstrate concurrency issues at different isolation levels

1. When the isolation level of the transaction is set to read uncommitted, dirty reads will be triggered, and repeated and virtual reads will not be allowed.

Window

Start transaction isolation level read uncommitted;

Start transaction;

Select * from account;

======== At this time, I found that the aaa account is 1000 yuan and transferred to the B window

Select * from acount where name = 'aaa ';

======== It is found that the aaa account has an additional 100 yuan. At this time, the data read by window a is not submitted by window B (dirty read );

Window B

Start transaction; (if you do not write a commit statement, the data will not be submitted to the database)

Update account set money = money + 100 where name = 'aaa ';

------ Do not submit, go to window A for query

2. When the transaction isolation level is set to read committed, non-repeated reads and virtual reads are triggered, but dirty reads are avoided;

Window

Start transaction isolation level read committed;

Start transaction;

Select * from account;

======== At this time, I found that the aaa account is 1000 yuan and transferred to the B window

Select * from acount where name = 'aaa ';

======== It is found that the aaa account has an additional 100 yuan. At this time, window a reads the data submitted by other transactions, the data read twice is different (repeated reading is not allowed );

 

Window B

Start transaction;

Update account set money = money + 100 where name = 'aaa ';

Commit;

----- Go to window

3. When the transaction isolation level is set to repeatable read (default mysql level), virtual reads are triggered, but dirty reads are avoided and repeated reads are not allowed;

Window:

Set transaction isolation level repeatable read;

Start transaction;

Select * from account;

======== Four records are found and transferred to window B

Select * from account;

======== Five records may be found. At this time, a reads the data inserted to another transaction (Virtual read)

Window B

Start transaction;

Insert into acount (name, money) values ('ggg', 1000 );

Commit;

------- Go to window

4. When the transaction isolation level is set to Serializable, all problems will be avoided;

Window:

Set transaction isolation level repeatable read;

Start transaction;

Select * from account;

======== Four records are found and transferred to window B

Window B

Start transaction;

Insert into acount (name, money) values ('ggg', 1000 );

------- The transaction cannot be inserted until a ends.

Database isolation level

The value of Step 4 is 0. Step 6 is 100. Although the value is updated in step 3, It is not submitted and has not been written to the database. Therefore, the value queried in step 3 is still the original value of the database. Step 5: after a submission, the value is written to the database and queried as the updated value. If multiple threads are used, dirty reading may occur. Since there is a submission, it depends on the priority of the thread. If the priority level is the same, you have to be lucky. All in all, if another thread executes the query after the update is submitted, the updated value will be investigated. If only the update is executed and the query is executed by the other thread, dirty reads will occur.

Isolation level of basic database events

This should be related to different databases and different lock level settings. There is no relevant information and no specific results can be provided.