2014/7/27 the isolation of------database

Demonstrate concurrency issues at different isolation levels

1. When the isolation level of a transaction is set to read UNCOMMITTED, dirty reads are thrown. Do not read repeatedly. Virtual Read

A form

Set TRANSACTION isolation level READ UNCOMMITTED;

Start transaction;

SELECT * from Account;

======= this time found AAA's account is 1000 yuan, go to B form

SELECT * from acount where name== ' AAA ';

======= found that AAA accounts for more than 100 yuan. This time a form reads the data when B forms uncommitted data (dirty read);

B Form

Start transaction; (there is this sentence.) Do not write commit statement, data is not committed to the database)

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

------do not submit. Go to a form query

2. When the isolation level of a transaction is set to read committed, it will throw non-repeatable read, false read, but can avoid dirty reading;

A form

Set TRANSACTION ISOLATION level Read Committed;

Start transaction;

SELECT * from Account;

======= this time found AAA's account is 1000 yuan. Go to form B

SELECT * from acount where name== ' AAA ';

======= found that AAA accounts for more than 100 yuan. At this time a form read the data submitted by other transactions, two read to a different data (not repeated reading);

B Form

Start transaction;

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

Commit

-----go to a form

3. When the isolation level of a transaction is set to repeatable read (MySQL default level), a virtual read is thrown, but the dirty reads are avoided and cannot be read repeatedly.

A form:

Set TRANSACTION ISOLATION level repeatable read;

Start transaction;

SELECT * from Account;

======= found 4 records, go to form B

SELECT * from Account;

======== may have found 5 records, when a read to another transaction inserted data (virtual Read)

B Form

Start transaction;

Insert into acount (Name,money) VALUES (' GGG ', 1000);

Commit

-------go to a form

4. When the isolation level of a transaction is set to serializable, all problems are avoided.

A form:

Set TRANSACTION ISOLATION level repeatable read;

Start transaction;

SELECT * from Account;

======= found 4 records, go to form B

B Form

Start transaction;

Insert into acount (Name,money) VALUES (' GGG ', 1000);

-------found unable to insert. Can only wait for a to end a transaction ability to insert

2014/7/27 the isolation of------database