ReadUnCommitted and ReadCommitted

Reproduced to: http://www.cnblogs.com/zhenyulu/articles/330494.html

ReadUnCommitted is the lowest isolation level. This level of isolation allows reading dirty data that has not been committed by others. In addition, there is still a problem of non-repeated read at this level of transaction isolation.

ReadCommitted is the default level for many databases. At this isolation level, there will be no issues with reading uncommitted data, but it still cannot avoid repeated reads (including Phantom reads. When your system has strict requirements on concurrency control, this default isolation level may not provide effective data protection, but this isolation level is sufficient for most applications.

We use the following experiment for testing:

First, configure the SQL Server 2000 database and add the DBApp database. Create a Management Console Application in Visual Studio. net and add necessary namespace references:

using System;using System.Data;using System.Data.SqlClient;using System.Configuration;

Then create two database links and adopt different transaction isolation levels:

private static SqlConnection conn1;private static SqlConnection conn2;private static SqlTransaction tx1;private static SqlTransaction tx2;private static void Setup(){conn1 = new SqlConnection(connectionString);conn1.Open();tx1 = conn1.BeginTransaction(IsolationLevel.ReadUncommitted);conn2 = new SqlConnection(connectionString);conn2.Open();tx2 = conn2.BeginTransaction(IsolationLevel.ReadCommitted);}

Here, transaction 1 allows reading uncommitted data, while transaction 2 allows reading only committed data.

In the main program, we simulate different operations of two people to generate concurrency consistency problems:

public static void Main(){Setup();try{ReadUnCommittedDataByTransaction1();UnCommittedUpdateByTransaction2();ReadUnCommittedDataByTransaction1();tx2.Rollback();Console.WriteLine("\n-- Transaction 2 rollbacked!\n");ReadUnCommittedDataByTransaction1();tx1.Rollback();}catch{……}}

Step 1: Use the ReadUnCommittedDataByTransaction1 method to read the student information with id 1 from the database. At this time, the information is the initial information of the database.

Step 2: Call the UnCommittedUpdateByTransaction2 method and send an UPDATE command from the first transaction to UPDATE the database, but it has not been submitted yet.

Step 3: Call ReadUnCommittedDataByTransaction1 again to read database data from transaction 1. You will find that uncommitted Updates released by transaction 2 are read by transaction 1 (ReadUnCommitted ).

Step 4: Transaction 2 discard the commit and roll back the transaction tx2.Rollback ();.

Step 5: Call ReadUnCommittedDataByTransaction1 (); again to read the data in the database. This is the data that has been rolled back.

The program running result is as follows:

-- Read age from database:Age:20-- Run an uncommitted command:UPDATE student SET age=30 WHERE id=1-- Read age from database:Age:30-- Transaction 2 rollbacked!-- Read age from database:Age:20

The methods for ReadUnCommittedDataByTransaction1 () and UnCommittedUpdateByTransaction2 () are defined as follows:

Private static void UnCommittedUpdateByTransaction2 ()
{
String command = "UPDATE student SET age = 30 WHERE id = 1 ";
Console. WriteLine ("\ n -- Run an uncommitted command: \ n {0} \ n", command );
SqlCommand cmd = new SqlCommand (command, conn2 );
Cmd. Transaction = tx2;
Cmd. ExecuteNonQuery ();
}
Private static void ReadUnCommittedDataByTransaction1 ()
{
Console. WriteLine ("-- Read age from database :");
SqlCommand cmd = new SqlCommand ("SELECT age FROM student WHERE id = 1", conn1 );
Cmd. Transaction = tx1;
Try
{
Int age = (int) cmd. ExecuteScalar ();
Console. WriteLine ("Age: {0}", age );
}
Catch (SqlException e)
{
Console. WriteLine (e. Message );
}
}

From the experiment above, we can see that at the ReadUnCommitted isolation level, the program may read uncommitted data, but this isolation level has the least lock on database resources.

The complete code of this experiment can be found under "SampleCode \ Chapter 2 \ Lab 2-6.

Let's start another experiment (this experiment requires fast actions, otherwise it may not be able to see the expected results ). First, modify the Setup () method code in the above Code

tx1 = conn1.BeginTransaction(IsolationLevel.ReadUncommitted);

Changed:

tx1 = conn1.BeginTransaction(IsolationLevel.ReadCommitted);

When you run the code again, you will find that the program does not run until the third step. If you have enough patience to wait, you will see that "the timeout time has reached. The timeout has expired or the server has not responded before the operation is completed. "A prompt, what does this prompt mean? Let's look at what happened:

Step 1: Open the Enterprise Manager of SQL Server 2000 before doing this experiment, and then open the SQL Server event inspector.

Step 2: run the modified program. When the program is executed halfway, It is paused. Quickly switch to the Enterprise Manager interface, right-click "current activity" under "manage", and select "refresh" (the entire process should be completed in about 15 seconds, as shown in 2-8 ), we get a snapshot of the current process of the database.

Figure 2-8 use the Enterprise Manager to view the current activity

We found that the process was blocked at this time. The blocked process was the No. 52 process, and the blocked process was the No. 53 process. That is to say, the work of process 53 hinders the continued work of process 52. (Process numbers may be different in different experiments)

Step 3: to further identify the cause, we switch to the event Inspector window to see what the two processes are doing. From 2 to 9, the event Inspector displays the details of the two processes. We can see that process 52 corresponds to our transaction process 1 and process 53 corresponds to our transaction 2. Transaction 2 executes the update command but has not yet committed it. At this time, transaction 1 reads uncommitted data and is blocked. We can see that process 52 is blocked.

At this time, if transaction 2 is committed, process 52 can stop waiting to obtain the expected results. However, our program did not submit data, so process 52 would have to wait infinitely. Fortunately, SQL Server 2000 detects that transaction 2 has been running for too long (this is the error message above, "the timeout has reached. The timeout has expired or the server has not responded before the operation is completed. "), So roll back transaction 2 to release the occupied resources. After the resource is released, process 52 can be executed.

Figure 2-9 event Inspector detects blocking commands

Step 4: After learning about what happened above, we can discuss the usage of shared and exclusive locks in depth. Return to the Enterprise Manager page and check the resources occupied by each process. As shown in Figure 2-10, process 53 (transaction 2) adds the exclusive lock (x lock) to the corresponding key before executing the update command ), according to the Level 1 blocking protocol mentioned above, the exclusive lock is released only after transaction 2 is committed or rolled back. Now process 52 (transaction 1) needs to read the same row of data. According to the Level 2 blocking protocol, it must first apply a shared lock to the row. However, the row of data has been added with an exclusive lock by transaction 2, therefore, transaction 1 can only be in the waiting state, waiting for the exclusive lock to be released. Therefore, we can see the previous "blocking" problem.

Figure 2-10 the exclusive lock is applied before the process executes the write operation.

Figure 2-11 The read operation of a process requires a shared lock but is blocked.

When the transaction isolation level of transaction 1 is readuncommitted, the read data is not locked, so the exclusive lock does not work for readuncommitted, and the process is not blocked, but the read is indeed "dirty" data.