Common concurrency concurrency Consistency issues include: missing changes, non-repeatable reads, read dirty data, Phantom reads (Phantom reads are often associated with non-repeatable reads in some materials).
2.2.1.1 Missing changesLet's take a look at an example that shows inconsistencies in the data caused by concurrent operations.
Consider an activity sequence in the aircraft booking system:
As a result, two tickets were sold, and the balance in the database was reduced by only 1.
Summed up is: Two transactions T1 and T2 read into the same data and modify, T2 submitted results destroy the results of the T1 submitted, resulting in T1 modification is lost. The problems and workarounds mentioned in the previous article (2.1.4 Data deletion and update) are often addressed to such concurrency problems. But there are still several types of problems that cannot be solved by the above method:
2.2.1.2 Non-repeatable READNon-repeatable read refers to the transaction T1 after the data is read, T2 performs the update operation, so that T1 cannot reproduce the previous read result. Specifically, non-repeatable reading consists of three cases:
Read "Dirty" data refers to the transaction T1 modify a certain data, and write it back to disk, transaction T2 read the same data, T1 for some reason was revoked, at this time T1 modified data Recovery original value, T2 read the data is inconsistent with the data in the database, then T2 read the data is "dirty" data, that is, incorrect data.
The main reason for the above three types of data inconsistency is that concurrent operations undermine the isolation of transactions. Concurrency control is the correct way to dispatch concurrent operations, so that the execution of a user's transaction is not affected by other transactions, so as to avoid the inconsistency of data.
2.2.2 Solutions to concurrency consistency problems 2.2.2.1 blockade (Locking)Blocking is a very important technique for implementing concurrency control. The so-called blockade is a transaction t before a data object such as tables, records, and so on, before the system to make a request to lock it. After locking, the transaction T has some control over the data object, and other transactions cannot update the data object until the transaction T releases its lock.
There are two basic types of blocking: exclusive locks (Exclusive locks précis-writers for x locks) and shared locks (Share locks précis-writers for s locks).
Exclusive lock is also called write lock. If the transaction t adds an X lock to the data object A, only T reads and modifies a, and no other transaction can add any type of lock to a, until T releases the lock on A. This guarantees that the other transaction can no longer read and modify a when T releases the lock on A.
Shared locks are also known as read locks. If the transaction t adds the S lock to the data object A, the other transaction can only be locked to a plus s, not the x lock, until T releases the S lock on a. This guarantees that the other transaction can read a, but cannot make any modifications to a when T releases the S lock on a.
2.2.2.2 Blockade ProtocolWhen using the two basic blocks of X-Lock and S-lock, it is also necessary to stipulate some rules when locking data objects, such as when to apply for X-lock or S-lock, lock-in time, when to release, etc. We call these rules the blockade agreement (Locking Protocol). The different rules governing the way in which closures are imposed have created different kinds of blockade agreements. The three-level lockdown protocol is described below. The level three blocking protocol solves the inconsistency problem of lost modification, non-repeatable reading and reading "dirty" data in different degree, and provides certain guarantee for the correct scheduling of concurrent operation. The following is only a definition of level three blockade protocol, no more discussion.
The 1-level blocking protocol is that transaction T must be X-locked before the data R is modified until the end of the transaction is released. The end of the transaction consists of a normal end (COMMIT) and an abnormal end (ROLLBACK). A Level 1 blocking protocol prevents loss of modification and guarantees that transaction T is recoverable. In a Level 1 blocking protocol, if only the read data does not modify it, it does not need to be locked, so it does not guarantee repeatable read and not read "dirty" data.
Level 2 Blocking protocol is: Level 1 blocking protocol plus transaction T before reading the data r must be added to the S lock, after reading can release S lock. The Level 2 blocking protocol prevents the loss of modifications and further prevents the reading of "dirty" data.
Level 3 Blocking protocol is: Level 1 blocking protocol plus transaction T before reading the data R must first lock it, until the end of the transaction is released. The Level 3 blocking protocol prevents non-repeatable reads, in addition to preventing loss of modification and non-reading of ' dirty ' data.
2.2.3 Transaction ISOLATION LEVELAlthough database theory provides a perfect solution to concurrency consistency, it is very difficult for programmers to control the timing of Hega, locking and unlocking. The vast majority of databases and development tools provide transaction isolation levels that allow users to handle concurrency consistency issues in an easier way. Common transaction isolation levels include: ReadUncommitted, ReadCommitted, RepeatableRead, and serializable four. The way the database is accessed under different isolation levels and the results of the database return may be different. We'll go through several experiments to learn more about the transaction isolation level and how SQL Server transforms them into locks in the background.
2.2.3.1 readuncommitted and ReadCommittedReadUncommitted is the lowest level of isolation, which allows reading of dirty data that has not yet been committed by others, and in addition to this, there is an issue of non-repeatable reads at this level of transaction isolation.
ReadCommitted is the default level for many databases, and at this isolation level, there is no problem of reading uncommitted data, but it is still not possible to avoid the problem of non-repeatable reads (including Phantom reads). This default isolation level may not provide data-efficient protection when your system is demanding concurrency control, but this isolation level is sufficient for most applications.
We use the following experiment to test:
First configure the SQL Server 2000 database, attaching the Dbapp database. Then create a management Console application in Visual Studio. NET and add the necessary namespace references:
Using system;using system.data;using system.data.sqlclient;using system.configuration;
Then set up two database links and adopt different transaction isolation levels, respectively:
private static SqlConnection conn1;private static SqlConnection conn2;private static sqltransaction tx1;private static Sq Ltransaction 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);}Where transaction 1 allows read-in uncommitted data, and transaction 2 only allows read-committed data.
In the main program, we simulate two different operations to produce concurrency consistency issues:
public static void Main () {Setup (); Try{readuncommitteddatabytransaction1 (); UnCommittedUpdateByTransaction2 (); ReadUnCommittedDataByTransaction1 (); tx2. Rollback (); Console.WriteLine ("\n--Transaction 2 rollbacked!\n"); ReadUnCommittedDataByTransaction1 (); tx1. Rollback ();} Catch{...}The first step is to use the ReadUnCommittedDataByTransaction1 method to read the student information from the database with ID value 1 using transaction 1. The information at this point is the initial information for the database.
The second step, call the UnCommittedUpdateByTransaction2 method, updates the database with an update command sent from the 2nd transaction but has not yet been committed.
Third, call ReadUnCommittedDataByTransaction1 again, read the database data from transaction 1, and you will find that the uncommitted updates released by transaction 2 are read by transaction 1 (readuncommitted).
Fourth, transaction 2 discards the commit and rolls back the transaction tx2. Rollback ();.
The fifth step is to call ReadUnCommittedDataByTransaction1 () again, and read the data in the database, which is the data that has been rolled back.
The results of the program run as follows:
--read age from database:age:20--Run a uncommitted command:update student SET age=30 WHERE id=1--read age from Databas e:age:30--Transaction 2 rollbacked!--Read 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);}}As can be seen from the experiment above, under the ReadUncommitted isolation level, the program may read uncommitted data, but this isolation level locks the database resource to a minimum.
The complete code for this experiment can be found under "Samplecode\chapter 2\lab 2-6".
Let's do another experiment (the experiment requires that the action be fast, otherwise you may not see the desired effect). First modify the Setup () method code in the above code to
tx1 = conn1.BeginTransaction(IsolationLevel.ReadUncommitted);
Switch
tx1 = conn1.BeginTransaction(IsolationLevel.ReadCommitted);
Run the code again and you'll notice that the program does not move until the third step, and if you have enough patience to wait, you'll see "The timeout is up." The time-out elapses before the operation completes or the server is not responding. "A hint, what exactly does this hint mean?" Let's look at what happened:
The first step is to open the SQL Server 2000 Enterprise Manager before you do this experiment, and then open the SQL Server Event Explorer and be in the exploratory state.
In the second step, after running the modified program, the program executes in half and pauses. At this point, quickly switch to the Enterprise Manager interface, right-click "Manage" under "Current Activity", select "Refresh" (the whole process should be completed in about 15 seconds, 2-8), we get a snapshot of the current process of the database.
Figure 2-8 using Enterprise Manager to view the current activity
We found that the process was blocked at this time, the blocked person is process 52nd, and the block is process number 53rd. In other words, the work of process 53rd hampered the continuation of process 52nd. (process numbers may vary by experiment)
Third, to further pinpoint the cause, we switch to the Event Explorer window to see what the two processes are doing. As shown in 2-9, the event Explorer displays detailed information about both processes. As we can see, process number 52nd corresponds to our transaction 1, process 53rd corresponds to our transaction 2. Transaction 2 executes the update command but has not yet committed, at which point the transaction 1 reads the data that has not yet been committed and is blocked. We can see that the number 52nd process is a blocked person.
At this point, if transaction 2 completes the commit, process number 52nd can stop waiting and get the desired result. However, our program did not submit the data, so the process of No. 52nd will wait indefinitely. Fortunately, SQL Server 2000 detected that transaction 2 was running too long (this is the above error message "The timeout period has elapsed.") The time-out elapses before the operation completes or the server is not responding. "), the transaction 2 is rolled back to release the resource being consumed. After the release of the resources, process number 52nd was implemented.
Figure 2-9 Event Explorer probe blocking command
Fourth step, after understanding what happened above, we can now discuss the use of shared and exclusive locks in depth. Back to the Enterprise Manager interface, let's look at what resources are being consumed by each of the two processes. As we can see from figure 2-10, process 53rd (transaction 2) adds an exclusive lock (x Lock) to the corresponding key before executing the update command, and the exclusive lock is released only after transaction 2 has been committed or rolled back, as described in the previous section of the 1 blocking protocol. Now the 52nd process (transaction 1) to read the same row of data, in accordance with the Level 2 blocking protocol, it will first add a shared lock on the row, but the row of data has been added to Transaction 2 lock, so transaction 1 can only be in the waiting state, waiting for the exclusive lock is released. So we see the "blocking" problem in front of us.
Figure 2-10 The process performs a write operation by first adding an exclusive lock
Figure 2-11 A shared lock is added before the process read operation, 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 "dirty" data is actually read.
2.2.3.2 RepeatableReadRepeatableRead refers to repeatable reads, which have a higher isolation level than the readcommitted level. It allows a transaction to perform repeated reads while the data remains intact, but still does not solve the phantom read problem. To gain a deeper understanding of the problems that repeatableread can solve, we use the following experiments to verify:
In the first step, transaction 1 is set to readcommitted at the same time as transaction 2, and the transaction is turned on.
private static void Setup () {conn1 = new SqlConnection (connectionString); conn1. Open (); tx1 = Conn1. BeginTransaction (isolationlevel.readcommitted); conn2 = new SqlConnection (connectionString); conn2. Open (); tx2 = Conn2. BeginTransaction (isolationlevel.readcommitted);}In the second step, transaction 1 reads the data from the database. Note that at this point there is no way to end transaction 1 by committing or rolling back, and transaction 1 is still active.
private static int ReadAgeByTransaction1 () {return (int) executescalar ("Select Age from student WHERE (id = 1)");} Private static Object ExecuteScalar (String command) {Console.WriteLine ("--Execute command: {0}", command); SqlCommand cmd = new SqlCommand (command, conn1); cmd. Transaction = Tx1;return cmd. ExecuteScalar ();}In the third step, transaction 2 modifies the age data and submits the changes.
private static void ModifyAgeByTransaction2 () {string command = "UPDATE student SET age=30 WHERE id=1"; Console.WriteLine ("--Modify Age by Transaction2, command:{0}", command); SqlCommand cmd = new SqlCommand (command, conn2); cmd. Transaction = Tx2;try{cmd. ExecuteNonQuery (); Tx2.commit ();} catch (Exception e) {Console.WriteLine (e.message); tx2. Rollback ();}}Fourth, transaction 1 read the age data repeatedly, you will find that the data read is the modified data, and the last read the data is not the same! As the name implies, cannot be read repeatedly. The main program code is as follows:
public static void Main () {Setup (); Try{int age1 = ReadAgeByTransaction1 (); ModifyAgeByTransaction2 (); int age2 = ReadAgeByTransaction1 (); Console.WriteLine ("\nfirst Read:age={0}\nsecond read:age={1}", Age1, Age2);} catch (Exception e) {Console.WriteLine ("Got an error!" + e.message);} Finally{cleanup ();}}The running results of the program are as follows:
--Execute command:select age from student WHERE (id = 1)--Modify Age by Transaction2, command:update student SET age=30 where id=1--Execute command:select age from student WHERE (id = 1) First Read:age=20second read:age=30
The data read on repeated reads is different from the first read because transaction 1 is set to the ReadCommitted isolation type, which does not prevent the problem of non-repeatable reads. The RepeatableRead transaction isolation level must be used in order to have the same data read two times in a transaction.
Let's modify the code in the Setup () method above to set the isolation level of transaction 1 to RepeatableRead:
TX1 = conn1. BeginTransaction (Isolationlevel.repeatableread);
Run the program again and you'll notice that the program is paused in the second step, and if you wait for a while you'll see "The timeout is up." The time-out elapses before the operation completes or the server is not responding. "Error, at this point, the repeated reading of the data is exactly the same as the first read. The results of the program execution are as follows:
--Execute command:select age from student WHERE (id = 1)--Modify Age by Transaction2, command:update student SET age=30 Where id=1 time-out has arrived. The time-out elapses before the operation completes or the server is not responding. --Execute command:select age from student WHERE (id = 1) First Read:age=20second read:age=20
To find out why, or as in the previous case, execute the program again, switch to Enterprise Manager to view the currently active snapshot in the event of a pause, and check for data locking in the blocking process, and you will see 2-12 and figure 2-13:
Figure 2-12 RepeatableRead to read the data with S lock until the end of the transaction is released
Figure 2-13 Modifying data requirements plus x lock, but blocked
According to the 3-level blocking protocol, transaction T must have a lock on the data before it is read, until the end of the transaction is released. Therefore, transaction 1 in the first reading of the data with a shared lock, the first time the data read completed after the transaction does not end, so the shared lock is not released, the transaction 2 attempts to modify the data, according to the Level 2 blocking protocol, before writing to the lock, but the data shared lock has not been released, Causes transaction 2 to be in a wait state. When the transaction 2 wait time expires, SQL Server forces the transaction to be rolled back. Although transaction 2 failed to execute, transaction 1 is guaranteed to have a repeatable read level of transactional isolation.
The RepeatableRead transaction isolation level allows for repeated reads within a transaction, but this does not prevent phantom reads, and if there is a potential problem with Phantom reads in your program, you must use the highest transaction isolation level: Serializable.
2.2.3.3 SerializableThe Serializable isolation level is the highest transaction isolation level, where the problem of reading dirty data, non-repeatable reads, and Phantom reads does not occur at this isolation level. Before detailing why first let's look at what Phantom reads.
The so-called Phantom reading means: Transaction 1 After some data records are read from the database by certain conditions, transaction 2 inserts a number of new records that conform to the transaction 1 retrieval criteria, and when transaction 1 reads the data again in the same condition, a few more records are found. Let's reproduce the question of phantom reading in the following cases:
In the first step, both transaction 1 and transaction 2 are set to the RepeatableRead isolation level and the transaction is turned on.
private static void Setup () {conn1 = new SqlConnection (connectionString); conn1. Open (); tx1 = Conn1. BeginTransaction (isolationlevel.repeatableread); conn2 = new SqlConnection (connectionString); conn2. Open (); tx2 = Conn2. BeginTransaction (Isolationlevel.repeatableread);}In the second step, transaction 1 reads the average score of the student with the number 1 and the number of the courses that are taught. At this time, students 1 learned 3 courses, with an average score of 73.67. Note that transaction 1 is not committed at this time.
private static Double ReadAverageMarksByTransaction1 () {return (double) executescalar ("Select AVG (Mark) as Avgmark from SC WHERE (id = 1) ");} private static int ReadTotalCoursesByTransaction1 () {return (int) executescalar ("Select COUNT (*) as Num from SC WHERE (id = 1) ");} Private static Object ExecuteScalar (String command) {Console.WriteLine ("--Execute command: {0}", command); SqlCommand cmd = new SqlCommand (command, conn1); cmd. Transaction = Tx1;return cmd. ExecuteScalar ();}In the third step, transaction 2 inserts a new record into the database so that the student with the number 1 learns 1 courses and the result is 80. Then commit the modifications to the database.
private static void InsertRecordByTransaction2 () {string command = "INSERT into SC VALUES (1, 5, 80)"; Console.WriteLine ("--Insert to table SC by transaction 2"); Console.WriteLine ("-command:{0}\n", Command); SqlCommand cmd = new SqlCommand (command, conn2); cmd. Transaction = Tx2;try{cmd. ExecuteNonQuery (); Tx2.commit ();} catch (Exception e) {Console.WriteLine (e.message); tx2. Rollback ();}}Fourth, transaction 1 reads the average grade of the student with the number 1 and the number of the courses studied. At this point, the readings are 4 courses, with an average of 75.25. Not the same as the first read! Even more of a course, the extra course is like a phantom in front of us. The main program for testing is as follows:
public static void Main () {Setup (); Try{console.writeline (">>>> Step 1");d ouble avg = ReadAverageMarksByTransaction1 (); int total = ReadTotalCoursesByTransaction1 (); Console.WriteLine ("Avg={0,5:f2}, total={1}\n", AVG, total); Console.WriteLine (">>>> Step 2"); InsertRecordByTransaction2 (); Console.WriteLine (">>>> Step 3"); avg = ReadAverageMarksByTransaction1 (); total = ReadTotalCoursesByTransaction1 (); Console.WriteLine ("Avg={0,5:f2}, total={1}\n", AVG, total);} catch (Exception e) {Console.WriteLine ("Got an error!" + e.message);} Finally{cleanup ();}}The results of the program execution are as follows:
>>>> Step Execute Command:select AVG (Mark) as Avgmark from SC WHERE (id = 1)--Execute Command:select CO UNT (*) as Num from SC WHERE (id = 1) avg=73.67, total=3>>>> Step 2--Insert to table SC by transaction 2--Comm And:insert into SC VALUES (1, 5, $) >>>> Step 3--Execute command:select AVG (Mark) as Avgmark from SC WHERE (i D = 1)--Execute command:select COUNT (*) as Num from SC WHERE (id = 1) avg=75.25, total=4
You can think about why the RepeatableRead isolation mode doesn't make the average of two reads the same? (This phenomenon can be explained from the angle of the lock).
Still, as we have done before, let's see what is going on. Before exploring, set the isolation level of transaction 1 in the Setup method to serializable, run the program again, and when the discovery program runs paused, look at the current active snapshot of the database, and you will find the locking problem shown in 2-14 and figure 2-15:
Figure 2-14 Serializable isolation mode adds a ranges-s lock to data that meets the search criteria
Figure 2-15 When you try to insert a record that meets the Rangein criteria, you can only be in the waiting state
We can see that in Serializalbe isolation mode, when the database is retrieving data, the Ranges-s shared lock is added to all records that satisfy the search criteria. Transaction 2 when attempting to insert a record that satisfies the Rangein condition, you must wait for these rangs-s locks to be released, otherwise you will only be in the waiting state. After the wait timeout, transaction 2 is forcibly rolled back by SQL Server.
The results of the modified program run as follows:
>>>> Step Execute Command:select AVG (Mark) as Avgmark from SC WHERE (id = 1)--Execute Command:select CO UNT (*) as Num from SC WHERE (id = 1) avg=73.67, total=3>>>> Step 2--Insert to table SC by transaction 2--Comm And:insert into SC VALUES (1, 5, 80) expired time expired. The time-out elapses before the operation completes or the server is not responding. >>>> Step 3--Execute command:select AVG (Mark) as Avgmark from SC WHERE (id = 1)--Execute Command:select CO UNT (*) as Num from SC WHERE (id = 1) avg=73.67, total=3
A failure to run transaction 2 ensures that transaction 1 does not occur with Phantom reads. It should be noted here that the 1-, 2-, 3-level blockade protocol does not guarantee effective resolution of phantom reads.
2.3 RecommendationsIn a few examples above, we have a deeper understanding of the measures the database takes to address concurrency consistency issues. Locking mechanism belongs to the lowest guarantee mechanism, but it is difficult to use directly. We can use the locking mechanism indirectly to ensure the complete consistency of our data through different transaction isolation modes. When using different levels of isolation mode, we should also be aware of the following issues:
Consistency issues for concurrency operations
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
