Database application concurrently controls several implementation paths in Built 01-7-10 01:57:05
One. introduction
Concurrency control refers to a mechanism to specify concurrent operations in a multi-user environment. Its purpose is to avoid the loss of data, read dirty data and non-repeatable read, etc., thereby ensuring the correctness and consistency of the data. Concurrency control is very important in multi-user mode, but this is often overlooked by some database developers, and because the level and type of concurrent control are very rich, sometimes people are more confused when they choose, unclear the measurement level The principles and ways of choice are selected. This article will start from one example, combined with the relevant knowledge of the database theory, make a more comprehensive summary of the way to control the data in database applications, hoping to help readers find a reasonable concurrent control method.
two. An example of a concurrent control failure
In order to better understand the concept of concurrency control, we repeat an example of reconciliation. First give a data dictionary definition of a simplified account table (Account) information in the bank database, we will use this table as an example.
Column Name Column Column Type Account Number ID (Key Column) Char (10) Open Master Unamechar (10) Deposits MPAYOURENCURENCY Expenditure MPalanceCurrency Deposit
This example is generated during the session of the client and the server-side database: a household representative pays 2,000 yuan in the bank front desk, the bank will come to the user's deposit information to display the bank deposit balance of 20,000 yuan; at this time, another bank account transfer The check payment of 5,000 yuan, the machine inquiry is also 20,000 yuan in the current user deposit. At this time, the bank's invalidation will have the user deposits more than the withdrawal amount, pay the customer 2,000 yuan and change the user deposit to 18,000 yuan, then the bank's According to the check, the operator will put the exchange of 5,000 yuan, and the balance of the user is changed to 25,000 yuan. It is clear that the bank will lose 2,000 yuan because the modifications made by another candidate are covered.
This is due to the failure of concurrent operation control, since there is no reasonable isolation of two concurrent operations, the data sets the data set and the database of the client data set and the database data from the database will be inconsistent, As a result, loss of modification was generated.
three. Database concurrent control theoretical foundation
In this case, the concepts often used in concurrent control are explained, and the readers can check the information. The transaction is an important concept in the database. It is a series of either do, or no more program collection is a unit of database concurrent control. If the transaction is incapable, it can generate loss modifications, read dirty data, and not repeatable read. However, in the application, in order to improve, it can tolerate some inconsistencies, such as that most business logic can be easily read after proper adjustment. Today's popular relational database system (such as Oracle, SQL Server et al.) Is the target of transaction isolation level and blocked mechanism, and we can get almost any type according to the protocol provided by the Transaction ISOLAL. Reasonable concurrent control method. For example, there are four locks in the Microsoft SQL Server system: shared locks, row lock, intent lock (also divided into shared intent locking, row it intended, sharing intention to lock), modify the lock. There is a certain compatibility between various locks. There are four transaction isolation levels: not submitted, read, readable, serialized, different isolation levels, different isolation levels, different isolation levels. This part is very rich, enough to write a book, the space limit, not prepared, please refer to the textbooks in the database theory.
What is the principle that the blocking type is so rich and the isolation level, then what is the principle? That is the data consistency requirements and two aspects: For example, the four isolation level data consistency is sequentially elevated, but the release sequentially decreases, and the general system default isolation level is submitted. Generally, this can meet the requirements of the application, but this isolation level cannot avoid non-repetitive phenomena, that is, during the period of the database record, the same records read different records at different times can have different content. Sometimes you need to dynamically pass the SQL statement to change its blocked state or isolation level. four. Concurrent control technology
There are many ways to implement concurrent control. If DBMS support, of course, it is best to use its own concurrent control capabilities. If the system does not provide such a function, you can use the development tool support, you can also consider adjusting the database application, and sometimes you can avoid this simultaneous operation of the efficiency by adjusting the working mode. The author has made a summary of various strategies, mainly some points:
(1) Adjusting the working mode, modifying applications, avoiding unnecessary concurrency.
This is feasible in some cases, for example, the entry person can only modify the records you create, then there will be no errors in concurrent operation, because the records that each different user can update will not Anhage occurred. In this case, you need to increase the user column in the database table. When the user browsses the record, the user column is used as a filter condition, and the application's SQL statement is adjusted accordingly. However, this strategy is limited, because in a large amount of case, concurrent control is inevitable.
(2) Function by means of DBMS.
Large relationship systems have a better concurrent control. For example, you can use update cursors, explicitly lock, change transaction isolation levels, and so on. Of course, there are a lot of attention in their use, such as: (1) The transaction definition is preferably not included in the customer interaction. (2) Only in the data consistency requirements are particularly stricter, the repeatable read and can read isolation level is used when the demand is not high. (3) In the same transaction, it is necessary to change the lock level of the data as needed, but in general, do not block the coarse grain size like Tablock. (4) Different transactions can explicitly set the isolation level according to the needs of concurrency. (5) Use a cursor in an operation containing the customer's interaction and will shorten the interaction time as much as possible.
We look at an example of updating a cursor in an Informix database. Define Update Route Syntax: Declare Cursor-Name Cursor for select-statement for update [of column-list]. Updating the cursor When completing the data browsing and modification, you want to lock the current record, pay attention to the update cursor is only valid for the update view. In order to increase concurrency, it is often necessary to use the scrolling cursor to use, scrolling the cursor definition method: DECLARE CURSORNAME SCROLL CURSOR [with hold] for selectstatement, but the scrolling cursor does not lock the current record.
The following code completes the browsing and modification of the customer's account content, and the code is written in Informix's ESQL / C (as a host language in C language) to display the usage of the update cursor:
$ Declare mycursor for select mDeposit, mPayout, Mbalance from acount for update; // Define Update Cursor
$ Open mycurs; // Open the cursor
For (;;)
{$ FETCH MyCURS INTO $ MDEPOSIT, $ MPAYOUT, $ mbalance; // Read records from the cursor
If SQLCODE = SQLNOTFODE = SQLNOTFOD1; // If the record is finished, exit the loop .... // Display record content to the user
....// If the user decides to modify the record, continue
$ UPDATE ACOUNT SET (MDeposit, MPayout, Mbalance) =
($ MDeposit, $ mPayout, $ mbalance) Where current of mycurs; // Update value
}
A cursor statement with for Update has a lock function. Look at the code, after the Fetch operation, the current record pointed to by the cursor is added to the shared lock. When the user decides to modify, the lock on the record is lifted into a row lock. The other users cannot update this record. This method has a disadvantage that even if the user does not modify the current record, it is necessary to lock the current record, affecting and release, then one method that can be used is: (1) Define a scrolling cursor to complete the query. (2) Get a record of the cursor, display it to the user; (2) user browsing record until you want to modify or delete records; (3) When the user selects a record, the user wants to modify the record to define an update Cursor (4) Use the update cursor to get records, and re-displays the locked record; (5) Update this record. Then the above program can be changed to:
$ Declare MyCurs Scroll Cursor for Select ID, MDeposit, MPayout, Mbalance from Acount; // Defines Scroll Top
$ Open mycurs // Open scrolling cursor
For (;;)
{$ FETCH MyCURS INTO $ ID, $ MDEPOSIT, $ MPAYOUT, $ mbalance; // Read record values from the cursor
If SQLCODE = SQLNOTFODE EXIT; // If reading, exit cycle
....// Display record content to the user
....// If the user decides to modify the record, continue
$ DECLARE MyCURS_UPDATE CURSOR for SELECT MDeposit, MPayout, Mbalay
From acount where id = $ ID; // Define Update Cursor
$ FETCH MyCURS_UPDATE INTO $ MDeposit, $ MPAYOUT, $ mbalance; // Read value
$ UPDATE ACOUNT SET (MDeposit, MPAYOUT, Mbalance) = ($ MDeposit, $ MPAYOUT, $ Mbalance)
Where current of mycurs_update; // Update value
(3) Use the support of development tools.
Many database development tools have some convenient options or components to support concurrency control, regardless of whether DBMS supports concurrency control. Let's take a look at the concurrent control method of Delphi and PowerBuilder.
Delphi is an excellent C / S development tool that is used to query data database controls are TQuery, which can complete the browsing and updates of database table data organically combined with TupdateSQL controls. Among them, there is an attribute in the TQuery control. He has three options: (1) UpwhereAll: As long as someone modified this record in the browsing and modification, then no matter if you have modified this column, you Modifications cannot be successful when submitting. (2) UpwhereChanged: Use the key to determine whether your changes are successful according to the columns you have modified. If the column of this record modified by others does not intersect the column you modified, then your changes are still successful. (3) Upwhereonly: Judging whether your update is successful according to the key value modification. It is very convenient to automatically generate the desired update statement based on the modified property specified by the TQUERY control. The second mode is the most common modification mode, as long as others do not have the record of the record, then submit success, that is, the loss, coverage, and high concurrency . Or the above example, such as the customer browsing record, modified the MPAYOUT, Mbalance two columns in the record, then under the modification option UpwhereChanged, the SQL statement generated by the TUPDATESQL control is:
Update Acount Set (MPAYOUT, Mbalance) = ($ MPAYOUT, $ MBALANCE)
WHERE key = key_old and mPayout = mPayout_old and mbalance = mbalance_old;
KEY_OLD, MPAYOUT_OLD and MBALANCE_OLD are the intermediate variables generated by Delphi, temporarily surpass the old value of the original data record, which is used to compare whether the value is equal to the current value, if not, other users have changed it. Record, then in order to avoid loss of modification, the user's update operation cannot be completed, and it can be done. Then when the Nasson modifies the account, if someone else has modified this account, then his modification is unsuccessful and must be re-refreshed. This transformation is performed on the example above, and the loss of banks can be avoided.
Another tool compared with Delphi is a famous PowerBuilder, in its DataWindows design, we select menu Rows | Update ..., the set window of the Specify Update Characteristics is displayed, in which we set the WHERE clause in the Update statement in this window. Generate, in order to perform concurrent control. There are three options here: (1) Key Column: The value of the primary key column in the generated WHERE clause determines whether to modify with the initial query. Corresponds to the Upwhereonly option in Delphi. (2) Key and updateable columns: The generated WHERE clause comparison table The value of the main key column and the modified column is the same as the initial query. Compared with Delphi's UpwhereAll. (3) Key and modified columns: Corresponds to the UpwhereChange Options of Delphi. The WHERE clause compares the primary key and the column to modify.
(4) Adjustment application.
Some databases do not provide concurrent control, such as FoxPro, etc., and some versions of MySQL also do not support transactions. And some development tools (such as some web script editors, etc.) do not provide components that implement concurrent control, then implement concurrent control, only by adjusting our applications and database structures. The application can be adjusted according to the basic idea of the blockade. Add a lock field in a database table that needs to be controlled, this field can be a Boolean variable, which is lock for true, which is idle for false. The structure of the table at this time is:
Column Name Column Column Type Account Number ID (Key Column) Char (10) Household Unamechar (10) Deposit MDeposit Currency Expenditure MPAYOUT CURRENCY Deposit balance MPalanceCurrency lock Lock Boolean
If an application, when the customer queries this table, you can modify the record, then to prevent other customers from modifying this record during the user editing, then the customer needs to be recorded to the recorded data. Plus the lock (that is, the lock field is changed to TRUE), the release lock is released after the modification (change the lock field to false). If another customer wants to modify the record of this table, it will first detect that the record is not locked. If you have been locked, you cannot modify it. If the lock field is idle, then first lock the record, then record the record to the customer, edit, during which other customers cannot modify the record. This is effective to prevent loss of loss.
The above is a common method, but it is not completely no shortcomings. It may generate such side effects: When a user decides to modify a record, the record is locked, waiting for the user to modify, but at this time, the user is leaving. Then this record will always be locked until the user is submitted (it may be a few hours) or the timeout is timeout, then other users can change this record, resulting in a low release. There is a solution to this problem is to record the record to the record in Old_Record, and copy the contents of Old_Record to a new record new_record (custom variable), the user edits new_Record, Compare Old_Record and the original record when submitting new_record, if it is not the same, it indicates that the user has modified the original record, and the modification of New_Record is given; if it is submitted.
There is still a small shortcoming in the method: when doing new and old records, you must compare the whole record, time and program is more troublesome. You can consider adding a timestamp column in the original table (at this point you can cancel the LOCK column), then the structure of the table changes:
Column Name Column Column Type Account Number ID (Key Column) Char (10) Open MorderChar (10) Deposits MPAYOURENCY Deposits MPalanceCurrency Time Stamp DateTime
When the timestamp value is recorded in the OLD_DATE (Custom Variable), the record content is recorded in a new record in new_record, and the user edits new_record, compares the timestamp in Old_Date and the original record when submitting new_record, if Different, it indicates that users have modified the original record, and the modifications to New_Record are given down; and they are submitted. Update statement: Update Acount Set (MPAYOUT, Mbalance, Date) = ($ MPAYOUT, $ Mbalance, $ DATE)
WHERE key = key_old and date = $ date_old;
Fives. to sum up
The concurrent control we usually say refers to concurrent control in the DBMS (Database Management System). The method of concurrent control is very rich, far more than this. It can be achieved by means of the ability of the database itself, and can also be utilized by adjusting its own programs. In the database application, the method of concurrent control is performed, and there are many ways to implement a way. The basic principle based on the selection is that data consistency must be used in the need for application, on this basis, as far as possible, as much as possible.
