Database Locking

Question

Database Locking

TRACEY-320982

SSChampion

Points: 13653
More actions
June 7, 2008 at 7:09 am

#191766

In our SQL 2000 we have blocking all the time....but as we are moving to SQL 2005 i assume it would be better. So i read around and found this article.
http://www.devx.com/codemag/Article/21570/1954?pf=true
The default of the database when install a new DATABASE in SQL 2005 is SQL Server's default isolation level of READ COMMITTED so any select statement will still be blocked by an update.
I looked at the SNAPSHOT option and thought i can change the database to this.
Then i got a bit confused on
USE AdventureWorks
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
SELECT EmployeeID
, BaseRate
FROM HumanResources.Employee
WHERE Title='Network Manager'
Do i have to set every select statement to use
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
Database A i have replicating to DATABASE B (all our bi people use this).
Some of the selects statements go back to Database A because they are not replicated.
So i assuming its DATABASE A i set the mode to SNAPSHOT.
DatabaseA is from our vendors ERP - so changing this to SNAPSHOT - would the transactions have to be changed to include the SET statement.
Any other problems i need to be aware of.
Is there a way to just issue the statements with the
SET TRANSACTION ISOLATION LEVEL SNAPSHOT rather than setting the default to SNAPSHOT.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

akshay1974 SSC-Addicted Points: 444 More actions · Answer 1

You can set snapshot at the database level. This below will change the default of READ_COMMITED TO READ_COMMITED_SNAPSHOT.

ALTER DATABASE MyDatabase

SET READ_COMMITTED_SNAPSHOT ON

More info below.

http://msdn.microsoft.com/en-us/library/tcbchxcb.aspx

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 2

The statement

USE AdventureWorks

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

I do not have to include this in every statement ?

Has anyone been running using snapshot mode and anymore had problems

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 3

Let me see if i can rewrite this.

Im looking at changing the database default to use the new snapshot feature in SQL 2005

So that i do not get any blocks caused by SHARED LOCKS AGAINST UPDATES

So i am researching and found both statements

ALTER DATABASE ADVENTUREWORKS

SET ALLOW_SNAPSHOT_ISOLATION ON

SET READ_COMMITTED_SNAPSHOT ON

I also am seeing that you must include this statement

BEGIN TRANS

SET READ_COMMITTED_SNAPSHOT ON

SELECT * FROM TABLE

(Do you only have to use this statement when you have not issued the statement at the Database level).

--I was testing and did the following:

ALTER DATABASE AdventureWorks

SET READ_COMMITTED_SNAPSHOT ON

ALTER DATABASE AdventureWorks

SET ALLOW_SNAPSHOT_ISOLATION ON

Then looked in select * from sys.dm_tran_version_store see the transactions then i thought i turn it off to test the select without this on with an update and see how it is normally locked up.

but it keeps writing to the version store even though i turned if off

ALTER DATABASE AdventureWorks

SET READ_COMMITTED_SNAPSHOT OFF

ALTER DATABASE AdventureWorks

SET ALLOW_SNAPSHOT_ISOLATION OFF

Any easy explanation of why you do both

statements or just ALLOW_SNAPSHOT_ISOLATION ON or just allow READ_COMMITTED_SNAPSHOT ON

would appreciated so i can determine the correct one to use.

I do not want to have to put code in everystatement as this is a vendor database who supply the code.