none
Select statement hanging/table locked until SQLtransaction isolationlevel ReadCommitted ends RRS feed

  • Question

  • Hi,

    I am using the same sqltransaction object which has a isolationlevel of readcommitted on three different dataadapters. Since the transaction property on dataadapters
    is not exposed out of the box, I am using the following method to overcome this limitation: This method uses reflection to expose the transaction property of a datadapter.

    http://www.codeproject.com/KB/database/TableAdapter-Transaction.aspx

    Everything works as expected and the three dataadapters can be wrapped in a single SQLtransaction. The problem is that as soon as the update method is called on
    any one of the dataadapters, the select statments on the database table hangs(in query analizer. In otherwords the table is locked until a rollback or commit is called on the SQLtransaction object. I wqas under the impression that as long as the sqltransaction object is using isolationlevel.ReadCommitted, other transactions or
    queries would be able to proform a select in the database.

    So basically I want to be able to perform a select on a database table while that table is being updated as part of a sqltransaction.

    Any help would be appreciated.

    Following is the code snippet I am using:

            
    
    
    
    ‘Table data tables
    Dim TranTest1 As New DSTransactionTest.TranTest1DataTable
            Dim TranTest2 As New DSTransactionTest.TranTest2DataTable
            Dim TranTest3 As New DSTransactionTest.TranTest3DataTable
    
    
    
    ‘Table tableAdapters
    
            Dim Tran1TestTA As New DSTransactionTestTableAdapters.TranTest1TableAdapter
            Dim Tran2TestTA As New DSTransactionTestTableAdapters.TranTest2TableAdapter
            Dim Tran3TestTA As New DSTransactionTestTableAdapters.TranTest3TableAdapter
    
    
         
    
    
    	  ‘Table 1 row	
            Dim Tran1Row As DSTransactionTest.TranTest1Row = TranTest1.NewTranTest1Row
            Tran1Row.TestName = "Test1"
            TranTest1.AddTranTest1Row(Tran1Row)
    
            'Setup connection 
            conn = New SqlConnection("Data Source=XXXX;Initial Catalog=Northwind;Integrated Security=True")
            conn.Open()
    
            'Setup transaction 
            transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted)
            HelperTA.SetTransaction(Tran1TestTA, transaction)
    
    
    	  ‘ Update Table1 and get back identity
            Tran1TestTA.Update(TranTest1) ‘ on this line the sql in query analyzer stops working
            Dim TestID As Integer
            TestID = Tran1Row.TestID
    
    
            ‘Table2 row insert identity from table1
            Dim Tran2Row As DSTransactionTest.TranTest2Row = TranTest2.NewTranTest2Row
            Tran2Row.TestName2 = "Test2"
            Tran2Row.TestID = Tran1Row.TestID ‘Identity from table 1
            TranTest2.AddTranTest2Row(Tran2Row)
            HelperTA.SetTransaction(Tran2TestTA, transaction)
            Tran2TestTA.Update(TranTest2)
    
            Dim TestID2 As Integer
            TestID2 = Tran2Row.TestID2
    	
    	  ‘Table 3 row	
            Dim Tran3Row As DSTransactionTest.TranTest3Row = TranTest3.NewTranTest3Row
            Tran3Row.TestName3 = "Test3"
            Tran3Row.TestID2 = Tran2Row.TestID2
            TranTest3.AddTranTest3Row(Tran3Row)
            HelperTA.SetTransaction(Tran3TestTA, transaction)
            Tran3TestTA.Update(TranTest3)
    
            transaction.Commit()
    


    • Moved by VMazurModerator Friday, October 9, 2009 10:12 AM (From:ADO.NET DataSet)
    Thursday, October 8, 2009 7:36 PM

Answers

  • > I wqas under the impression that as long as the sqltransaction object is using isolationlevel.ReadCommitted

    Not quite.  (But yes if the READ_COMMITTED_SNAPSHOT database option is in effect.  See below.)

    When you write to a row, SQL Server obtains an exclusive lock on it. (At a minimum it must lock the row you wrote, but there are many situations where SQL Server will lock more than just that row.  This is beyond the scope of this answer.)  The exclusive lock is held until you commit or rollback the transaction.  For a write to a row, all of this happens regardless of the writing transaction's isolation level.

    A second transaction that attempts to read a row that has been exclusively locked by a first transaction will have the following behavior:

    1.  If the reading transaction is running in the READ UNCOMMITTED isolation level or has used a NOLOCK hint in the query, the lock will be ignored.  This could cause the reading query to see data that ultimately gets rolled back (known as a "dirty read") or other anomalies and/or errors (for example, the reading transaction might skip over the locked row).

    2.  If the reading transaction is running in the SNAPSHOT isolation level, the row will be read from the snapshot version store.  You could think of this as if the reading transaction looked back in time and established a view of what was committed in the database at the time the transaction first accessed any data.  The ALLOW_SNAPSHOT_ISOLATION database option must be set to use SNAPSHOT isolation level.

    3.  If the READ_COMMITTED_SNAPSHOT database option is set and the reading transaction is running in READ COMMITTED isolation level, it works like #2 with the subtle difference that the snapshot is re-established with every statement in the reading transaction, so different statements could see different points in time.

    4.  If none of the above apply, the reading transaction waits for the lock to be released.

    The first thing I would check is to see if your SELECT statements are selecting too much data.  Perhaps you are selecting all the rows in the table when you could instead do a targeted SELECT on just the rows you need, thus likely avoiding selection of the locked rows with good probability.  If this is not the solution for you, consider whether you can reduce the duration between the first write and the eventual commit.  Commonly, a transaction can be broken up into a sequence of several transactions.

    If the ALLOW_SNAPSHOT_ISOLATION and/or READ_COMMITTED_SNAPSHOT interest you, discuss it with your database administrator as these require database-level options to be set and there are system-wide implications of turning these options on.  If you are the administrator, I refer you to Books Online "Row Versioning-based Isolation Levels in the Database Engine" (http://msdn.microsoft.com/en-us/library/ms177404.aspx).
    • Marked as answer by Yichun_Feng Tuesday, October 13, 2009 7:11 AM
    Thursday, October 8, 2009 11:58 PM
  • Thanks for your respnse. That clears up things for me. I did find a workaround for the issue.
    if the query is given a hint of READPAST the query will return rows even while the sqltransaction is going on.

    So the query would look like the following:

    Select * from TestTable with(READPAST).

    Although in my scenario, there were just too many queries that needed to be changed, since it affected a table that was used heavily in all our applications.
    So we decided not to use the workaround,but luckily our sql transaction does not take that long. Intially I thought that the table would get locked for a long time but since that is not the case we can use the SQL transaction without worrying about the table getting locked.

    Thanks for your help again.
    • Marked as answer by Yichun_Feng Tuesday, October 13, 2009 7:11 AM
    Monday, October 12, 2009 3:24 PM

All replies

  • > I wqas under the impression that as long as the sqltransaction object is using isolationlevel.ReadCommitted

    Not quite.  (But yes if the READ_COMMITTED_SNAPSHOT database option is in effect.  See below.)

    When you write to a row, SQL Server obtains an exclusive lock on it. (At a minimum it must lock the row you wrote, but there are many situations where SQL Server will lock more than just that row.  This is beyond the scope of this answer.)  The exclusive lock is held until you commit or rollback the transaction.  For a write to a row, all of this happens regardless of the writing transaction's isolation level.

    A second transaction that attempts to read a row that has been exclusively locked by a first transaction will have the following behavior:

    1.  If the reading transaction is running in the READ UNCOMMITTED isolation level or has used a NOLOCK hint in the query, the lock will be ignored.  This could cause the reading query to see data that ultimately gets rolled back (known as a "dirty read") or other anomalies and/or errors (for example, the reading transaction might skip over the locked row).

    2.  If the reading transaction is running in the SNAPSHOT isolation level, the row will be read from the snapshot version store.  You could think of this as if the reading transaction looked back in time and established a view of what was committed in the database at the time the transaction first accessed any data.  The ALLOW_SNAPSHOT_ISOLATION database option must be set to use SNAPSHOT isolation level.

    3.  If the READ_COMMITTED_SNAPSHOT database option is set and the reading transaction is running in READ COMMITTED isolation level, it works like #2 with the subtle difference that the snapshot is re-established with every statement in the reading transaction, so different statements could see different points in time.

    4.  If none of the above apply, the reading transaction waits for the lock to be released.

    The first thing I would check is to see if your SELECT statements are selecting too much data.  Perhaps you are selecting all the rows in the table when you could instead do a targeted SELECT on just the rows you need, thus likely avoiding selection of the locked rows with good probability.  If this is not the solution for you, consider whether you can reduce the duration between the first write and the eventual commit.  Commonly, a transaction can be broken up into a sequence of several transactions.

    If the ALLOW_SNAPSHOT_ISOLATION and/or READ_COMMITTED_SNAPSHOT interest you, discuss it with your database administrator as these require database-level options to be set and there are system-wide implications of turning these options on.  If you are the administrator, I refer you to Books Online "Row Versioning-based Isolation Levels in the Database Engine" (http://msdn.microsoft.com/en-us/library/ms177404.aspx).
    • Marked as answer by Yichun_Feng Tuesday, October 13, 2009 7:11 AM
    Thursday, October 8, 2009 11:58 PM
  • Thanks for your respnse. That clears up things for me. I did find a workaround for the issue.
    if the query is given a hint of READPAST the query will return rows even while the sqltransaction is going on.

    So the query would look like the following:

    Select * from TestTable with(READPAST).

    Although in my scenario, there were just too many queries that needed to be changed, since it affected a table that was used heavily in all our applications.
    So we decided not to use the workaround,but luckily our sql transaction does not take that long. Intially I thought that the table would get locked for a long time but since that is not the case we can use the SQL transaction without worrying about the table getting locked.

    Thanks for your help again.
    • Marked as answer by Yichun_Feng Tuesday, October 13, 2009 7:11 AM
    Monday, October 12, 2009 3:24 PM