如何在Microsoft SQL Server中显式锁定表格（寻找黑客 - 不合作的客户）

Question

如何在Microsoft SQL Server中显式锁定表格（寻找黑客 - 不合作的客户）

9

这是我的原始问题：

我正在尝试强制执行SQL Server中的独占表锁。我需要解决不配合的读者（超出我的控制范围，闭源东西），他们明确将隔离级别设置为READ UNCOMMITTED。效果是，无论我在插入/更新时指定多少锁定和什么类型的隔离，客户端只需要设置正确的隔离级别就可以重新阅读我的垃圾进度。

答案其实很简单 -

虽然没有办法触发显式锁定，但任何DDL更改都会触发我正在寻找的锁定。

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

胜利！

#!/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 ] ],
  '没有行开始',
);

$writer->begin_work;

$writer->prepare("INSERT INTO artist VALUES ('bupkus') ")->execute;
# 这是我们锁定的方式
$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看到插入的行',
);
# 延迟读者
sleep 2;

$writer->rollback;
# 不应影响读者
sleep 2;
is_deeply (
  $writer->selectall_arrayref ('SELECT COUNT(*) FROM artist'),
  [ [ 0 ] ],
  '没有提交的内容（writer）',
);
wait;
done_testing;

RESULT:

读者27311：正在等待在mssql_isolation.t文件的第27行创建表格。测试1 - 读者27311：没有在数据库中看到任何内容，休眠1秒钟（时间戳为1310555569）。测试1 - 没有开始行。测试2 - 写入者看到插入的行。测试2 - 读者27311：没有在数据库中看到任何内容，休眠1秒钟（时间戳为1310555571）。测试3 - 读者27311：没有在数据库中看到任何内容，休眠1秒钟（时间戳为1310555572）。测试3 - 没有提交（写入者）。测试4 - 读者27311：没有在数据库中看到任何内容，休眠1秒钟（时间戳为1310555573）。测试5 - 读者27311：没有在数据库中看到任何内容，休眠1秒钟（时间戳为1310555574）。

- Peter Rabbitson

3个回答

7

一种 hack hack hack 的方法是强制对表进行一个操作，该操作会在表上获取SCH-M锁，即使在READ UNCOMMITTED隔离级别下也会防止对表的读取。例如，在操作中执行ALTER TABLE REBUILD（可能是在特定的空分区上以减少性能影响），将防止在提交之前所有并发访问该表。

- stevehem

哦，这是我喜欢的东西。下班后会尝试一下，并回报结果。 - Peter Rabbitson

这确实是如何做到的，而且甚至不会太过hacky。我甚至会说相当优雅 :) 有关答案，请查看原问题的编辑。 - Peter Rabbitson

4

当一个连接处于READ UNCOMMITTED隔离级别时，没有直接的方法可以强制锁定。

解决方案是在被读取的表上创建视图，并提供READCOMMITTED表提示。如果您控制读者使用的表名，这可能非常简单。否则，您需要修改写入程序以写入新表，或在视图上创建INSTEAD OF INSERT/UPDATE触发器。

编辑：

迈克尔·弗雷德里克森指出，仅定义为从基础表选择的视图与表提示不需要任何触发器定义即可更新。如果您将现有的问题表重命名并用视图替换它们，则第三方客户端应该不会注意到。

- zinglon

1

我同意...这是满足需求的好解决方案。将艺术家表重命名为tblArtist，然后简单地创建一个围绕tblArtist表的视图，并在视图中使用锁定提示来覆盖客户端正在执行的操作。 - Michael Fredrickson

@M. Fredrickson：客户端既要读取又要写入这些表，视图也不可行。 - Peter Rabbitson

3

@Peter：虽然这仍是一种“hack”，但这个视图可以满足“可更新视图”的要求，可以进行插入/更新/删除操作。 - Michael Fredrickson

+1 对于“可更新视图”答案，完全回答问题而不使用可能会更改的行为（DDL 更改）。在我工作的系统中，如果我们必须提供直接的 SQL 访问，那么它必须是通过视图进行的，并且我们确保在更新表之前有触发器来验证数据。 - Seph

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- JNK · Accepted Answer

给你的 SELECT 添加一个锁定提示：

SELECT COUNT(*) FROM artist WITH (TABLOCKX)

并将你的 INSERT 放入事务中。

如果你的初始语句在一个显式事务中，那么在处理之前，SELECT 会等待锁定。