事务隔离和从SQL Server Express和SQL Server 2005上的多个表中读取 [英] Transaction isolation and reading from multiple tables on SQL Server Express and SQL Server 2005

问题描述

我有一个数据库，其中包含一个主表(称为所有者)和几个具有馆藏的子表(例如汽车，书籍等).

I have a database with a main table (lets call it Owner) and several sub tables with holdings (like Cars, Books etc).

例如:

• Owner具有列:owner_id, name
• Cars具有列:owner_id (foreign key), brand
• Books具有列:owner_id (foreign key), title, author

• Owner has columns: owner_id, name
• Cars has columns: owner_id (foreign key), brand
• Books has columns: owner_id (foreign key), title, author

我的程序应该使用各种第三方库来计算统计数据，例如多少宝马车主还拥有一本《哈利波特》书.我想同时读取所有表中的所有行，然后以非SQL代码进行分析.

My program should calculate statistics like How many BMW owners also owns a Harry Potter book using various third party libraries. I want to read all rows from all tables at the same time and then do the analysis in non-sql code.

我想使用单独的Select * From X语句读取所有表.我不能使用一个大联接，因为它将返回太多行((所有者*汽车*书籍)，而不是(所有者+汽车+书籍)).联盟也不会削减它，因为表包含不同类型的不同列.

I want to read all tables using separate Select * From X statements. I cannot use one big join since it would return too many rows ((owners * cars * books) instead of (owners + cars + books)). A Union doesn't cut it either since the tables contain different columns of different types.

我已经设置

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

但是我还是遇到了一些问题.

but I'm having some issues anyway.

如果我通过运行两个线程来强调数据库，一个线程随机地插入或删除，而另一个读取有时会出现不一致的结果，例如在读取Owners和读取Cars表之间已删除了Cars的结果.

If I stress the database by running two threads, one randomly inserting or deleting and the other reading I sometimes get inconsistent results, like Cars have been deleted between reading Owners and reading the Cars table.

我有几个问题:

1. 从一张表中一张一张地读取时，防止修改的正确方法是什么?在读取所有表之前，不得修改表.

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.

我正在使用SQL Server 2005(在网络上)和SQL Server 2005 Express(在本地).我可以同时显式地在多个表上获得锁吗?

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?

如果我对本地SQL Server Express数据库运行，无论做什么我都无法使其正常工作.如果我在联网的SQL Server 2005数据库上运行，则可以使它工作(需要付出一些努力). SQL Server Express是否支持SERIALIZABLE事务隔离级别?我相信应该.造成这种差异的原因可能是网络连接速度较慢，但​​我不知道.

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.

在我的本地数据库上，我不能阻止两次读取之间的修改.也就是说，一个线程会随机删除一个随机所有者(先是车，然后是书籍，然后是所有者)，或者插入一个新所有者(插入所有者，插入2辆汽车，插入2本书).另一个线程正在使用:

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

无论我做什么，有时我都会得到一个拥有零辆汽车或零本的所有者.这绝不应该发生，因为所有插入和删除都在单个事务中.我好像快递服务器不能同时锁定Owner，Cars和Books语句.

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.

在联网的SQL Server 2005上，它可以正常工作，但这可能是由于连接速度较慢，从而降低了同时执行的可能性.

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.

在我的本地数据库上，我从所有表中使用虚拟Select开始每个事务，以防止死锁.我不明白为什么这可以防止死锁，但不能防止对表的修改.在联网的SQL Server 2005上这不是必需的.

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之间的差异而引起的.任何帮助或见解将不胜感激.

At the moment, I can't tell if I've misunderstood something about transaction isolation or if it's an issue with differences between SQL Server Express and SQL Server 2005. Any help or insights would be greatly appreciated.

推荐答案

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

Your choice of loading all data in one go means very few options:

• 使用sp_getapplock通过相关代码对访问进行序列化
• 在事务中读取时使用TABLOCKX，HOLDLOCK

您遇到问题，因为SET TRANSACTION ISOLATION LEVEL SERIALIZABLE仅影响锁的隔离:您需要控制持续时间(HOLDLOCK)和粒度 + 模式(TABLOCKX)

You have issues because SET TRANSACTION ISOLATION LEVEL SERIALIZABLE only affects isolation of the locks: you need to control duration (HOLDLOCK) and granularity + mode (TABLOCKX)

这篇关于事务隔离和从SQL Server Express和SQL Server 2005上的多个表中读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！