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 … [Read more...] about Concurrency Control, Concurrency Models
Concurrency Control
Transactions, Properties of Transactions
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 … [Read more...] about Transactions, Properties of Transactions
T-SQL Statements and Transactions, Transaction Log
There are six Transact-SQL statements related to transactions: BEGIN TRANSACTIONBEGIN DISTRIBUTED TRANSACTIONCOMMIT [WORK]ROLLBACK [WORK]SAVE TRANSACTIONSET IMPLICIT_TRANSACTIONS The BEGIN TRANSACTION statement starts the transaction. It has … [Read more...] about T-SQL Statements and Transactions, Transaction Log
Locking, Lock Modes, Granularity & Escalation
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 … [Read more...] about Locking, Lock Modes, Granularity & Escalation
Affecting Locks, Displaying Lock Information, Deadlock
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 … [Read more...] about Affecting Locks, Displaying Lock Information, Deadlock
Isolation Levels, Concurrency Problems
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 … [Read more...] about Isolation Levels, Concurrency Problems
Row Versioning, READ COMMITTED SNAPSHOT
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 … [Read more...] about Row Versioning, READ COMMITTED SNAPSHOT
