locked
About Transaction Isolation Level Read-Committed RRS feed

  • Question

  • On MSDN, I got the following:

    Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

    But i'm not sure if when I run two query at the same times. One query to inserts 1000 rows to table A and the other selects rows in table A based on a criteria. I wonder the select query has to wait the insert query completed or not?

    Please tell me the truth :D

    Tuesday, June 26, 2012 10:37 AM

Answers

  • But i'm not sure if when I run two query at the same times. One query to inserts 1000 rows to table A and the other selects rows in table A based on a criteria. I wonder the select query has to wait the insert query completed or not?

    It depends on whether the select query touches the newly inserted rows.  If a suitable index exist on the table such that the select query can avoid the uncommitted data, the select query can run even while the insert transaction is in progress.  The select can also proceed if the database READ_COMMITTED_SNAPSHOT option is turned on since the latest committed row version will be returned instead of attempting to read uncommitted data.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by Naomi N Tuesday, June 26, 2012 5:27 PM
    • Marked as answer by Nghia Nguyen HTCS Wednesday, June 27, 2012 3:00 AM
    Tuesday, June 26, 2012 12:35 PM

All replies

  • Hi,

    Its vary easy to try this out. If your select * query is not using hint(nolock) then it will have to complete till the transaction gets completed (unless you don't have a index on your table and filter conditions)

    If your table is having an index and your query is just looking for a range scan that may not touch the index keys that are under update then it may not have to wait. Otherwise yes it will have to wait. So better solution is to implement read committed snapshot islolation

    CREATE TABLE [dbo].[Table_1](
    	[Id] [int] NULL,
    	[name] [varchar](50) NULL
    ) ON [PRIMARY]
    
    --session 1
    begin tran t1
    insert into table_1 values('5', 'satheesh');
    
    --run this in a new session SEssion 2
    select * from Table_1

    This is a good site with examples, you can have a look

    http://www.gavindraper.co.uk/2012/02/18/sql-server-isolation-levels-by-example/

    Regards
    Satheesh


    Tuesday, June 26, 2012 11:10 AM
  • But i'm not sure if when I run two query at the same times. One query to inserts 1000 rows to table A and the other selects rows in table A based on a criteria. I wonder the select query has to wait the insert query completed or not?

    It depends on whether the select query touches the newly inserted rows.  If a suitable index exist on the table such that the select query can avoid the uncommitted data, the select query can run even while the insert transaction is in progress.  The select can also proceed if the database READ_COMMITTED_SNAPSHOT option is turned on since the latest committed row version will be returned instead of attempting to read uncommitted data.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by Naomi N Tuesday, June 26, 2012 5:27 PM
    • Marked as answer by Nghia Nguyen HTCS Wednesday, June 27, 2012 3:00 AM
    Tuesday, June 26, 2012 12:35 PM