Database Transactions and Locks (i)

Data | database


briefly




The
relational database has four notable features, namely, security, integrity, concurrency and monitoring. The security of the database is to ensure the security of the data in the database, to prevent unauthorized users to modify the data in the database to ensure the security of the data. In most database management systems, the security of the database is ensured primarily through licensing. Integrity is an important feature of database, and it is also an important mechanism to ensure the data in database is effective, prevent errors and realize business rules. In the database, the difference between the saved data is useless garbage or valuable information, mainly based on the integrity of the database is sound. In SQL Server 7.0, the integrity of data is guaranteed by a series of logic that is divided into three aspects, namely, entity integrity, domain integrity, and referential integrity. This can be said for any system, without monitoring, there is no optimization. This statement is also practical in terms of database management systems. Only through the comprehensive performance monitoring of the database can we find the factors and bottlenecks that affect the performance of the system, so that we can take practical strategies to solve the problems and improve the performance of the system. Concurrency is also a very important concept, which is used to solve problems when multiple users operate on the same data. Especially for the network database, this feature is more prominent. To improve the speed of database processing, it is not enough to rely solely on improving the physical speed of the computer, and the concurrency of the database should be fully considered to improve the efficiency of database concurrency. So how do you guarantee concurrency? In this next-century database product SQL Server 7.0, the concurrency of the database is resolved by using transaction and lock mechanisms.





Concepts and Characteristics





transactions and locks are two closely related concepts. A transaction is a unit of work, including a series of operations that are either all successful or fail all. Transactions ensure that the modification of multiple data is handled as a unit. For example, in the banking business, there is a accounting principle, that is, borrowing and lending, loans are equal. In order to ensure this principle, it is necessary to ensure that the registration of borrowing and lending is simultaneous success or failure at the same time. If only a loan is recorded, or if only a loan is recorded, then the accounting principle is violated, and the error of the account will occur. SQL Server manages multiple transactions by supporting transaction mechanisms to ensure transactional consistency. A transaction uses a lock to prevent other users from modifying data in another transaction that has not yet been completed. The lock mechanism is necessary for multi-user systems. In SQL Server 7.0, the transaction log is used to ensure the integrity and recoverability of the modifications.





SQL Server has a variety of locks that allow transactions to lock different resources. A lock is the protection of a specified resource and is not manipulated by another transaction. To minimize the cost of the lock, SQL Server automatically locks the resource object with a lock of the appropriate level of the task. Locking smaller objects, such as locking rows, can increase concurrency, but has a higher cost because if you lock many rows, you need to occupy more locks. Locking larger objects, such as locking a table, greatly reduces concurrency because locking the entire table restricts other transactions from accessing other parts of the table, but costs less because you need to maintain fewer locks.





transactions and locks have the following characteristics:

A
transaction is a unit of work that either does all or none of the transaction guarantees the consistency and recoverability of the operation. Each Transact-SQL statement can be a transaction the actual use of a transaction is a user-defined transaction, which includes a series of actions or statements in a multiple-server environment Using user-defined distributed transactions, guaranteed operation consistency Lock is a means of ensuring concurrent control the types of resources that can be locked include rows, pages, clusters, tables, and database locks, mainly including shared and exclusive locks. Special types of locks include intent locks, modify locks, and pattern lock share locks allow other transactions to continue using locked resources Exclusive lock allows only one transaction to access the data system itself can handle the deadlock user can customize some characteristics of the lock according to the actual situation


Transaction





the definition of a transaction





A business is a unit of work that is either wholly or wholly done. As a logical unit, you must have four attributes: Automatic, consistent, independent, and persistent. Automation means that a transaction must be an automatic unit work, or that all data modifications are performed, or that all data modifications are not performed. Consistency means that when a transaction completes, all data must be in a consistent state. In a relational database, all rules must be applied to the modification of a transaction in order to maintain the integrity of all data. All internal data structures, such as the link between the tree index and the data, must be correct after the transaction has ended. Independence means that the modification of a parallel transaction must be independent of the modification of other parallel transactions. The data that one transaction sees is either the state before which the transaction was modified by another transaction, or the second transaction had modified the completed data, but the transaction could not see the data being modified. This feature is also called Serial. Persistence means that when a transaction is completed, its effects are permanently generated in the system, that is, the modification is written to the database.




The
transaction mechanism ensures that the modification of a set of data is either fully implemented or not implemented. SQL Server uses transactions to ensure data consistency and to ensure recoverability in the case of system failure. A transaction is the work of a unit that can be recovered, consisting of one or more Transact-SQL statements that can affect a row or rows of data in a table. After the transaction is opened, it is not committed until the transaction is successfully completed, or until the transaction fails all of its execution or is rolled back.