Concurrency Control

As you already know, data in a database is generally shared between many user application programs. The situation in which several user application programs read and write the same data at the same time is called concurrency. Thus, each DBMS must have some kind of control mechanism to solve concurrency problems. A high level of… [Continue Reading]

A transaction specifies a sequence of Transact-SQL statements that is used by database programmers to package together read and write operations, so that the database system can guarantee the consistency of data. There are two forms of transactions: Implicit Specifies any single INSERT, UPDATE, or DELETE statement as a transaction unit Explicit Generally, a group… [Continue Reading]

There are six Transact-SQL statements related to transactions: BEGIN TRANSACTION BEGIN DISTRIBUTED TRANSACTION COMMIT [WORK] ROLLBACK [WORK] SAVE TRANSACTION SET IMPLICIT_TRANSACTIONS The BEGIN TRANSACTION statement starts the transaction. It has the following syntax: transaction_name is the name assigned to the transaction, which can be used only on the outermost pair of nested BEGIN TRANSACTION/COMMIT or… [Continue Reading]

Concurrency can lead to several negative effects, such as the reading of nonexistent data or loss of modified data. Consider this real-world example illustrating one of these negative effects, called dirty read: User U1 in the personnel department gets notice of an address change for the employee Jim Smith. U1 makes the address change, but… [Continue Reading]

Affecting Locks You can use either locking hints or the LOCK_TIMEOUT option of the SET statement to affect locks. The following subsections describe these features. Locking Hints Locking hints specify the type of locking used by the Database Engine to lock table data. Table-level locking hints can be used when finer control of the types… [Continue Reading]

In theory, each transaction should be fully isolated from other transactions. But, in such a case, data availability is significantly reduced, because read operations in a transaction block write operations in other transactions, and vice versa. If data availability is an important issue, this property can be loosened using isolation levels. Isolation levels specify the… [Continue Reading]

The Database Engine supports an optimistic concurrency control mechanism based on row versioning. When data is modified using row versioning, logical copies of the data are maintained for all data modifications performed in the database. Every time a row is modified, the database system stores a before image of the previously committed row in the… [Continue Reading]