SQL Server Express和SQL Server 2005上的事务隔离级别和从多个表中读取数据

Question

我有一个数据库，其中包含一个主表（我们称之为Owner），以及多个子表，包括汽车、书籍等持有物品的信息。例如：

- Owner 表包含列：owner_id, name - Cars 表包含列：owner_id（外键），brand - Books 表包含列：owner_id（外键），title, author 我的程序需要使用第三方库计算统计数据，例如“拥有 BMW 的车主中，同时也拥有《哈利·波特》的人数”。我想要同时从所有表中读取所有行，并在非 SQL 代码中进行分析。

我希望使用各自独立的“Select * From X”语句来读取所有表。由于一次大型联接将返回过多行（而不是 owner + cars + books），因此我不能使用一个大联接。Union 也不可行，因为这些表包含不同的列和不同的类型。

我已经设置好了……

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

但是我仍然遇到一些问题。

如果我通过运行两个线程来压力测试数据库，一个线程随机插入或删除，另一个线程读取，有时会出现不一致的结果，比如在读取Owners和Cars表之间，Cars已经被删除了。

我有几个问题：

1. What's the proper way of preventing modification when reading from multiple tables one by one? No table must be modified until all have been read.

   I'm using SQL Server 2005 (on network) and SQL Server 2005 Express (local). Can I explicitly get locks on multiple tables at the same time?

2. If I run against my local SQL Server Express database, I can't make it work no matter what I do. If I run against my networked SQL server 2005 database, I can make it work (with some effort). Does SQL Server Express support transaction isolation level SERIALIZABLE? I believe it should. The differences could be due to a slow network connection but I don't know.

   On my local db, I can not prevent modification in between reads. That is, one thread is randomly deleting a random owner (first cars, then books, then owner) or inserting a new owner (insert owner, insert 2 cars, insert 2 books). Another thread is reading using:

   Begin Tran
   Select owner_id From Owner
   Select owner_id, brand From Cars
   Select owner_id, title, author From Books
   Commit Tran
   

   No matter what I do, sometimes I get an owner with zero cars or zero books. This should never happen since all inserts and deletes are in a single transaction. I seems like the express server doesn't lock Owner, Cars and Books statements at the same time.

   On the networked SQL Server 2005, it works fine but it could be because of a slow connection and thus lower probability of simultaneous execution.

3. On my local db, I am starting every transaction with a dummy Select from all tables to prevent deadlocking. I don't understand why this prevents deadlocking but not modification of the tables. This is not necessary on the networked SQL Server 2005.

目前，我无法确定自己是否对事务隔离有误解，还是SQL Server Express和SQL Server 2005之间存在差异导致的问题。如果有任何帮助或见解，将不胜感激。

- athoren

Answer 1

您选择一次性加载所有数据的选项意味着很少的选择：

• 使用 sp_getapplock 来串行访问相关代码
• 在事务中对读取使用 TABLOCKX、HOLDLOCK

您会遇到问题，因为 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 只影响锁的隔离级别：你需要控制持续时间（HOLDLOCK）和粒度+模式（TABLOCKX）。

- gbn

---

谢谢，sp_getapplock似乎可以帮助。 - athoren

我使用了sp_getapplock选项，因为它是最容易实现的。虽然它无法保护我免受其他应用程序修改数据库的影响，但目前这不是一个问题。感谢您解释了事务隔离的工作原理。 - athoren

Answer 2

有时候我会得到不一致的结果，

除非你之后要对一个否则不使用的数据库进行批处理，否则最好习惯于某些波动，这些波动无论如何都不重要。

除非你只有很少的条目，否则这些变化在绝对数字上并不重要。你总是在处理统计数据。使用READ COMMITED，并通过接受数据集不是静态的事实来处理不一致性。

其他任何方法都会完全破坏性能。

或者选择批处理。

另外：使用快照来封存数据库的“时间视图”。

- TomTom

---

实际上，这很重要。某些组合（例如没有汽车的所有者）永远不应该发生，并且将被视为错误。只要我的快照在内部一致，数据是否更改并不重要。 - athoren

其实这并不重要。统计不一致性本来就是处理统计数据的一部分。当你处理30万个数据点时，2个错误的数据有多重要？根本不重要。 - TomTom

有些组合永远不应该发生。如果它们确实发生了，我们就会面临巨大的问题。得到错误的正面结果是不可接受的，但错过可能的错误会更糟。所以，这很重要。 - athoren