How to explicitly lock a table in Microsoft SQL Server (looking for a hack - uncooperative client)

Question

This was my original question:

I am trying to figure out how to enforce EXCLUSIVE table locks in SQL Server. I need to work around uncooperative readers (beyond my control, closed source stuff) which explicitly set their ISOLATION LEVEL to READ UNCOMMITTED. The effect is that no matter how many locks and what kind of isolation I specify while doing an insert/update, a client just needs to set the right isolation and is back to reading my garbage-in-progress.

The answer turned out to be quite simple -

while there is no way to trigger an explicit lock, any DDL change triggers the lock I was looking for.

While this situation is not ideal (the client blocks instead of witnessing repeatable reads), it is much better than letting the client override the isolation and reading dirty data. Here is the full example code with the dummy-trigger lock mechanism

WINNING!

#!/usr/bin/env perl

use Test::More;

use warnings;
use strict;

use DBI;

my ($dsn, $user, $pass) = @ENV{ map { "DBICTEST_MSSQL_ODBC_$_" } qw/DSN USER PASS/ };

my @coninf = ($dsn, $user, $pass, {
  AutoCommit => 1,
  LongReadLen => 1048576,
  PrintError => 0,
  RaiseError => 1,
});

if (! fork) {
  my $reader = DBI->connect(@coninf);
  $reader->do('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED');

  warn "READER $$: waiting for table creation";
  sleep 1;

  for (1..5) {
    is_deeply (
      $reader->selectall_arrayref ('SELECT COUNT(*) FROM artist'),
      [ [ 0 ] ],
      "READER $$: does not see anything in db, sleeping for a sec " . time,
    );
    sleep 1;
  }

  exit;
}

my $writer = DBI->connect(@coninf);

eval { $writer->do('DROP TABLE artist') };
$writer->do('CREATE TABLE artist ( name VARCHAR(20) NOT NULL PRIMARY KEY )');
$writer->do(do('DISABLE TRIGGER _lock_artist ON artist');

sleep 1;

is_deeply (
  $writer->selectall_arrayref ('SELECT COUNT(*) FROM artist'),
  [ [ 0 ] ],
  'No rows to start with',
);

$writer->begin_work;

$writer->prepare("INSERT INTO artist VALUES ('bupkus') ")->execute;
# this is how we lock
$writer->do('ENABLE TRIGGER _lock_artist ON artist');
$writer->do('DISABLE TRIGGER _lock_artist ON artist');

is_deeply (
  $writer->selectall_arrayref ('SELECT COUNT(*) FROM artist'),
  [ [ 1 ] ],
  'Writer sees inserted row',
);

# delay reader
sleep 2;

$writer->rollback;

# should not affect reader
sleep 2;

is_deeply (
  $writer->selectall_arrayref ('SELECT COUNT(*) FROM artist'),
  [ [ 0 ] ],
  'Nothing committed (writer)',
);

wait;

done_testing;

RESULT:

READER 27311: waiting for table creation at mssql_isolation.t line 27.
ok 1 - READER 27311: does not see anything in db, sleeping for a sec 1310555569
ok 1 - No rows to start with
ok 2 - Writer sees inserted row
ok 2 - READER 27311: does not see anything in db, sleeping for a sec 1310555571
ok 3 - READER 27311: does not see anything in db, sleeping for a sec 1310555572
ok 3 - Nothing committed (writer)
ok 4 - READER 27311: does not see anything in db, sleeping for a sec 1310555573
ok 5 - READER 27311: does not see anything in db, sleeping for a sec 1310555574

Solution 1

One hack hack hack way to do this is to force an operation on the table which takes a SCH-M lock on the table, which will prevent reads against the table even in READ UNCOMMITTED isolation level. Eg, doing an operation like ALTER TABLE REBUILD (perhaps on a specific empty partition to reduce performance impact) as part of your operation will prevent all concurrent access to the table until you commit.

Solution 2

Add a locking hint to your SELECT:

SELECT COUNT(*) FROM artist WITH (TABLOCKX)

and put your INSERT into a transaction.

If your initial statement is in an explicit transaction, the SELECT will wait for a lock before it processes.

Solution 3

There's no direct way to force locking when a connection is in the READ UNCOMMITTED isolation level.

A solution would be to create views over the tables being read that supply the READCOMMITTED table hint. If you control the table names used by the reader, this could be pretty straightforward. Otherwise, you'll have quite a chore as you'll have to either modify writers to write to new tables or create INSTEAD OF INSERT/UPDATE triggers on the views.

Edit:

Michael Fredrickson is correct in pointing out that a view simply defined as a select from a base table with a table hint wouldn't require any trigger definitions to be updatable. If you were to rename the existing problematic tables and replace them with views, the third-party client ought to be none the wiser.

Comments

[zinglon]

Since his problem is with isolation levels, just using WITH (READCOMMITTED) as the hint should suffice.

[JNK]

@Peter - Are you not able to change the queries?

[Peter Rabbitson]

Which part of closed source 3rd party software wasn't clear? :)))

[Michael Fredrickson]

I agree... this is a good solution for the requirements. Rename the artist table to tblArtist, then simply create a view named artist around the table tblArtist and use a locking hint in the view to override what the client is doing.

[Peter Rabbitson]

@M. Fredrickson: The client both reads and writes to said tables, a view isn't viable either.

[Michael Fredrickson]

@Peter: It doesn't make it any less of a hack.. but this view would meet the requirements for an "Updatable View", and can be inserted / updated / deleted.

[Peter Rabbitson]

Oooooh now this is something I like. Will experiment with after hours, and report back.

[Peter Rabbitson]

And this is indeed how to do it, and it is not even too hacky. I would even say quite elegant :) See edit of original question for answer

[Seph]

+1 for Updatable View answer, entirely answers the question without using behaviour that might later change (DDL changes). In systems I work with if we must provide direct SQL access it's exclusively through views and we make sure that we have triggers in-place to validate the data before it updates the tables.

[Sudhanshu Mishra]

+1 because I was looking for a way to simulate a timeout on a read caused by a locked table! (This is for a unit test)