Integrity of relational databases---locks, constraints, keys, indexes

The RDBMS can maintain the integrity of the data in the transaction, which is achieved through a variety of mechanisms implemented by database objects, listed below are the 4 most important objects:

• Lock
• Constraints
• Key
• Index

In SQL Server, locks allow multiple users to access the same data at the same time, and ensure that data is not modified while reading data. Also, locks are used to ensure that a process does not conflict with other processes that perform data modification operations or data read operations when modifying data.

SQL Server manages locks on a per-connection, which means that a lock cannot be held concurrently by multiple connections; SQL Server also manages locks in transaction units, and multiple connections cannot hold the same lock at the same time, and multiple transactions cannot simultaneously hold the same lock.

For example, if an application opens a SQL Server connection that is attached to a table and is given a shared lock, the application can no longer open a new connection that modifies the table's data. The same is true of things. If an application initiates a transaction that modifies a particular data, nothing else can modify the data until the transaction is completed. This is true even if multiple transactions share the same connection.

SQL Server uses 6 types of locks, more specifically, 6 resource lock modes:

• Shares
• Update
• Exclusive
• Intention
• Mode
• Batch Update

Shared locks, update locks, exclusive and intent locks are available for table or index rows, pages (tables, or 8KB of storage pages used for indexes), extensions (64KB of 8 consecutive indexes or table pages), tables, databases.

Pattern locks and Bulk update locks apply to tables.

Shared Locks

A shared lock allows multiple connections and transactions to concurrently read the assigned common resources. Any other connection or transaction cannot modify the data as long as the connection and transaction are granted a shared lock. When an application successfully reads the data, the shared lock is usually released, but in some special cases, the action can be modified. For example, a shared lock is assigned to the entire transaction, ensuring that the transaction is based on data that is not modified until the transaction is complete, thus maximizing the consistency of the data. This extended lock, which can be used for transactional consistency must be 100% guaranteed, but the cost of holding a lock is to reduce the concurrent access to the data, for example, to withdraw $100 from a savings account and place a shared lock on the balance containing the savings account. This data is used to ensure that sufficient funds are available to support this withdrawal operation. It is best to prevent other connections from modifying the balance until the withdrawal operation is complete. Because shared locks are mutually compatible, there is no conflict between different transactions and connections when reading the same data.

Update lock

SQL Server uses an update lock to prevent deadlocks. Deadlocks are bad, and usually deadlocks are caused by poor programming techniques. Deadlocks occur when two processes compete for the same resource. Back to the front bank example: in this hypothetical banking business, my wife and I are on-line, transferring funds from the savings account to the checking account. It happened that we had to make a transfer at the same time, so two processes started and executed the transfer operation separately. When my process accesses these two accounts, the process issues a shared lock on the resource. Everything is normal so far, but when our process tries to modify the resources, the confusion comes along. First, my wife's process attempts to upgrade the shared lock to an exclusive lock to modify the data. Almost at the same time, my process is trying to do the same upgrade. However, the shared lock we share prevents any process from being upgraded to an exclusive lock. A deadlock occurs because no process is willing to release it, sharing the lock.

SQL Server does not take special care of deadlocks, and if a deadlock occurs, SQL Server automatically selects one of the processes and stops it as a victim. SQL Server chooses the process with the least cost associated with it, stops it, rolls back the related transaction, and returns error code 1205 to the related application. If the user catches the error correctly, they get the message: "Transaction # #在X资源上与其他进程, there is a deadlock, And is selected as the victim of a deadlock, rerun the transaction.

To prevent deadlocks from occurring, SQL Server typically uses update locks instead of shared locks. Only one process can get an update lock, which prevents its relative process from upgrading its own locks, and the bottom line is that if read is done for the sole purpose of the update, SQL Server can issue an update lock instead of a shared lock in order to avoid potential deadlock risks. SQL provides the logic to prevent deadlocks, as long as careful planning and implementation, you can avoid the generation of deadlocks.

Exclusive Lock

SQL Server typically issues an exclusive lock when performing a modification operation. When you change a field value that is summarized by a row, SQL Server grants exclusive access to the related process to access this row. This exclusive access prevents any concurrent transactions or connected processes from being read, updated, and deleted for data that is in the process of being modified. An exclusive lock is incompatible with any other type of lock.

Intent Lock

To prevent any concurrent transaction, or the process in the connection, to place an exclusive lock on a resource that has been locked by another process, SQL Server has designed an intent lock. For example, to execute a transaction that updates a single row in a table, SQL Server grants this transaction an exclusive lock on that row, and also grants this transaction intent lock on the table containing the row. This prevents other processes from placing exclusive locks in the table.

Take a real-world example: it explains the behavior of intent locks in SQL Programming: The user lives in the 404 Room of the SQL hostel and now has the unique (exclusive) permission to use Room 4 on the 4 floor. Other patrons of the hotel are not allowed to enter this room. And no customer can book all the rooms of the hotel, because 404 rooms have been occupied by the user alone. For hotels, the user has an intent lock, and for room 404, the user has an exclusive lock. The intent lock is compatible with other locks that are lower than it.

Batch update lock

Batch update locks on a table allow multiple batches to load threads, load data into tables, and disallow other types of data access. Batch update locks are issued when table locking is enabled on a table, or when the table lock option is selected with a batch operation.

Key Range Lock

When using serializable isolation levels, key-range locks protect a range of rows that are implied in the result set and are not read by T-SQL statements. The Serializable isolation level requires that the same rowset be obtained for each query in a transaction. A key range lock, which prevents other transactions from inserting new rows whose key value (read by serializable transactions) within the specified range, satisfies this requirement.

Integrity of relational databases---locks, constraints, keys, indexes