What is concurrency? Suppose there is such a book, customers can drink tea and study there. The customer registers at the counter with the selected books, finds a place to read, and returns the books to the store as soon as they leave. One day, a customer was about to register a book, and another customer's hand grabbed the only book, And the concurrency problem occurred. The two customers had to read the same book, so they did not interact with each other. This gave the shopkeepers a headache. This case is only a small part of many concurrency problems, but we can see that the concurrency problem mainly occurs when multiple users access limited resources, if the problem is not solved, the system will be directly affected to be effective and normal. The database is a shared resource, and the emergence of concurrency problems is inevitable. How to identify and control the concurrency type is the focus of this chapter.
This chapter will be divided into two parts: the concurrency control mechanism in Visual Foxpro. The concurrency control in VFP is relatively simple, and the Data Locking method is relatively simple. It is very suitable as a starting point for preliminary understanding of concurrency issues. The second part takes SQL Server 2000, ADO. net, and C # as the main tools to gain an in-depth understanding of concurrency consistency issues, blocking protocols, transaction isolation, and other content, which is more difficult. For more in-depth concurrency control measures, such as multi-granularity blocking and image lock, this chapter will not discuss them in depth. If you are interested, refer to relevant books.
2.1 concurrency control mechanism 2.1.1 exclusive access in Visual FoxproIs there a solution to the problem raised above? Of course. One way is to "exclusively access"-only one of your book bars can read books at any time. He can select favorite books and never worry about who will compete for resources with him. After reading the book, the next customer can come in, of course, only one at a time. You may laugh and say which seller is so stupid. In fact, if your bookstore only has two or three customers a day, you can do this :.
If you map a bookstore to a table in the database and each book corresponds to a record in the table, you can apply "exclusive access" to the database table. Once a table is exclusive, other users cannot access the table unless the exclusive table actively releases the resource. We can use Visual FoxPro to simulate database behavior in the exclusive and shared modes. Please complete [Experiment 2-1 using Visual FoxPro exclusively (shared) Access Table ].
Experiment 2-1 Use Visual Foxpro for exclusive (shared) Table Access
Exclusive Access ensures that no concurrency issues occur, but seriously affects the database efficiency. I don't think a merchant is willing to have only one customer patronize His store at a time. However, in the real world, there are still examples of "exclusive access. A few months ago, my wife was hospitalized with children. During daily ward rounds and bathing for babies in the hospital, to prevent accidents (stealing children), she had to lock the whole ward and leave only one bed with her. If a family member wants to go in, well, the bed companion must come out, and only one person can be in it at any time. This is a typical "exclusive access ". In addition, things like "monitoring" must also be "exclusive.
2.1.2 data lockOur shopkeepers are not interested in the "exclusive mode". After all, buying and selling are more important. It is unacceptable to allow only one customer to access them at a time. Is there any better way? The man may say: Install a responder. Whoever presses the book first will give it. Although the idea of the server guard is somewhat bizarre, it is okay to change it. Our store quickly came up with a very feasible solution with clever brains:
First, the store creates a set of labels, each of which has a name, one-to-one correspondence between books and labels. The book club also opens a new window in front of the counter to issue these labels. The store gave these tags a name called "Lock", and the window for issuing "Lock" is called "Lock ". In this way, when the customer queries the books to be read, he must first go to the "lock" Queue (first come first served) to get the "Lock" corresponding to the book ". Under normal circumstances, the customer can get the lock, and then go to "book reception" to register, "Book reception" to give the corresponding book to the customer for reading and leave the customer's "Lock ". After reading the book, the customer returns the book, and the "Book retrieval" is responsible for returning the corresponding "Lock" to the "Lock retrieval" for the next release. Of course, if both of them want to read the same book, they will see who is in front of the queue, and the customers at the back won't be able to get the book without the lock. At this time, the customer has two options: one is to give up reading; the other is to wait for the previous customer to return the book, and then return the lock to the book reception ". 2-1.
Figure 2-1 book club operation mode (pessimistic buffer Mode)
In this way, the problems caused by "exclusive" access are eliminated. A large number of customers can select books at the same time. They can only "lock the lock" in the queue when they are sure they want to read the books. This avoids the problem of allowing only one customer to access the books exclusively. The "Lock" acts as a "token" in it, and is only authorized to access its corresponding resources after holding the token. In addition, each book corresponds to a "Lock". Michael Jacob holds the locks of 1, 3, and 5. Li Si can also hold the locks of 2, 4, and 6, you can share books as long as the resources you access do not conflict with each other. Even if there is a conflict, you are not afraid. Whoever queues to hold the lock first can access the resource. The person who cannot get the lock can either wait or give up. That's easy.
The database also solves the sharing conflict problem through locking. Visual FoxPro provides the rlock () function to lock records. Once a record is locked, other users cannot lock the same record. The lock can be applied again only after the lock on the record is released. Please complete [Experiment 2-2 Use Visual FoxPro to lock and unlock records ].
Experiment 2-2 Use Visual FoxPro to lock and unlock records
2.1.3 optimistic and pessimistic buffer policy 2.1.3.1 pessimistic lock
The book club operated for a period of time after introducing the lock mechanism, but the store quickly found new problems. Some customers are afraid that others will steal the books they want to read, so they will receive a large number of locks in advance and read them one by one with peace of mind. After all, they will receive the books. 2-2:
Figure 2-2 there is a problem with the operation of the book club. Some people occupy a large number of locks, causing resource shortage in the lock.
It can be seen that the more lock resources the customer occupies, the longer the lock holding time, the more customers will not be able to get the lock in queue. In fact, the book resources are not so tight, there are only people who "Occupy the pitfalls and do not pull shit", and one person occupies several traps.
The overwhelmed shopkeepers had to seek experts and ask them to help them with their ideas. This time, experts helped us not only find the root cause of the problem, but also gave our shopkeepers two medicines to solve the urgent issue of the book. Where is the root cause of the problem? What are the two medicines?
After expert examination, the original business process is abstracted and summarized, and the original business process is summarized into the following five steps: apply the following database operations: Record lock, read data, modify data, save modification, and record unlock ). When a user holds the Lock of a book (record), other users cannot hold the lock on the book (record) until the lock is released. The reason for this process is that the shopkeeper is too pessimistic and worried about concurrency conflicts. Therefore, it is required to lock and then browse (read or modify). This is equivalent) the advantage of achieving exclusive access at the granularity is that there will never be concurrency problems, but the cost is also heavy, resulting in the failure to effectively use resources. Our experts give this model a name called "pessimistic lock" (in fact, "pessimistic buffer" is more appropriate, but we haven't mentioned the concept of "Buffering" yet, so it is also called a pessimistic lock ).
Pessimistic locks are characterized by locking, modifying, saving, and unlocking. Pessimistic locks enable "exclusive access" at the record-level granularity to solve the concurrency conflict issue. At the same time, the more records that the pessimistic lock locks, the longer the time, the lower the resource usage efficiency, giving people the feeling that the performance is lower.
To this end, experts provide the following prescriptions: (1) Reduce the number of resources that each customer can lock as much as possible; (2) reduce the resource lock time as much as possible. Therefore, we can limit each customer to receive only one lock at a time, while limiting the reading time of the customer, so that the lock can be returned to the lock as early as possible. However, from the perspective of the store, it is feasible for the reader to get only one lock at a time. It may be unsatisfactory to limit the reading time. Since there is no limit on the reader's reading time, it cannot be ruled out that, after someone has taken the Lock of a book, he will not be able to get the book, or he may have been reading the book for three days, and the net will be used for two days (taking a pitfall without pulling shit ), resource usage efficiency is still low.
2.1.3.2 buffer and optimistic lockOur experts seem to have seen the shopkeeper's mind, and then gave him a second prescription. This time, the shopkeeper needs to make a thorough transformation of his business model. One of the transformations is to introduce the "buffer" mechanism.
What is the "buffer" mechanism? Let's take a look at the new solution (2-3) provided by experts. In this solution, each customer holds a handheld reader. "Borrowing books" does not really take books away, but simply downloads them to the reader from many terminals of the book bar, and then reads them with their own readers. In this way, multiple people can read the same book at the same time without interfering with each other. We can understand "Reader" as "buffer ". After the data in the database is downloaded to the reader, the reader can be "offline" (Off Line), and each customer has their own "Buffering", so they do not interfere with each other. As for how much data you want to buffer, it depends on the shopkeeper's restrictions. If the shopkeeper restricts the reader to load only one book (one record) at a time, we call this buffer mode "Row Buffering". If the shopkeeper is generous enough, if we allow all the books in the library to be downloaded to the reader, we call it "Table Buffering ". In fact, the expert solution we see in [2.1.3.1 pessimistic lock] is the pessimistic row buffer solution.
Figure 2-3 Introduction of new devices and improvement of business processes
If our book club only allows customers to read books, there will be no improvement after the introduction of "Buffering. After all, all books are read-only for customers. You can download and read data separately without any concurrency issues. However, our experts advise shopkeepers to provide customers with new services: allow customers to record the number of times they read the book when they "return. At the same time, customers can add comments to each book and save the comments. Later readers can see the number of reads and comments of their predecessors while downloading the book. It is not only a place for reading books, but also a place for readers to communicate with each other.
The introduction of new services also brings about new problems. Assume that two customers have downloaded the same book and submitted their own comments to the database. In order to solve the concurrency problem, we recommend the "Lock" magic weapon again. But this lock is different from the previous one. We call it "optimistic lock" (also called "optimistic buffer mode "). Optimistic people often think that the possibility of concurrent conflicts is very small, so they can give priority to the resource utilization efficiency problem and then consider whether there will be conflicts.
The optimistic buffer Mode features modification, lock, save, and unlock. That is to say, the optimistic buffer mode allows multiple users to perform their own modification operations at the same time (because no lock is applied during modification). Only the lock is applied when the modification is saved, and the lock is immediately unlocked after the modification. Because it takes a long time for users to modify data, and the storage is only an instant, the optimistic buffer mode can ensure that the resource lock time is as short as possible, thus improving the resource usage efficiency. However, the disadvantage of optimistic buffer mode is that concurrent conflicts cannot be avoided.
Let's take an example: Assume that Zhang and Li have downloaded the same book from the book bar at the same time, and the book has been read for 10 times. He recorded the information he had read for 10 times in Michael Jacob and Michael Lee. Zhang sanxian finished reading the book and asked for "returning the book" at the bar to download another one. During the course of returning the book, The system adds the number of reads in Michael Jacob's reader to 11 and saves them to the database. After that, Li Si finished reading the book and he also went to the bar to "return the book ". The System reads Li Si's reader 10 times, adds it to 11 times, and saves it to the database. Note that the number of reading times for the original books was 10 times, and the number of reading times for James and James should be 12 times. However, what we record in our database is 11 times, and one reading count is lost (this is the concurrency conflict problem named "Lost modification" which we will refer to later )!
As the optimistic buffer mode allows users to modify the data first, both Michael and James can modify the data. However, there is a conflict during storage. The optimistic buffer mode increases resource usage efficiency while also brings about potential concurrency conflicts. If no effective test is performed, conflicts may cause serious consequences. How to detect conflicts and update in Visual Foxpro is described in [2.1.4 data deletion and update.
To enhance the impression of the optimistic buffer mode, please complete [Experiment 2-3 Updating the number of books reading and related concurrency conflicts in the optimistic buffer mode ].
Experiment 2-3 Update the number of books read and related concurrency conflicts in optimistic buffer Mode
2.1.3.3 SummaryIt is hard to say whether optimistic buffering is good or pessimistic buffering is good. However, when using pessimistic buffering, we should try to ensure that as few resources as possible are locked and the lock time is as short as possible. When using optimistic buffering, we must provide a complete set of conflict detection and resolution mechanisms to prevent concurrent problems. In addition, different buffer policies may be required in different scenarios.
Currently, the vast majority of database operations adopt an optimistic buffer mode to improve data access efficiency. At the same time, it also puts forward higher requirements for developers of database applications, that is, you must learn how to write programs to solve concurrency conflicts. We will discuss this in detail in the subsequent sections of this chapter. A higher usage frequency of optimistic buffering does not mean that pessimistic buffering is useless. The following experiment demonstrates an Application Scenario of pessimistic buffering:
In Visual Foxpro 6.0, there is no automatic growth field (Note: Visual FoxPro 8.0 allows you to set a field to an automatic growth field ), when we need to set the primary key field of a table to auto-increment, We have to implement it through programming. At the same time, to ensure that the primary keys applied by two concurrent users do not overlap, we must use the pessimistic row buffer policy. Experiment 2-4 designed and implemented a set of automatic growth Field Mechanism for pessimistic buffering, which can help us understand some applications of pessimistic buffering.
Experiment 2-4 Use a pessimistic buffer policy to implement automatic growth Fields
Note: Unless otherwise mentioned, the optimistic buffer mode is used.
2.1.4 data deletion and update 2.1.4.1 currentvalue and oldvalueThe optimistic buffer mode improves the efficiency of concurrent database access, but it also brings us a new problem, that is, to solve the data inconsistency problem caused by concurrency. In the story above, we can see that Zhang San and Li Si browse books once each time, while they only add 1 instead of 2 when updating the database. How can we detect and circumvent similar problems? The vast majority of database access technologies provide currentvalue and oldvalue (ADO. net is called originalvalue). By comparing these values, we can find potential conflicts (Note: This method does not guarantee all concurrency problems, for further analysis, see the second part of this chapter ). In order to have a perceptual knowledge, complete step 3 of [update the number of books and related concurrency conflicts in Experiment 2-3 optimistic buffer mode.
So what is currentvalue and oldvalue. Let's use a brand new example to illustrate this problem. Assume that John and James read the number of items from the database and store them in the local cache (2-4 ).
Figure 2-4 Zhang 3 and Li 4 read the record with the item code as 1 from the database
Michael jacob first sold one item and updated the database, changing the quantity to 9. At this time, the number in the buffer is still 10, and he does not know that someone has modified the data in the database (2-5 ).
Figure 2-5 zhangsan first updates the database and writes data to table 9
Now, due to a new batch of goods, Li Si wants to add 10 to the number of goods encoded as 1. If he performs operations directly in his own buffer and then forcibly updates the database, he will erase the fact that John sold one item, because the number of records in the database after the update is 20, and the actual situation should be 19.
How does Li Si ensure that he does not cover any updates? At this time, Li Si can adopt such a policy: when he tries to modify the data in the buffer zone, he first backs up the data in the buffer zone, that is, the original data read at that time is retained, modify a copy of the original data (2-6 ).
Figure 2-6 three values are obtained by retaining the buffered backup.
Then we get the values of three different versions of the same data, namely, the original value (old value or original value), that is, the values first read into the buffer; the current value is the value in the current database. The recommended value is the value that the user intends to change the data in the database.
Now, Li's update policy can be adjusted as follows: (1) first, lock the corresponding records in the database to ensure that the data will not change during subsequent operations. (2) compare whether current value and old value are the same. (3-a) if the two values are the same, it indicates that the data has not been moved and the update is completed directly. (3-B) if the two values are different, this indicates that someone modified the value in the database during data modification by Li Si. At this time, the current value, original value, and recommended value can be displayed to the user separately, and the user determines whether to force update. If force update is performed, 20 is forcibly written regardless of the current database value. If you do not force update, you can replace the original value with the current value and ask the user to modify the data again. (4) unlock.
In the above update policy, we made the lock again, but the lock time for data is very short, so it will not affect the database's concurrency efficiency. By comparing the original value with the current value, we can find whether someone has moved the data in the database, prompting the user to make further adjustments.
This solution solves some concurrency problems, but it will lead to new ideas. Example 2-7:
Figure 2-7 update different fields
John modified the quantity, and Li Si modified the amount. Although the current value in the quantity field is different from the original value, Li Si never touched this field. On the amount field modified by Li Si, the current value is the same as the original value. Can Li Si be saved or not?
It depends on your update policy. If you think the amount and the quantity are irrelevant, you can change them separately. At this time, you can allow Li Si to update. If you think that the amount and the quantity are inseparable and any change will affect the other, you cannot allow Li Si to update.
Some people are beginning to have a headache. How do you ensure the implementation of the update policy? This is what we will discuss below.
2.1.4.2 where phrase of a hidden organThe SQL language has become the standard language for relational database operations. It can be said that all relational databases support SQL commands. When we use various graphical user interfaces to perform database operations, we should also note that running at the underlying layer is a very simple SQL command. When you press the Save button with the mouse, an update command may be sent to the database. There are many data update commands in the broad sense, including insert, delete, and update commands. For the sake of convenience, in this part of the content, we will only talk about "where phrase of hidden organs" in the context of the update command ".
In the previous section, we discussed the current value, original value, and recommended value for half a day. During database operations, all updates must be converted to the update command in SQL, how are these values rubbed into the update command, and how can they detect concurrency conflicts? In fact, all the secrets are in the where phrase of update. Let's take a look at how the where phrase plays the role of a secret weapon in the example in Figure 2-6.
Li Si now wants to save the changes. He will first consider using the update command. The command he wrote is as follows:
Update item table set quantity = 20 Where item code = 1
This command is used to change the number of products encoded as 1 to 20. Can the command be successfully executed? Of course. Because the item number field is the primary key, it will uniquely locate this record, and then change the number to 20, it will not consider whether there is a conflict. To determine whether data has been moved, this update command must be written
Update item table set quantity = 20 Where item code = 1 and quantity = 10
Note that a condition is added to the where phrase: quantity = 10. Here, the raw value of the quantity field is used as the condition, which means that such a record is found in the database. Its product code is 1, the quantity is 10, and then its quantity is changed to 20. Although the condition of "commodity code = 1" can be used to uniquely locate this record, a protection condition is added, that is, "quantity = 10". If no one has moved this data, then it should be 10, and there will be one record that meets the where condition, and the update can be successfully executed. If the data in the database has been changed to 9, the record that meets the where condition will no longer be added after the protection conditions are added, so the update will not be completed. From the user's point of view, the concurrency conflict is detected. In this case, you can use the SELECT command to read the current value of the database and provide it to the user for comparison.
Next let's look at the example in Figure 2-7. During the update, does Li Si consider how to use the where phrase to implement the quantity field? The truth is the same. If Li Si only checks whether the amount field has been moved but does not care about the quantity field during the update, his update command can be written as follows:
Update item table set amount = 14 Where item code = 1 and amount = 12
If the number and amount fields are not modified by others during the update, the update command can be written as follows:
Update item table set amount = 14 Where item code = 1 and amount = 12 and quantity = 10
The original values in both the quantity and amount fields are used. We can also see the importance of retaining the original data backup.
The above example shows that the where phrase in the update command can not only limit the update range, but also detect concurrent problems at the same time. If the where phrase conditions are carefully arranged during software design, they can play an unexpected role.
Even so, constructing a where phrase is still troublesome. However, many programming languages provide a set of where phrase generation policies to include common generation methods, we only need to choose the right one from these policies.
2.1.4.3 where phrase Generation PolicyThe where phrase generation policies provided by different languages may be slightly different, but they are basically similar to the following: keywords, keywords, updatable fields, keywords, modified fields, timestamps, and so on.
Complete [verify where phrase generation policy with Visual FoxPro in Experiment 2-5 ].
Experiment 2-5 Use Visual FoxPro to verify the where phrase Generation Policy
Through the experiment, we can see that Visual FoxPro uses the where phrase generation policy to simplify the trouble of manually writing where phrases and improve the efficiency of generating SQL commands. The timestamp is worth mentioning here.
SQL Server provides a data type called timestamp, which automatically changes each time a database is updated. This is the strictest update policy. When a policy is generated using the "timestamp" where phrase, even if the currentvalue and oldvalue are identical, as long as the timestamp is different (for example, a person modifies the data, it's time to go back again. Although the data does not change in the result, but the timestamp changes), the data update operation cannot be completed.
In some O/R mapping software (such as Hibernate), The timestamp function is implemented using a numeric field that only adds and does not subtract, which is similar to the timestamp in SQL Server.
2.1.5 SummaryFrom the above discussion, we can see that Visual FoxPro provides a complete set of conflict detection and resolution mechanisms to achieve effective concurrency control in most cases. However, the solution mechanism provided by Visual FoxPro is still lacking, and it cannot handle more complex concurrency problems (such as non-repeated reading and phantom reading. In the subsequent sections of this chapter, we will begin with a sound concurrency theory and have a more in-depth inquiry concurrency control mechanism.
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
