none
Transaction Issue

    Question

  • Dear All, I am facing the transaction Issue, i am giving my query for clear view of my issue....

    connection # 1
    ----------------------
    BEGIN TRANSACTION
    UPDATE atbookingrental_info SET machine_name = 'Jitu'
    COMMIT TRANSACTION


    BEGIN TRANSACTION
    UPDATE cfdeskuser_master SET user_code = 'Admin12'
    COMMIT TRANSACTION


    connection # 2
    ----------------------
    SELECT * FROM atbookingrental_info


    SELECT * FROM cfdeskuser_master


    Order of execution :


        Connection # 1 , (atbookingrental_info) Begin tran and update , without commit changes ,
        Connection # 2, (atbookingrental_info) Select same table, query is going in waiting state as might be table is locked
        Now if we execute commit in conn # 1 , select in conn #2 return result.


        Connection # 1 , (cfdeskuser_master) Begin tran and update , without commit changes , (First time)
        Connection # 2, (cfdeskuser_master) Select same table, query is going in waiting state as might be table is locked (First time)
        Now if we execute commit in conn # 1 , select in conn #2 return result.(First time)


        Connection # 1 , (cfdeskuser_master) Begin tran and update , without commit changes , (Second time)
        Connection # 2, (cfdeskuser_master) now here in second attempt select in conn # 2 return the result and query is not going in waiting, We don't know reason of this.


        If we do same with (atbookingrental_info) second time, each time query is going in waiting.


    Both table has consistent data types and only difference is in no of columns and no of records. atbookingrental_info as around 37 K records , and cfdeskuser_master has only 59.


    So we took another table with only 1 row and repeat same procedure here also table is going in waiting state each time. Only cfdeskuser_master giving strange behavior.


    Please let me know if you conclude anything from this, what can be the issue.


    Sandeep J. Sharma

    Friday, March 16, 2012 8:05 AM

Answers

  • I would say that this is something similar to http://sqlblog.com/blogs/paul_white/archive/2010/11/01/read-committed-shared-locks-and-rollbacks.aspx

    The second time you run the update for cfdeskuser_master, you don't change nothing to the value of user_code because it's already been set to 'Admin12'.  Therefore, even if you have an uncommitted transaction, the second connection can still read the values set by the first connection because there is no change for this value between the uncommitted, the committed or the rolled back states.

    SQL-Server has code for detecting this state in order to increase concurrency but its effectiveness depends on a variety of factors: presence or not of a clustered index and of other indexes, number of rows affected, etc.

    Monday, March 19, 2012 7:46 AM

All replies

  • Could you give us the Value of @@TRANCOUNT after before the first select and after each Satement:
    http://msdn.microsoft.com/en-us/library/ms187967.aspx

    It could also be that there are some triggers or constraints on fields of the tables (and if they do not properly rollback/commit their transactions it could cause the entrie thing to go haywire because of an Transaction still being uncommited).

    Friday, March 16, 2012 8:36 AM
  • Check your ISOLATION LEVEL as well by executing  DBCC USEROPTIONS. May be you're using a row versioning isolation level that cache different versions of data/row size is small. That may be the reason for the second query to execute without waiting (reading from the cache). Please have a look at the following link:

    Behavior Changes to Database Engine Features in SQL Server 2008 R2

    Edit:

    Have a look at the below link as well (especially the section 'Behaviour when reading data'):

    Understanding Row Versioning-Based Isolation Levels

    - Krishnakumar S


    Friday, March 16, 2012 8:44 AM
  • It seems to me that , the execution plan is getting cached and because of that only while running second time it is not locking the table.Try changing the update condition for sure the second query  will wait. Also you can try clearing the buffer cache and try running the update statment again to make the second connection to wait.
    Friday, March 16, 2012 8:58 AM
  • Hi Sorna,

    The execution plan do not have any impact on locking, concurrency, and transaction. The execution plan is, as the name suggests, an optimal plan that indicates how to access/modify data in minimal cost (the optimal path). The execution plan is compiled by the query engine. It's the storage engine's job (lock manager in storage engine precisely) how to access table or index data needed (mentioned in the execution plan) and lock the resources according to the isolation level settings.

    Execution plan cachining is a normal behaviour and clearing the cache do not prevent locking and may cause plan recompiles that impact performance of the query.

    - Krishnakumar S

    Friday, March 16, 2012 12:49 PM
  • Thanks for your explanation Krishna and what i was trying to mean in my answer is if the same statement is executed again from the same connection without any change happened in the table , the table data will not be locked. If we change the condition in update statement , it will lock the table data until the trasaction is commited.SQL Server engine is inteligent enough to check the above parameters and lock the table accordingly.I do completly agree with your pointers about the execution plan. Usage of the word execution plan is wrong in the above answer and thanks for pointing out.

    Monday, March 19, 2012 5:57 AM
  • I would say that this is something similar to http://sqlblog.com/blogs/paul_white/archive/2010/11/01/read-committed-shared-locks-and-rollbacks.aspx

    The second time you run the update for cfdeskuser_master, you don't change nothing to the value of user_code because it's already been set to 'Admin12'.  Therefore, even if you have an uncommitted transaction, the second connection can still read the values set by the first connection because there is no change for this value between the uncommitted, the committed or the rolled back states.

    SQL-Server has code for detecting this state in order to increase concurrency but its effectiveness depends on a variety of factors: presence or not of a clustered index and of other indexes, number of rows affected, etc.

    Monday, March 19, 2012 7:46 AM