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 […]
Read More...Transactions, Properties of Transactions – SQL Server 2012
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 […]
Read More...T-SQL Statements and Transactions, Transaction Log – SQL Server 2012
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:
1 2 | BEGIN TRANSACTION [ {transaction_name | @trans_var } [WITH MARK ['description']]] |
transaction_name is the name assigned to the transaction, which can be used only on the outermost pair of nested BEGIN TRANSACTION/COMMIT […]
Read More...Locking, Lock Modes, Granularity & Escalation – SQL Server 2012
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 […]
Read More...Affecting Locks, Displaying Lock Information, Deadlock – SQL Server 2012
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 […]
Read More...Isolation Levels, Concurrency Problems – SQL Server 2012
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 […]
Read More...Row Versioning, READ COMMITTED SNAPSHOT – SQL Server 2012
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 […]
Read More...