Sybase's Affairs

This article describes the impact of database transaction isolation levels on locks, and understands the relationship between lock and dirty reads by comparing transaction isolation levels 0 and 1.

Experiment content: Set 0 and Level 1 isolation levels, perform two different but related transactions. The experimental environment is as follows:

1, access to the user database (this is aca_database) and database tables have Modify and query permissions.

2, the database has a table auths, including salary column (data type is money), there is a certain amount of data (there are 10,000 lines).

Steps:

1, such as in the same machine, can be executed two times "SQL Advantage", respectively, with the legal account log in preparation for their respective implementation of a transaction.

2, open the user database Aca_database in the respective interface, set the isolation level to 1 (this is also the default value):

Set TRANSACTION ISOLATION Level 1 To view current isolation levels with SELECT @ @isolation

3, execute the first two sentences of the transaction, temporarily do not perform the subsequent commit or rollback.

BEGIN TRANSACTION

Update auths Set salary=salary+100

Commit TRANSACTION

(ROLLBACK TRANSACTION)

4. Perform a second transaction select SUM (Salary) from auths

5, the first transaction completes normally, but only in the memory buffer to complete the modification, the transaction does not really end, the corresponding does not release the lock, the second transaction is in the waiting state. Executable sp_lock and sp_who command view.

6. Perform the commit or ROLLBACK command in step 3rd and the second transaction is executed.

7, change the transaction isolation level to 0, repeat 3-4 steps. The first transaction does not change, and the second transaction comes out quickly. The result of modifying data based on the first transaction seems to be unaffected by the first transaction in executing the response.

8, repeat the end of the 6th step.

Through the experiments above we know:

1, Isolation Level 0 o'clock transactions allow dirty read, isolation Level 1 o'clock transactions do not allow dirty reads, but simply wait for the previous modification transaction to really end and release the lock.

2, the isolation level is 0 o'clock, if the transaction is modified at the end of the rollback operation, the query read transactions will not be able to read the correct data.

3. If you are confident that there is no rollback operation in the transaction and require higher execution efficiency and parallelism, then consider setting the isolation level to 0.