none
SqlTransactions RRS feed

  • Question

  • Hello everyone.

    What is the behavior of sql transactions when it comes to locking down tables? Specifically, If I do a SELECT in a transaction, does it lock down the row, or the entire table, or not lock at all?

    Thanks,
    Jordan

    Wednesday, December 3, 2008 4:35 PM

All replies

  • SqlTransactions are ADO.NET transaction which technically are a unit of work like the RDBMS transactions so if you are using it with any SQL Server version from 7.0 it is row level because it was SQL Server 6.5 that does table level locking which is not good.   I also don't think newer SQL Server like 2005 and 2008 will let you lock a table because that is very bad for SQL Server.  Look into the transaction isolation levels in both ADO.NET and SQL Server in the BOL and MSDN for SqlTransactions.  Post again if you still have more questions because this type transaction can be manually quantified.

     

     

     

     

     

    Wednesday, December 3, 2008 4:54 PM
  • So if i'm doing a query like this in a transaction:

    SELECT COUNT(*) FROM some_table
    WHERE some_predicate

    and I want to prevent someone from inserting a row or deleting a row from some_table while I'm in the transaction (since an insert or delete could change the value returned in my query above if i were to run the query again), the only way to accomplish this is by setting the isolation level to SERIALIZABLE? Is there another way to accomplish this?

    Thanks,
    Jordan
    Wednesday, December 3, 2008 5:25 PM
  • I think Serializable isolation in any version of SQL Server from 7.0 is not a good idea because transaction main goal is atomicity which you can get with most levels of isolation.  I am assuming you know all aggregate functions ignore Null except COUNT(*) so you risk your application timing out on just this operation.

     

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

     

     

     

    Wednesday, December 3, 2008 5:59 PM
  • SELECT COUNT(*) FROM some_table
    WHERE some_value = @param

    Lets say the above query is wrapped up in a function in my dataset called GetCount()  and I want to have a transaction with pseudocode:

    BeginTransaction();
    int count = GetCount();
    if( count > 0 )
    {
       DoSomeInsert();
    }
    else
      DoSomeOtherInsert();
    EndTransaction();

    Given what I've read about isolation levels, Serializable is the only one which guarantees that the value returned by GetCount() is the same everytime I call it in my transaction. If someone were allowed to insert a row into some_table during my transaction, then it is possible for me to execute DoSomeInsert() when I really wanted to execute DoSomeOtherInsert() or vice versa. Does this example make sense to you? I can clarify if you need.

    Thanks again,
    Jordan

    Wednesday, December 3, 2008 7:08 PM
  • I see two separate operation however check the thread below for some options to your problem.

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2632909&SiteID=1

     

     

    Wednesday, December 3, 2008 7:36 PM
  • Actually default locking in SQL Server 7 and up is not row-level. Row-level locking is very expensive and affects performance of application. SQL Server decides what kind of lock to use, row, page or table depending on context. That means you cannot expect that SQL Server will always lock only one row or one page of data. Following link provides some details about locking modes in SQL Server

     

    http://msdn.microsoft.com/en-us/library/aa213039(SQL.80).aspx

     

    Thursday, December 4, 2008 11:43 AM
    Moderator
  • I did not say default but row level locking was one of the achievements of 7.0 I know this in my sleep because I was also one of the early MCDBA. In 7.0 and now SQL Server can escalate locks but in RDBMS mathematical unit of work transactions low level is a better lock than table because table lock means nobody can use the table until the end of the transaction which is what the OP is asking and I am telling the op to use alternative solution. Please read the whole thread again.

     

    Thursday, December 4, 2008 1:03 PM