How to force SELECT blocking on SQL server?

Question

I see so much information about avoiding blocks. My situation is that I WANT blocks.

We have this table with which two separate processes will be communicating with each other. The processes will run at random times and will use this control table to understand if the other process is busy. Both processes can't be busy at the same time, hence the control table.

Each job, when run, will check the control table... and based on that data will decide whether it's OK to run, and if OK, will update the control table record.

The issue is that if both processes run at the same moment, it's not clear that they won't do the following undesired actions (in this exact order):

1. Proc A reads the control table (table says good to go)
2. Proc B reads the control table (table says good to go)
3. Proc A updates control table (table now says "Proc A is busy")
4. Proc B updates control table (table now says "Proc B is busy")

<- In that scenario, both processes think they successfully updated the control table and will start their main flow (which is NOT what we want)

What I want here is for Proc B to be BLOCKED from SELECTING (not just updating) from the control table. This way, if/when Proc B's select query finally works, it will see the updated 'busy' value, not the value that existed before being changed by Proc A.

We're using SQL Server 2008 R2 I believe. I checked out SERIALIZABLE isolation but it doesn't appear to be strong enough.

For what it's worth we're trying to accomplish this with JDBC... using conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

Which we understand to be the highest level of isolation, but I can still run selects all day from another window.

I'm 100% sure this is nowhere near a unique problem.... does anyone have any suggestion for making this work?

Answer 1

Your approach can work, but there are a few things to consider:

1. You need to open a transaction in the very beginning (before the first read) and you must only commit it after you have finished your work.

2. If both A and B try to read/modify the same record, this will work out of the box, even with the default transaction isolation level (READ COMMITTED). Otherwise, you need to tell SQL Server to lock the whole table (using the TABLOCK hint).

3. In fact, you don't need the reads at all!

This is how it will work:

     P1                 P2
 ---------------------------------
  BEGIN TRANS
                     BEGIN TRANS
  WRITE (success)
                     WRITE (blocked)
  do work            |
  .                  |
  .                  | 
  COMMIT     ->      block released, WRITE finishes

                     do work
                     . 
                     .
                     COMMIT

---

PS: Note, though, that SQL server supports application locks. Thus, if you just want to synchronize two processes, you don't need to "abuse" a table:

• Implementing application locks within SQL Server (Distributed Locking Pattern)

PPS: For completeness, let me also answer the question in the title ("How to force SELECT blocking on SQL server?"): For this, you can use a combination of the HOLDLOCK and the XLOCK table hint (or TABLOCKX, if you want to exclusively lock the whole table).

Answer 2

If you need the read (because you want to some processing) I would do the following:

Set transaction isolation level serializable
begin transaction
 select from tablea  
 update tablea
commit