Using Locking

In database terms, locking is a mechanism used to avoid inconsistency when multiple users access and update data simultaneously. Using the locking technique, you can lock records or a database table when a user is modifying the records or table, and the data will be unavailable for other users until the current user calls the update method to make final changes. For example, say user A is updating a record. During this operation, user B deletes that record. This scenario may lead to inconsistency in the table. You can avoid this inconsistency by making that record unavailable when user A is updating it and make it available when user A is done updating it. The only drawback of this is user B has to wait until user A is done updating the record. Oh well, waiting is better than having inconsistencies and inaccurate data.

Isolation Levels

An isolation level represents a particular locking strategy applied on a database. It's basically a way of representing the stages of locking depending on the complexity of locking. There are four isolation levels, starting from level 0 to 3. Applying these levels on transactions is also called determining Transaction Isolation Levels (TILs).

TILs provide consistency during data manipulation when multiple users are accessing data simultaneously. There are three cases that apply to the transactions:

• Dirty read: A dirty read occurs when a user reads data that have not yet been committed. For example, user 1 changes a row. User 2 reads the row before user 1 commits the changes. What if user 1 rolls back the changes? User 2 will have data that never existed.

• Nonrepeatable read: A nonrepeatable read occurs when a user reads the same record twice but gets different data each time. The simple case for this situation is when user 1 reads a record. User 2 goes and either changes a record or deletes a record and commits the changes. Now if user 1 tries to read the same records again, user 1 gets different data.

• Phantom: A phantom is a record that matches the search criteria but isn't initially seen. For example, say user 1 searches for records and gets some records. Now user 2 adds new rows with user 1's search criteria. Now if user 1 searches again using the same criteria, user 1 gets different results than the previous one.

Table A-2 describes the isolation levels defined by OLE DB.

Table A-2: Isolation Levels

LEVEL	DEFINITION
Read Uncommitted	A user can read uncommitted changes made by other users. At this level all three cases (dirty reads, nonrepeatable reads, and phantoms) are possible.
Read Committed	A user can't see changes made by other users until the changes are committed. At this level of isolation, dirty reads aren't possible, but nonrepeatable reads and phantoms are possible.
Repeatable Read	A user can't see the changes made by other users. At this level of isolation, dirty reads and nonrepeatable reads aren't possible, but phantoms are possible.
Serializable	A user sees only changes that are finalized and committed to the database. At this isolation level, dirty reads, nonrepeatable reads, and phantoms aren't possible.

Note

The definitive book on transaction processing is Concurrency Control and Recovery in Database Systems by Philip A. Bernstein, Vassos Hadzilacos, and Nathan Goodman (Addison-Wesley, 1987).You can download it in its entirety as a 22.9MB self-extracting ZIP file from http://research.microsoft.com/pubs/ccontrol/. You can also find related information at http://research.microsoft.com/~philbe/.

Table A-3 summarizes the data consistency behaviors and isolation level relationship. (Read Committed is SQL Server's default isolation level.)

Table A-3: Isolation Levels and Data Consistency

LEVEL	DIRTY READ?	NONREPEATABLE READ?	PHANTOM?
Read Uncommitted (0)	Yes	Yes	Yes
Read Committed (1)	No	Yes	Yes
Repeatable Read (2)	No	No	Yes
Serializable (3)	No	No	No

Locking Modes

In general, locking avoids the consequences of multiple users accessing the same data simultaneously and tries to maintain the data's consistency and integrity. There are two locking modes: shared and exclusive. The shared mode lets multiple users access the same data simultaneously. However, an exclusive mode won't let user 2 access the data until user 1 unlocks the data. Depending on the database, you can implement locking on a record, a table, or a page level. In table locking, a user can lock the entire table until he commits the changes and unlocks the table. The same method applies on a page as well as on a record for page-level and record-level locking.

Note

Chapters 4 and 5 discuss ADO.NET concurrency.

Based on these general locking modes, ADO provides two different types of locking on recordsets and the combination of them: optimistic locking and pessimistic locking. Optimistic locking is based on the assumption that there won't be any other users when a user is accessing some records of a table.

In optimistic locking, locking only occurs during the final update of records. If any changes were made to the data since it was last accessed, the application must read data again and update the data.

In pessimistic locking, records are unavailable to other users when a user is accessing records until the user makes the final changes.

You can pass an argument when you create a recordset to specify the type of locking. Table A-4 describes these values.

Table A-4: Locking Types

CONSTANT	VALUE	DESCRIPTION
adLockReadOnly	1	Read-only
adLockPessimistic	2	Pessimistic locking
adLockOptimistic	3	Optimistic locking
adLockBatchOptimistic	4	Optimistic locking with batch updates

You set the LockType variable in the recordset to 1 when you need to read data. It doesn't allow you to add or update data.

You use LockType=3, or optimistic locking, when you need to lock records and only when ADO physically updates the record. Otherwise, records are available when you're editing them. In contrast to the optimistic locking, pessimistic locking locks the records when you start editing records. You set LockType=2 in that case. Before applying optimistic locking, you need to make sure that database manufacturer supports it. Some database manufacturers don't support optimistic locking.

Optimistic locking with batch updates, LockType=4, enables you to access multiple records, update them locally, and update the database as a single batch operation. Again, before using this type of locking, you need to make sure the database manufacturer supports this type of locking. Many don't.

Note

For more information on locking and concurrency levels, you may want to read Jim Gray's article on isolation levels at http://research.microsoft.com/~gray/Isolation.doc or Transaction Processing: Concepts and Techniques by Jim Gray and Andreas Reuter (Morgan Kaufmann, 1993).

What Are Deadlocks?

If you've heard about concurrency and different kinds of locks, you've probably also heard about deadlocks. Unfortunately, they're an inevitable fact of life in the database world. But, fortunately, today's Database Management Systems (DBMSs) are typically designed to handle deadlocks—usually by selecting a "victim" whose transaction gets rolled back (or undone).

When users share access to database tables, they may prevent each other from completing transactions. That's because a user who locks a table or a single record during an update may prevent other transactions from acquiring the locks they need to complete their task. As a result, the other transactions enter a wait state, waiting their turn. Sometimes, the locks are unresolvable, though. For example, if transaction A can't complete until it acquires a lock being used by transaction B, and transaction B can't complete until it acquires a lock being used by transaction A, the transactions enter a deadlock state and neither can complete unless the other is terminated.

Microsoft describes deadlocks by noting the following: "A deadlock occurs when there is a cyclic dependency between two or more threads for some set of resources." It adds that deadlocks can occur on any system with multiple threads, not just on a relational DBMS.