我试图弄清楚如何在sql Server中强制执行EXCLUSIVE表锁.我需要解决不合作的读者(我无法控制,闭源的东西),这些读者明确地将他们的ISOLATION LEVEL设置为READ UNCOMMITTED.结果是,无论我在执行插入/更新时指定了多少锁和什么样的隔离,客户端只需要设置正确的隔离并返回读取我正在进行的垃圾.

答案结果很简单 –

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

虽然这种情况并不理想(客户端阻塞而不是目击可重复读取),但它比让客户端覆盖隔离和读取脏数据要好得多.这是带有虚拟触发器锁定机制的完整示例代码

赢了！

#!/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,$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'),'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'),'Nothing committed (writer)',);

wait;

done_testing;

结果：

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