No Locks

This section will help you understand the need for a more comprehensive locking strategy. What happens if you have two users trying to modify the same row (on the same page) at the same time? I’m not discussing two users concurrently running an UPDATE statement at once—that’s handled automatically through the transaction and locking systems on the server.

What we are talking about is the problem where two users retrieve the same row to a client/server application for editing data, modify the data differently, and then each update the row and send it back. Figure 20.2 shows what happens when two users deplete inventory at the same time for the same item ID, 1234.

Figure 20.2.  When two users take turns updating the same row, some changes can be lost.

The first user depletes inventory by 5 (from 99 to 94); the second user depletes inventory by 6 (from 99 to 93). Each user reports his version of the final inventory value, which is correct for his update.

The automatic locking associated with both SELECT and UPDATE operations does not help resolve this data integrity problem. SELECT statements automatically invoke a shared or read lock, which is released as soon as SQL Server is finished reading the data page. All UPDATE statements acquire exclusive locks, but those are released as soon as the transaction is complete.

Let’s review exactly what the locks look like as two users traverse this SELECT … UPDATE process on the same row at roughly the same time. User 1 retrieves the row with SELECT. The SELECT statement obtained a shared lock, but released it after the row was read by User 1’s process. Understand that the lock is not in place while the user is looking at it in a client application (during the edit). User 2 also retrieves the page. Even if he reads at exactly the same time as User 1, there are no exclusive locks, that is, locks that can block a SELECT statement.

When either user goes back to update the row, there are no locks left around that could block the UPDATE process. If User 1 is the first to update, his update runs in its own transaction space, which is released as soon as the update completes, so User 2 is free to update as well as long as the first update is complete. There are no persistent locks that will block his work. If User 2 should happen to try to execute at the very instant that User 1 is working, he encounters a live lock, which blocks his process indefinitely until User 1’s update process is complete.

When you don’t use a more sophisticated locking method while edits are occurring onscreen, the server only promises you that each statement will be treated as a transaction, and that locking will ensure the consistency of that statement. SQL Server does not promise that multiple statements will be consistent with each other, even if those statements occur in the same batch. We will discover some methods to force the server to handle concurrency better.

No locking provides the broadest data sharing and the best performance (no locks persist to block other users’ work), but risks serious data loss.

Persistent Shared Locks

One solution to the problem is to create persistent shared locks. You see, if the shared locks you acquired when you read the row originally could be retained throughout the edit session, then you would be able to update the row only when the other user had released his locks. There is no possibility that either user can modify the data while the other holds the lock.

To obtain a persistent shared lock, use the HOLDLOCK keyword or optimizer tip inside a transaction when you read the original row. Here’s the code to use HOLDLOCK to create a persistent lock:

begin      transaction
select   id,   descr,   qty
     from  t  holdlock
 where  id  =  1234

This approach only works if you begin a transaction before reading the row. The combination of the BEGIN TRANSACTION statement and the HOLDLOCK keyword makes the shared lock last until the user commits or rolls back the transaction or logs out. The editing protocol would look like this:

-- Read  the  row:
begin    transaction
select  id,  descr,  qty
  from  t  holdlock
 where id = 1234

-- modify  the  row  on  screen  (some  delay  for  editing)

-- Write  the  row:
update   t
     set  descr  =  “abcde”
 where  id  =  1234
commit    transaction

What actually happens if two users try to work on this row at one time? The first user reads the row, obtaining a persistent shared lock on the data page. The second user also reads the row, also obtaining a persistent shared lock. That’s the meaning of a shared lock: it doesn’t lock out other users.

When the first user goes to update the row, he can’t do it. He must wait for the second user’s shared lock to clear. When the second user also attempts an update, he must also wait for the first user’s lock to clear. This is a deadlock (see Figure 20.3).

Figure 20.3.  Using HOLDLOCK, you create locking chains leading to deadlocks and performance problems.

A deadlock occurs when two or more users obtain a chain of blocking locks that cannot be resolved by a single user’s actions. Many systems require an administrator to resolve deadlocks manually, but SQL Server automatically detects deadlocks as they develop and resolves them.

When SQL Server detects a deadlock, it automatically rolls back one of the processes involved, aborting its current batch, releasing its locks, and freeing up the other process(es) to move forward and complete their work. The deadlock victim is chosen according to accumulated CPU time, which will seem pretty random to you when it happens. As a programmer, you can often just re-execute your update query after you receive a deadlock error (error 1205, procedure return code -3), but this can lead to the same problems in data integrity as if you had written the query with no locks at all (see the preceding section).