CODEWITHSUNDEEP
c#.netsql-server
NDeveloper
NDeveloper

Reputation: 1847

Allow select while transation is running

Here is the code to modify table from in one transatcion. As I know why IsolationLevel Serializable the read is not blocked, but I can't select records from the table. How can I run transaction while not blocking selects from the table ?

TransactionOptions opt = new TransactionOptions();
opt.IsolationLevel = IsolationLevel.Serializable;

using (TransactionScope scope = new TransactionScope(
    TransactionScopeOption.Required, opt))
{             
    // inserts record into table   
    myObj.MyAction();

    // trying to select the table from Management Studio                

    myObj2.MyAction();

    scope.Complete();
}

Upvotes: 2

Views: 2198

Answers (3)

Mitch Wheat
Mitch Wheat

Reputation: 300549

Serializable is the highest transaction level. It will hold the most restricted locks.

What are you trying to protect with an isolation level of Serializable.

Read Commited Snapshot might be more appropriate, but we would need more information to be sure.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

It doesn't matter what isolation level your (insert, update, etc) code is running under - it matters what isolation level the SELECT is running under.

By default, this is READ COMMITTED - so your SELECT query is unable to proceed whilst there is *un*committed data in the table. You can change the isolation level that the select is running under using SET TRANSACTION ISOLATION LEVEL to allow it to READ UNCOMMITTED. Or specify a table hint (NOLOCK).

But whatever you do, it has to be done to the connection/session where the select is running. There's no way for you to tell SQL Server "Please, ignore the settings that other connections have set, just break their expectations".

If you generally want selects to be able to proceed on a database wide basis, you might look into turning on READ_COMMITTED_SNAPSHOT. This is a global change to the database - not something that can or should be toggled on or off for the running of a single statement or set of statements, but it then allow READ COMMITTED queries to continue, without requiring locks.

Upvotes: 1

Adam Ralph
Adam Ralph

Reputation: 29956

Have a look at http://msdn.microsoft.com/en-us/library/ms173763.aspx for an explanation of the isolation levels in SQL Server. SERIALIZABLE offers the highest level of isolation and takes range locks on tables which are held until the transaction completes. You'll have to use a lower isolation level to allow concurrent reads during your transaction.

Upvotes: 1

Related Questions