Let’s finish up now. We’ll try to perform the same update on connection 1 and watch the deadlock happen. Each user is waiting for the other’s work to complete. (Before you run this last command, you might want to check and see that the globe on Connection 2 is still spinning.)

-- From  Connection 1
update   publishers
    set  state = “MA”
  where pub_id = “0736”
commit tran

Now you have to go to the Results tab for each of the windows to see what happened. You’ll notice the globe finally stopped spinning. On one connection, you will see this:

(1   row(s)   affected)

That command executed. The other one failed.

Msg  1205,  Level  13,  State  2
Your  server  command  (process  id  10)  was  deadlocked  with  another
process
and  has  been  chosen  as  deadlock  victim.  Re-run  your  command.

That’s the end of the demonstration. The server automatically handled the deadlock, allowing one user’s work to complete. (It’s kind of anticlimactic because everything is suddenly cleaned up with no muss or fuss from the server.)

HOLDLOCK is usually an ineffective way to build systems. It provides very high concurrency and integrity for read-only applications, but if users tend to read and then write rows frequently, performance and acceptance problems will arise. Performance will suffer as the number of deadlocks increases, and users will become frustrated if deadlocks cause them to have to re-enter their work.

The biggest problem with the holdlock solution (compared to the optimistic locking solution) has to do with the locking level. SQL Server locks at the page level, not at the row level.

---

Note:  With MSSQL 6.5, SQL Server now supports row-level locking for insert on tables where you request it. But this section is focusing on updates, where all locks are at the page level.

---

Because there’s only page-level locking, you can’t tell the difference between a deadlock caused by a collision on a particular row, and one caused by a collision on a page where different rows are involved. Each requires a user or programmer to re-execute a query. In some environments, the impact of page-level locking is reduced by padding the rows so that each row occupies an entire page. The additional cost of storage, the performance penalty during SELECT operations, and the added overhead to the transaction log for every update make this approach impractical most of the time.

The bottom line is, avoid HOLDLOCK in most situations. It leads to deadlocks, and there are better ways to handle concurrency.

Persistent Exclusive Locks

In order to reduce deadlocks, some programmers obtain a persistent exclusive lock before reading the row. This is done by updating the row within a transaction and then reading the row. To obtain this lock, you execute BEGIN TRAN followed by an UPDATE statement and then retrieve the row with SELECT. Here’s how you would acquire that lock with the publishers table:

begin   tran
update     publishers
      set  state  =  state
 where id = “0736”
select  pub_id,  pub_name
    from  publishers
 where id = “0736”

Notice that the UPDATE statement sets the value of the state column to its original value. This update does not actually change the value of the row, but it still acquires an exclusive lock on the page that persists until the transaction is committed or rolled back.

Figure 20.4 shows how this locking method provides a user with an exclusive lock.

Figure 20.4.  UPDATE statements in transactions generate a persistent exclusive lock on the data page.

What happens to a user who tries to select a row after another user acquires an exclusive lock on that page? The new reader waits for a blocking lock to clear without an implicit timeout and without an error message.

This approach eliminates deadlocks in the data pages, but at the cost of concurrency. How long could a user hold a blocking lock preventing a user from reading a row? As long as he holds the data on his screen, which could be forever.

---

Note:  Imagine this scenario: John retrieves the data for the company’s largest customer, makes some changes, and clicks OK. The application asks, “Are you sure?”, but John is late for his plane to Cancun, so he doesn’t notice the message and leaves without releasing any locks. All transactions for this customer are blocked by his exclusive lock.

John enjoys the sunny south for two peaceful weeks, but his coworkers are losing their jobs because they don’t know the KILL command (to kill a connection, not a coworker). John returns back to work, clicks the Yes button, and orders start to ship again. (The good news: John gets a promotion for solving the biggest problem of the year. The bad news: He’s the only one left to do all the work.)

---

This approach provides absolute data integrity with no deadlocks and no danger of data loss. On the other hand, individuals attempting to read data will be locked out. The scope of those locks goes beyond the actual row affected by the UPDATE statement: it also affects every other row on that page. What’s more, the additional UPDATE statement isn’t free; there’s extra work to do in memory, and there will be a lot of additional rows in the transaction logs.

Manual Check-Out

Some users require an active row-level locking system, so they build their own check-out/check-in system to handle row locking. This is often implemented with an additional column that identifies the owner of the lock and the time of check-out. The locks expire after a defined length of time.

I won’t go into the coding details for this method. You need to understand that this approach adds a ton of overhead to every data manipulation, and there are serious problems associated with maintenance of locks after server shutdowns and similar events.

Let’s face it, the programmers of SQL Server cheated in every way they could think of so that the locking system would create the lowest possible overhead. They wrote all of the code to support locking in C, not SQL. They put the locking table in a static area in memory, not in the regular data cache, and certainly not on disk. The locking table is automatically eradicated every time the server restarts (it’s in memory, right?), so there’s no additional maintenance work there. You probably won’t be able to write a more efficient system than theirs, so don’t try. If you really need row-level locking, read the next section on optimistic locking.