June 14, 2013 at 6:56 am
Instead of placing the database into single user mode, while we load new data, I am trying to figure out the SQL command: SET TRANSACTION ISOLATION LEVEL, but there is not a lot of information on the MS page. (only one example)
What I would like to do is put the locks in place (so we can ovoid the deadlocks we are getting with users during the load process, since it always kills the stored proc).
From what I am reading.. the SQL would look something like..
SET TRANSACTION ISOLATION LEVEL TABLOCK HOLDLOCK
GO
BEGIN TRANSACTION
(CURRENT SQL CODE)
END TRANSACTION
Is that correct?? Wouldn't I also need to change the transaction isolation back to normal?
June 14, 2013 at 7:12 am
SET TRANSACTION ISOLATION LEVEL is used to set one of the 5 isolation levels:
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
SNAPSHOT
The syntax is described at http://msdn.microsoft.com/en-us/library/ms173763.aspx
What you listed there are locking hints which go into a query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2013 at 8:06 am
Thanks for clearing that up Gail... That leave the following questions...
1) the example shows a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
. What does it mean by session, when the stored proc ends (in this case) or when all the steps in the Job complete?
2) If you use a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
(http://msdn.microsoft.com/en-us/library/aa213026(v=sql.80).aspx)
then use a hint to create table locks.. those locks would remain until the commit. Correct?
3) Can you use hints when you create a table... so users can not access it until the data load and indexing has taken place? Or must it be in the Insert Into statement only. (Given the page it looks like you can not. So my guess is that you would have to write a bogus line, and delete, to get table lock placed)
June 14, 2013 at 8:14 am
dwilliscp (6/14/2013)
1) the example shows a set... begin.. comit. So once the commit is done the set command ends?
No, it's a session-level setting. It persists til you set otherwise or close the connection
2) If you use a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
(http://msdn.microsoft.com/en-us/library/aa213026(v=sql.80).aspx)
then use a hit to create table locks.. those locks would remain until the commit. Correct?
Hit?
3) Can you use hits when you create a table... so users can not access it until the data load and indexing has taken place? Or must it be in the Insert Into statement only. (Given the page it looks like you can not. So my guess is that you would have to write a bogus line, and delete, to get table lock placed)
Hits?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2013 at 8:18 am
Gail sorry found the section at the bottom, and changed my last post... but you had already read what I posted before. Opps.. hit should have been hint. I cleaned up my earlier post.
June 14, 2013 at 8:20 am
If you are running under serialisable isolation, all locks are held until the end of the transaction (and there will be a lot of locks). Hints go on queries, not on tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2013 at 8:26 am
Thanks Gail, I was afraid of that.
One more question... (SET TRANSACTION ISOLATION LEVEL SERIALIZABLE)
In my case what does it mean by session, when the stored proc ends or when all the steps in the Job complete?
June 14, 2013 at 8:34 am
When the connection that ran the SET closes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply