The database concurrency processing 1. The feature of concurrent processing database is the centralized management and sharing of data. In general, there are always several transactions in concurrently, and these parallel transactions may have the same data. Therefore, an important task of the database management system is to have a mechanism to ensure that this concurrent access and modification does not destroy data integrity, ensuring that these transactions can run correctly and achieve the correct results. We know that if the transaction is not controlled, if it is not controlled, it will lead to incorrect results and the inconsistent status of the database. To ensure that the database data correctly reflects the update of all transactions, and the data is modified when other transactions is changed at a transaction, the database system is locked by the lock to control concurrency access to the data. Second, Oracle's concurrent processing mechanism does not require any description, Oracle automatically provides a row lock, which allows users to update different rows in the table without conflict. Row-level locks are very useful for online transactions. 1, Oracle lock
The type of Oracle Lock Under normal circumstances, Oracle automatically locks the resource you need to lock to protect the data, this lock is implied, called hidden locks. However, under some conditions, these automatic locks do not meet the needs when practical applications, must be manually plus some locks. These artificial plus locks are displayed. The SQL statement that will generate hidden locks is specified below: INSERT; UPDATE; DELETE; DDL / DCL statement. The following indicates the SQL statement that generates a display lock: SELECT for Update; Lock Table Inxxx Mode. Solve read non-repeatability can use the following method. In Oracle, use SELECT for Update to add (X), and shared lock (RS) on the table. It is often used to lock a row, but don't really modify this line. There is a interaction between the lock. For example, add the RX lock to the table when updating, plus the X lock, and only the RS lock and the RX lock allows the RX lock. Therefore, the table allows updates when there are RS and RX locks. For example, when executing DDL and DCL statements, it will be plugged to lock X, and the X lock cannot be added again in the presence of X, RS, SRX, RX, and S locks. Therefore, when there is X, RS, SRX, RS or S lock, the table cannot be made to do DCL and DDL operations. In this way, the database will automatically prevent one user update the data in the table, while other users are modified simultaneously. 2, Oracle read-only transaction
Oracle supports read-only transactions. Some features have the following features: * Only inquiry * Other transactions can be modified and query data * In the transaction, any other modification of other users can not see read-only transaction is: SET TRANS ACTION READONLY SQL statement commit, Rollback, DDL ends read-only transaction 3, level of transaction consistency
The transaction is a unit defining and maintaining consistency, and the blockade is to ensure this consistency. If the requirements of the blockade will increase overhead, reduce concurrency and efficiency; some affairs do not strict requirements for quality (such as statistical transactions), if a strict blockade is unnecessary and uneconomical. Therefore, there is a need for further analysis to investigate the effects of different levels of consistency on the quality and parallelism of database data. The consistency level is defined as a few conditions: 1) The transaction does not modify the dirty data of any other transaction. Dirty data is modified by other transactions, but the data that has not been submitted. 2) Unlock the modified resource before the end of the transaction. 3) The transaction does not read the dirty data of any other transaction. 4) Add a shared lock (RS) and row lock to the data before reading until the transaction ends. * The transaction that meets the condition 1 is the 0th level. * Meet the level 1 and 2's transaction called Level 1 consistency. * Satisfied with the transactions of Conditions 1, 2, and 3 are level 2 consistency transactions. Oracle's read consistency guarantees dirty data that is not reading other transactions. * Meet the level 3 consistency transactions that meet the conditions 1, 2, 3 and 4. Three properties from Oracle: Automatically add implicit locks, release lock and read consistency at the end of the transaction, so that Oracle is automatically satisfied with the above 0, 1 and levels consistent transactions. Therefore, Oracle automatically prevents dirty reading (write - reading dependence). However, Oracle does not automatically prevent loss of modification (write-write dependence), read non-repeatability (read-write dependence), completely solve the problem in concurrency, need to meet the fourth condition (Level 3 consistency), this The programmer is required to program according to the actual situation. The method is as follows: * If you want to make some data from other transactions in a period of time, you can use the SetTransactionReadOnly statement to achieve this. * If you want to modify a data within a while, you should lock with SelectforuPdate before reading this data. * If you want to read a data in a transaction, and want to modify other data based on this data, you should lock this data before reading the data. For this type of application, the most appropriate use this SQL statement. * If you want to avoid non-read phenomena, you can use SelectForUpdate to lock the data with SELECTFORUPDATE before reading, or use SET Trans Action Readonly to set read-only transactions. Third, Sybase's concurrent processing mechanism Sybase is similar to Oracle, but in many respects. Sybase has two granular blocking, one of the particle size is a page, and the particle size of the other is a table. Sybase decides to block the lock or table seal according to the situation of the SQL statement. 1, the page lock page lock has the following three categories: * Shared: Add a shared lock when reading. In the default, the shared lock is released after the read operation is completed. * EXCLUSIVE: Mandle it when the update is updated. In the default, the discharge is released after the transaction is complete. * Update: Addition of the modified lock in the initial stage of modifying and deleting the operation (when you read the modified or deleted page). After adding a modified lock, you can also add a shared lock, but you cannot modify and lock it again. When modifying and deleting operations, if there is no shared lock, the modified lock is converted to a row lock. The purpose of this lock is to prevent deadlocks. Sybase only uses the page level lock and modify the lock when you contain an index column in the WHERE clause. 2, table-level lock
The table-level lock has the three categories shown below: * Intent: When there is a lock and sharing lock in the table, add the intent lock in the table. After all the page lock is released, the intent lock is released. * Shared: Add a shared lock when reading it. In the default, the shared lock is released after the read operation is completed. * EXCLUSIVE: Mandle it when the update is updated. In the default, the discharge is released after the transaction is complete. 3. Request the lock request lock to prevent shared locks from picking one endlessly in the table, so that writing the transaction (to ordered it) cannot be performed. 4, Sybase blockade level
Blocking levels defined by the ANSI standard definition transaction: (1) Level 1: Dirty reading (2) (2) Level 2: Do not repeat (3) (3) The current value of the cursor is confusing (4) Sybase's lack of lack of The provincial consistency level is 1. If you want to achieve consistency levels 2 and 3, you must use the Holdlock keyword to continue the shared lock to the end of the transaction. The method is as follows: Select * from auths holdinglock where author_code = 'A00001' Sybase can also change Sybase's consistency level through T-SQL's set command, so that Sybase automatically adds Holdlock keywords: SET Trans Action is Olation Level3 5. Improve concurrent efficiency in Sybase
* Avoid a plurality of users on a specific page in the table. * Avoid defining a transaction in the application of human-machine interaction, which will enable a user to block the stamp (such as picking up the phone), so that other users continue to wait. * Make the transaction as short as possible. * Use the Holdlock keyword only if necessary.
