sql-server - 如何显式锁定 Microsoft SQL Server 中的表(寻找 hack - 不合作的客户端)

Question

这是我最初的问题:

我正在尝试弄清楚如何在 SQL Server 中强制执行 EXCLUSIVE 表锁。我需要解决不合作的读者(超出我的控制范围，闭源的东西)，这些读者明确地将其隔离级别设置为“未提交的读取”。其效果是，无论我在执行插入/更新时指定多少个锁和哪种隔离，客户端只需要设置正确的隔离，然后返回读取我的正在进行的垃圾。

答案其实很简单 -

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

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

胜利!

#!/usr/bin/env perluse 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 readersleep 2;$writer->rollback;# should not affect readersleep 2;is_deeply (  $writer->selectall_arrayref ('SELECT COUNT(*) FROM artist'),  [ [ 0 ] ],  '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 1310555569ok 1 - No rows to start withok 2 - Writer sees inserted rowok 2 - READER 27311: does not see anything in db, sleeping for a sec 1310555571ok 3 - READER 27311: does not see anything in db, sleeping for a sec 1310555572ok 3 - Nothing committed (writer)ok 4 - READER 27311: does not see anything in db, sleeping for a sec 1310555573ok 5 - READER 27311: does not see anything in db, sleeping for a sec 1310555574

Answer 1

执行此操作的一种 hack hack 方法是强制对表执行一个操作，该操作在表上获取 SCH-M 锁，这将阻止对表的读取，即使在 READ UNCOMMITTED 隔离级别也是如此。例如，作为操作的一部分执行诸如 ALTER TABLE REBUILD 之类的操作(可能在特定的空分区上以减少性能影响)将阻止对表的所有并发访问，直到您提交。