title: How to explicitly lock a table in Microsoft SQL Server (looking for a hack - uncooperative client)
body: <p>This was my original question:</p>
  
  <p><i>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.</i></p>
  
  <p>The answer turned out to be quite simple -</p>
  
  <blockquote>
    <p>while there is no way to trigger an explicit lock, any DDL change triggers the lock I was looking for. </p>
  </blockquote>
  
  <p>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</p>
  
  <p>WINNING!</p>
  
  <pre>
  #!/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;
  
  
  
  </pre>
  
  <h2>RESULT:</h2>
  
  <pre>
  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
  </pre>