Database transactions and locks

Brief Introduction

Relational databases have four notable features: security, integrity, concurrency, and monitoring. Database security is to ensure the security of data in the database, prevent unauthorized users from modifying data in the database at will, and ensure data security. In most database management systems, the security of databases is guaranteed through licensing. Integrity is an important feature of the database and an important mechanism to ensure the effectiveness of data in the database, prevent errors, and implement business rules. In a database, the difference between the stored data is useless garbage or valuable information, mainly based on whether the database integrity is sound.

In SQL Server 7.0, data integrity is guaranteed through a series of logics, which are divided into three aspects: entity integrity, domain integrity and reference integrity. This can be said for any system, without monitoring, there will be no optimization. This statement is also practical in the database management system. Only by conducting comprehensive performance monitoring on the database can we find the factors and bottlenecks that affect the system performance. In this way, we can take appropriate policies to address the bottlenecks and improve the system performance. Concurrency is also a very important concept. It is used to solve the problem when multiple users operate on the same data.

Especially for network databases, this feature is even more prominent. It is not enough to increase the processing speed of the database simply by improving the physical speed of the computer. We must also fully consider the database concurrency problem and improve the efficiency of database concurrency. So how can we ensure concurrency? SQL Server 7.0, a database product oriented to the next century, solves the database concurrency problem by using transactions and locks.

Concepts and features

Transactions and locks are two closely related concepts. A transaction is a unit of work, including a series of operations. These operations are either all successful or all failed. The transaction ensures that the modification of multiple data is processed as a unit. For example, in banking, there is a accounting principle, that is, lending and lending are equal. In order to ensure this principle, you must ensure that the registration of loans and loans either succeeds at the same time or fails at the same time.

If you record only the borrow or only the loan, you will be in violation of the accounting principle, and the wrong account will be recorded. SQL Server manages multiple transactions through the transaction mechanism to ensure transaction consistency. Transactions use locks to prevent other users from modifying data in another unfinished transaction. For multi-user systems, the lock mechanism is required. In SQL Server 7.0, transaction logs are used to ensure the integrity and recoverability of modifications.

SQL Server has multiple locks that allow transactions to lock different resources. A lock is used to protect specified resources and is not operated by other transactions. To minimize the lock cost, SQL Server automatically locks the resource object based on the lock level corresponding to the task. Locking a small object, such as locking a row, can improve concurrency, but it has a high cost, because if many rows are locked, more locks are needed. Locking a large object, such as locking a table, significantly reduces the concurrency, because locking the entire table limits other transactions to access other parts of the table, but the cost is relatively low, because only a small number of locks need to be maintained.

Transactions and locks have the following features:

A transaction is a unit of work, either completely or completely.
Transactions ensure operation consistency and recoverability
Each Transact-SQL statement can be a transaction.
The actual transaction is a user-defined transaction, which includes a series of operations or statements.
In a multi-server environment, user-defined distributed transactions are used to ensure operation consistency.
Lock is a means to ensure Concurrency Control
Resources that can be locked include rows, pages, clusters, tables, and databases.
The lock types mainly include shared locks and exclusive locks.
Special types of locks include intent locks, modification locks, and mode locks.
The shared lock allows other transactions to continue to use the locked resources.
The exclusive lock allows only one transaction to access data.
The system can handle deadlocks.
You can customize the features of the lock based on the actual situation.

 Transaction Definition

A transaction refers to the work of a unit, which is either done in full or not done in all. As a logical unit, it must have four attributes: automation, consistency, independence, and persistence. Automatic means that a transaction must be an automatic unit operation, either to modify all data or to modify all data.

Consistency means that all data must be consistent when the transaction is completed. In relational databases, all rules must be applied to transaction modifications to maintain the integrity of all data. All internal data structures, such as links between tree indexes and data, must be correct after the transaction ends.

Independence means that the modification of a parallel transaction must be independent from the modification of other parallel transactions. The data seen by a transaction is either the status before the transaction is modified by another transaction or the data that has been modified by the second transaction. However, the transaction cannot see the data being modified. This feature is also known as seriality. Durability means that after a transaction is completed, its impact is permanently generated in the system, that is, the modification is written to the database.

The transaction mechanism ensures that the modification of a group of data is either completely executed or not executed. SQL Server uses transactions to ensure data consistency and recoverability when the system fails. A transaction is a recoverable unit. It consists of one or more statements, which can affect one or more rows of data in a table. After the transaction is opened, it is committed after the transaction is successfully completed, or when the transaction fails to be fully canceled or rolled back.

 

* *** Working principle of the transaction

Transactions ensure data consistency and recoverability. The principle 1 of the transaction is shown in.

Figure 1 transaction Working Principle

 

After the transaction starts, some operations of the transaction are successively written into the transaction log. Generally, there are two types of operations to write data to logs: one is data operations and the other is task operations. Operations on data, such as insertion, deletion, and modification, are typical transaction operations. The objects of these operations are large amounts of data. Some operations are for tasks, such as creating indexes. These task operations record a flag in the transaction log to indicate that such operations are performed.

When a transaction is canceled, the system automatically performs the inverse operation to ensure system consistency. The system automatically generates a checkpoint mechanism, which occurs periodically. The checkpoint period is the time interval automatically calculated by the system based on the user-defined time interval and the frequency of system activity. Check the transaction log periodically. If all the transactions are completed in the transaction log, the check point will commit the transactions in the transaction log to the database, and make a checkpoint commit mark in the transaction log.

If the transaction is not completed in the transaction log, the check point will not commit the transactions in the transaction log to the database, and a checkpoint is not submitted in the transaction log. The transaction recovery and checkpoint Protection System Integrity and Recoverability can be described in the example 2.

Figure 2 example of transaction recovery and Check Point

In this example, there are five transactions: Transaction 1, transaction 2, transaction 3, transaction 4, and transaction 5. The box indicates the start and completion of the transaction. The horizontal direction indicates the time. A Checkpoint indicates that a checkpoint mechanism occurs at a certain time point. A system failure indicates that a system failure occurs at a certain time point due to power failure or system software failure.

The completion of transaction 1 occurs between checkpoints, so transaction 1 is committed to the database. Transaction 2 and transaction 4 are completed before the system fails, so these two transactions can be rolled forward to the database by the system. TRANSACTIONS 3 and 5 were canceled because the system failed and were not completed.