SQL Server: Transaction and Locking Architecture
- Transaction Properties
- Transaction Types
- Transaction Syntax
- Transaction Tricks
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Transactions are an inherent part of any application that collects or manipulates data. SQL Server has to ensure the data integrity. This means that no two users should modify the same piece of data at the same time. Nor should they read "dirty" data—modified but uncommitted rows. This first article in this series on transactions and locking will explain the general terms of transactions and locking in Microsoft SQL Server. The rest of the articles will go into the details of managing transactions, and locking and resolving the blocking problems.
As it applies to SQL Server, a transaction is a single statement or multiple data modification language (DML) statements executed together. All statements in a transaction are treated as a single component of work—either all of them succeed or all of them fail. SQL Server writes the changes to the log file first; if all statements succeed, the transaction is then committed—the net result of changes is saved to the data file. If any of the statements fail, then the whole transaction is rolled back, and none of the changes are saved. For example, consider what happens when Ms. Jones purchases a $1000 diamond ring. The application needs to record the purchase of the ring, adjust Ms. Jones's outstanding balance, and reduce the inventory count. The following code snippet shows what the system would have to do when such a purchase takes place:
BEGIN TRANSACTION
INSERT purchase (
item,
customer,
price,
item_count,
total)
VALUES (
'ring',
'ms. jones',
1000,
1,
1000)
IF @@ERROR <> 0
BEGIN
RAISERROR('error occured while recording purchase', 16, 1)
ROLLBACK
END
UPDATE ring_inventory
SET current_count = current_count - 1
WHERE price = 1000
IF @@ERROR <> 0
BEGIN
RAISERROR('error occured while adjusting inventory', 16, 1)
ROLLBACK
END
UPDATE balance
SET balance = balance + 1000
WHERE customer = 'ms. jones'
IF @@ERROR <> 0
BEGIN
RAISERROR('error occured while adjusting balance', 16, 1)
ROLLBACK
END
COMMIT TRANSACTION
Two-tiered applications in which the business logic is enforced within the database make heavy use of database transactions. In multi-tiered applications, most of the business logic is encapsulated in middle-tier objects. Even so, multi-tiered applications also benefit greatly from transactions—database transaction simply becomes a part of a larger transaction typically initiated by the middle-tier component.
The way transactions are implemented can greatly affect the application's performance. The reason for this is the way SQL Server handles transactions. When a unit of data is about to be modified, SQL Server reserves that unit for exclusive use of the connection that initiated the transaction. In SQL Server, this is referred to as locking. Due to locking, no two users can modify the same piece of data at the same time. Nor can a user read data that is being modified by another user at the same time.
Perhaps we should step back and think about the reasons why locking is so important. Going back to the example above, consider what happens if Ms. Smith decides to purchase exactly the same ring as Ms. Jones. At 6 p.m. on March 2nd, the inventory table shows that the store has 20 rings available in stock—all worth $1000. Ms. Jones hands her charge card to the clerk. The clerk looks up the inventory, which shows 20 rings; in the meantime, Ms. Smith also hands her charge card to another clerk, who also sees 20 rings in the inventory. Both clerks decrement the inventory by one, but the total number of rings inventory now shows 19. What if Ms. White had purchased the same ring a week ago, and brought it back just as the other two ladies were buying the rings? Then the inventory might show 18, 19, 20, or 21, depending on which clerk was first to read data from the inventory table. You get the picture—without locking, our applications would run into numerous problems.
SQL Server supports various levels of lock granularity. The lowest level is a single row. Sometimes, SQL Server doesn't have enough resources to lock each individual row. In such cases, SQL Server can acquire locks on a single data or index page, group of pages, or an entire table. The granularity of locks depends on the memory available to SQL Server. We'll discuss the locking in greater detail in a following article.
By now, you should guess that transactions and locking represent a tradeoff between data integrity and concurrency. If you constantly lock data pages, the rest of the users will have to wait until your data modifications complete. On the other hand, if you don't acquire appropriate locks, the data integrity might be compromised. Understanding SQL Server transactions and locking behavior will get you well on your way to writing highly scalable applications.
Transaction Properties
Each transaction has to abide by the ACID properties, defined in the following table:
|
ACID Property |
Meaning |
|
Atomicity |
Either all or no work is performed. |
|
Consistency |
A transaction must leave data in a consistent state. |
|
Isolation |
Each transaction is independent of all other transactions. That means each transaction will read data that was committed prior to beginning of the other transactions or after the end of the other transactions. |
|
Durability |
After transaction is committed, the data is in a persistent state, regardless of the circumstances. SQL Server records the transaction in the transaction log, and marks it as being committed. If the transaction is not committed, then SQL Server will roll all data changes back. |