June 28, 2006 at 9:48 am
I'm having a little trouble getting transaction isolation level to work, so I'm asking for a little help. I want to leave the last committed version of the data available to other connections whI'm having a little trouble getting transaction isolation level to work, so I'm asking for a little help. I want to leave the last committed version of the data available to other connections while I modify the data, and then as soon as I commit, have that data available to other connections. In the meantime, however, I don't want to lock other connections that access data that I'm modifying - I want them to see previously committed data. What transaction isolation level allows me to do this?
Also, I'm beginning my transaction by saying:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION SSIS_Hourly_Import
Is this the proper way to set the transaction isolation level for my connection? Thanks for any help you can provide!
ile I modify the data, and then as soon as I commit, have that data available to other connections. In the meantime, however, I don't want to lock other connections who access data that I'm modifying - I want them to see previously committed data. What trasaction isolation level allows me to do this?
Also, I'm beginning my transaction by saying:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION SSIS_Hourly_Import
Is this the proper way to set the transaction isolation level for my connection? Thanks for any help you can provide!
Ryan
June 28, 2006 at 11:28 am
I figured out my own problem. The first thing was that I didn't get that transaction levels apply to the data reader, not the transaction owner, so it really didn't matter what level I set when I began my transaction - as long as my reader was set to READ COMMITTED, I wasn't going to be able to read previously committed data.
The second was that I was really looking for SNAPSHOT isolation, which is described in depth in these two articles:
Part 1: http://www.code-magazine.com/Article.aspx?quickid=0407071
Part 2: http://www.code-magazine.com/article.aspx?quickid=0501061
SNAPSHOT isolation level allows other processes to continue to read the last committed version of the data, even while you're updating it. Read the articles if you're interested in this isolation level, which is new to SQL Server 2005.
Ryan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply