SQL Server table isolation level and lock issue

For the same ADO.Net statement, I want to make sure my understanding of isolation level and lock is correct.

  1. In default SQL Server isolation level (read committed), after read each row, the row will unlocked;

  2. Count # of Rows in Stored Procedure Result, then Insert result into table
  3. T-SQL USE satetement in condition
  4. Generate changes script
  5. C# cmd.ExecuteScalar(): “Cannot continue the execution because the session is in the kill state.”
  6. Creating View with dynamic columns by stored procedure
  7. how to use sql connection in PHP to execute a query on two databases
  8. If I raise isolation level to repeatable read, the lock (on the whole table? or some other level lock?) will be hold until the end of while loop?

e.g.:

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText= "select operation_id, operation_code, product_id, quantity
from dbo.operations where processed=0";

reader=cmd.ExecuteReader();

while (reader.Read())    
{
   // some operations
}

thanks in advance,
George

  • SQL Query aggregate may not appear in WHERE clause
  • Sql Server query varchar data sort like int
  • Split string and take last element
  • How can I pull a list of ID's from a SQL table as a comma-separated values string?
  • ASP SQL Server Connection
  • Changing SQL Server Database sorting
  • 3 Solutions collect form web for “SQL Server table isolation level and lock issue”

    1) Your first point is not correct: The default isolation level of Read Committed means that Dirty Reads will not occur (although phantom or non-repeatable reads might). It does not guarantee that single rows are locked.

    A non-repeatable read can occur in the following situation:

    1. Transaction 1 begins
    2. Transaction 1 read a row
    3. Transaction 2 begins
    4. Transaction 2 changes the value of the same row read by Transaction 1
    5. Transaction 2 commits
    6. Transaction 1 reads the row again. Transaction 1 has inconsistent data.
    

    2) Repeatable Read isolation level means the above situation cannot occur (though phantom reads still might). A phantom read can occur in the following situation:

    1. Transaction 1 begins
    2. Transaction 1 read a row
    3. Transaction 2 begins
    4. Transaction 2 deletes the row read by Transaction 1
    5. Transaction 2 commits. Transaction 1 can no longer repeat its initial read, 
       since the row no longer exists.
    

    If you want to guarantee that data does not chnage while you read it, you would need the Serializable isolation level. I would strongly advise agianst not using the Serializable isolation level unless you absolutely have to, as concurrency will suffer.

    A few useful articles about differences in isolation levels: Selects under READ COMMITTED and REPEATABLE READ may return incorrect results.

    When Snapshot Isolation Helps and When It Hurts

    In repeatable read or in serializable isolation levels the row locks acquired by the SELECT will be held until the transaction commits, not until the end of the loop. If you do not specify an explicit transaction then the SELECT statement will start an implicit one that will auto-commit when the SELECT statement completes. That is not the same moment as when the while loop ends, the loop is on the client and the SELECT statement may complete on the server before your loop ends.

    As Mitch said, the higher level isolation levels have a certain purpose, to avoid phantom reads or non-repeatable reads. A single SELECT statement, in an auto-committed implicit transaction, cannot require a higher isolation level. These levels only come into play on multiple statement transactions, when data is read multiple times. Perhaps is better if you explain the context of the operation you’re doing and why are you concerned about the locks this SELECT will place?

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.