CODEWITHSUNDEEP
sqlsql-servertransactions
Baso
Baso

Reputation: 1354

How to prevent transaction locking in sql server for all the connections?

Hello

I have a Sql Server Database that is accessed from two different applications, one of these application just Select from the Database (lets name it x) and the other one (name it y) is doing everything. My problem here is while the y application is inserting or updating the database through a transaction the x application is not able to read from the database.

I'v read about transaction isolation level and found that the SNAPSHOT isolation level would be good for my case, but I don't know how to make the default isolation level for the database is SNAPSHOT so that I don't have to specify the isolation level for every transaction or every connection.

In a nutshell: I want to read from the database through an application while there is a transaction from a different application is running.

Thanks

Upvotes: 1

Views: 2720

Answers (2)

M.Ali
M.Ali

Reputation: 69524

To be able to read the last committed data to the disk, if data is being modified Snapshot Isolation is a good solution.

You can enable snapshot isolation by executing the following command. 

ALTER DATABASE Database_Name 
SET READ_COMMITTED_SNAPSHOT ON;
GO

Mind it, it will use your Temp db extensively, make sure your Tempdb is on a drive with plenty of free space.

On the other hand if Application A only reads then you might look into a different solution, like having altogether a separate database for reads only, you can have Log-shipping set to have a read-only copy of that database or maybe Database Replication.

Upvotes: 1

Dave.Gugg
Dave.Gugg

Reputation: 6771

Changing the isolation level is pretty simple:

ALTER DATABASE dbname SET READ_COMMITTED_SNAPSHOT ON;

However, you need to really, really make sure you are aware of the consequence of the different isolation levels. I'd suggest this Brent Ozar (by Kendra Little) article as a starting point.

Upvotes: 1

Related Questions