locked
Asking about transaction isolation level RRS feed

  • Question

  • I monitor database by activity monitor then i see many wait time for lock.

    i want solve it by

    -change isolation level to READ_UNCOMMITTED

    -Select database which it want read and write . turn on READ_COMMITTED_SNAPSHOT

    Can i use this solution?

    This solution have any affect?

    Thank

    Wednesday, May 13, 2015 3:53 AM

Answers

  • hmm..whats is the reason for the locks being held up for longer duration.. i would advice you to start looking into it from that angle before you start changing isolation level

    because the consequences for longer txns would be same irrespective of isolation - of course, you might not see blocking directly but it still to effect other things like tempdb storage for version control etc..

    if you change to read uncommitted - it can lead to reading dirty(uncommitted) data - not sure if your application would be happy with that

    read committed snapshot can work but uses tempdb for version control..so, highly advice you first check the reason for locks being held up for longer duration and then, if you still have blocking issues, use read committed snashot


    Hope it Helps!!

    Wednesday, May 13, 2015 4:01 AM
  • I monitor database by activity monitor then i see many wait time for lock.

    i want solve it by

    -change isolation level to READ_UNCOMMITTED

    -Select database which it want read and write . turn on READ_COMMITTED_SNAPSHOT

    Can i use this solution?

    This solution have any affect?

    Thank

    First of all there is no need to use read uncommitted Isolation level you would end up messing things.

    Snapshot isolation level , in your case RCSI, is not all unicorns and rainbow and it does have drawbacks please read below article to understand snapshot based isolation level. Your tempdb must have enough free space and should be on fast drives to take advantage because Snapshot isolation heavily uses Tempdb

    Choosing Row Versioning-based Isolation Levels


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Wednesday, May 13, 2015 5:23 AM
    Answerer
  • Hello ! Locking is a natural phenomena in DBMS to prevent concurrency side-effects, it is the way by which Transactional integrity is maintained in concurrent workloads and allow others to work in parallel

    But long-term locks is something which needs to be avoided and there are some basic ground rules for those such as good code design, keeping your transactions short, commit frequently, hitting rows with appropriate indexes and so on.

    Using READ UNCOMMITTED is never a solution and likewise SNAPSHOT ISOLATION too has some considerations before using it.

    It is imperative for you to know what causes blocking (Blocker and Blocked processes) therefore you can use the following set of queries to know about them and post back your results here for further help and advice:

    -- Workload Analysis - Run this every few seconds and store the result in excel for analysis
    SELECT
    DB_NAME( Database_ID ) AS Database_Name, CPU_Time, Total_Elapsed_Time, Total_Elapsed_Time/1000 TE_Insec, Wait_Time/1000 Wait_Insec,
    Session_ID,
    Blocking_Session_ID, Status,
    Wait_Type, Wait_Time, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
    ((CASE qs.statement_end_offset
    WHEN -1 THEN DATALENGTH(st.text)
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1) AS statement_text, GetDate(),
    Open_Transaction_Count
    FROM sys.dm_exec_requests AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    ORDER BY Wait_Time DESC
    
    -- Lead Blocker and Blocked process tree
    SELECT  x.session_id,
            x.host_name,
            x.login_name,
            x.start_time,
            x.totalReads,
            x.totalWrites,
            x.totalCPU,
            x.writes_in_tempdb,
        (
                -- Query gets XML text for the sql query for the session_id
              SELECT      text AS [text()]
                FROM  sys.dm_exec_sql_text(x.sql_handle)
              FOR XML PATH(''), TYPE
     
        )AS sql_text,
         COALESCE(x.blocking_session_id, 0) AS blocking_session_id,
        (
            SELECT p.text
            FROM
            (
                -- Query gets the corresponding sql_handle info to find the XML text in the next query
                SELECT MIN(sql_handle) AS sql_handle
                FROM sys.dm_exec_requests r2
                WHERE r2.session_id = x.blocking_session_id
            ) AS r_blocking
            CROSS APPLY
            (
                -- Query will pull back the XML text for a blocking session if there is any from the sql_haldle
                SELECT text AS [text()]
                FROM sys.dm_exec_sql_text(r_blocking.sql_handle)
                FOR XML PATH(''), TYPE
            ) p (text)
        ) AS blocking_text
    FROM
    (
     
    SELECT  r.session_id,
                s.host_name,
                s.login_name,
                r.start_time,
                r.sql_handle,
                r.blocking_session_id,
                SUM(r.reads) AS totalReads,
                SUM(r.writes) AS totalWrites,
                SUM(r.cpu_time) AS totalCPU,
               SUM(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb
       FROM    sys.dm_exec_requests r
        JOIN    sys.dm_exec_sessions s ON s.session_id = r.session_id
        JOIN    sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id
        WHERE   r.status IN ('running', 'runnable', 'suspended')
          and r.blocking_session_id <> 0
        GROUP BY    r.session_id,
                    s.host_name,
                    s.login_name,
                    r.start_time,
                    r.sql_handle,
                    r.blocking_session_id
    ) x
    


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Wednesday, May 13, 2015 5:42 AM
  • Read_committed_snapshot is the database option that affects behavior of the readers in READ COMMITTED isolation level. When you enable this option on database level (requires exclusive db access to change it), all your readers in READ COMMITTED isolation level will start to use row-versioning reading old(committed) versions from the version store rather than being blocked by (S)/(X) locks incompatibility. This option does not require any code changes (assuming you readers are using READ COMMITTED). It would not change behavior of writers (writers still block each other) nor behavior of readers in any other transaction isolation level.

      

    Speaking of consistency, RCSI gives you statement level consistency – readers ignores the data changes done after statement has been started. Snapshot – transaction level consistency – session deals with “snapshot” of the data at the moment transaction started. Again, it could lead to error 3960 in the system with volatile data.

     

    Downsides:

    1.       Excessive tempdb usage due to version store activity. Think about session that deletes 1M rows. All those rows must be copied to version store regardless of session transaction isolation level and/or if there are other sessions that running in optimistic isolation levels at the moment when deletion started.
    2.       Extra fragmentation – SQL Server adds 14-byte version tag (version store pointer) to the rows in the data files when they are modified. This tag stayed until index is rebuild
    3.       Development challenges – again, error 3960 with snapshot isolation level. Another example in both isolation levels – trigger or code based referential integrity. You can always solve it by adding with (READCOMMITTED) hint if needed.

     

    While switching to RCSI could be good emergency technique to remove blocking between readers and writers (if you can live with overhead AND readers are using read committed), I would suggest to find root cause of the blocking. Confirm that you have locking issues – check if there are shared lock waits in wait stats, that there is no lock escalations that block readers, check that queries are optimized, etc.  


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, May 13, 2015 7:28 AM

All replies

  • hmm..whats is the reason for the locks being held up for longer duration.. i would advice you to start looking into it from that angle before you start changing isolation level

    because the consequences for longer txns would be same irrespective of isolation - of course, you might not see blocking directly but it still to effect other things like tempdb storage for version control etc..

    if you change to read uncommitted - it can lead to reading dirty(uncommitted) data - not sure if your application would be happy with that

    read committed snapshot can work but uses tempdb for version control..so, highly advice you first check the reason for locks being held up for longer duration and then, if you still have blocking issues, use read committed snashot


    Hope it Helps!!

    Wednesday, May 13, 2015 4:01 AM
  • I monitor database by activity monitor then i see many wait time for lock.

    i want solve it by

    -change isolation level to READ_UNCOMMITTED

    -Select database which it want read and write . turn on READ_COMMITTED_SNAPSHOT

    Can i use this solution?

    This solution have any affect?

    Thank

    First of all there is no need to use read uncommitted Isolation level you would end up messing things.

    Snapshot isolation level , in your case RCSI, is not all unicorns and rainbow and it does have drawbacks please read below article to understand snapshot based isolation level. Your tempdb must have enough free space and should be on fast drives to take advantage because Snapshot isolation heavily uses Tempdb

    Choosing Row Versioning-based Isolation Levels


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Wednesday, May 13, 2015 5:23 AM
    Answerer
  • Hello ! Locking is a natural phenomena in DBMS to prevent concurrency side-effects, it is the way by which Transactional integrity is maintained in concurrent workloads and allow others to work in parallel

    But long-term locks is something which needs to be avoided and there are some basic ground rules for those such as good code design, keeping your transactions short, commit frequently, hitting rows with appropriate indexes and so on.

    Using READ UNCOMMITTED is never a solution and likewise SNAPSHOT ISOLATION too has some considerations before using it.

    It is imperative for you to know what causes blocking (Blocker and Blocked processes) therefore you can use the following set of queries to know about them and post back your results here for further help and advice:

    -- Workload Analysis - Run this every few seconds and store the result in excel for analysis
    SELECT
    DB_NAME( Database_ID ) AS Database_Name, CPU_Time, Total_Elapsed_Time, Total_Elapsed_Time/1000 TE_Insec, Wait_Time/1000 Wait_Insec,
    Session_ID,
    Blocking_Session_ID, Status,
    Wait_Type, Wait_Time, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
    ((CASE qs.statement_end_offset
    WHEN -1 THEN DATALENGTH(st.text)
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1) AS statement_text, GetDate(),
    Open_Transaction_Count
    FROM sys.dm_exec_requests AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    ORDER BY Wait_Time DESC
    
    -- Lead Blocker and Blocked process tree
    SELECT  x.session_id,
            x.host_name,
            x.login_name,
            x.start_time,
            x.totalReads,
            x.totalWrites,
            x.totalCPU,
            x.writes_in_tempdb,
        (
                -- Query gets XML text for the sql query for the session_id
              SELECT      text AS [text()]
                FROM  sys.dm_exec_sql_text(x.sql_handle)
              FOR XML PATH(''), TYPE
     
        )AS sql_text,
         COALESCE(x.blocking_session_id, 0) AS blocking_session_id,
        (
            SELECT p.text
            FROM
            (
                -- Query gets the corresponding sql_handle info to find the XML text in the next query
                SELECT MIN(sql_handle) AS sql_handle
                FROM sys.dm_exec_requests r2
                WHERE r2.session_id = x.blocking_session_id
            ) AS r_blocking
            CROSS APPLY
            (
                -- Query will pull back the XML text for a blocking session if there is any from the sql_haldle
                SELECT text AS [text()]
                FROM sys.dm_exec_sql_text(r_blocking.sql_handle)
                FOR XML PATH(''), TYPE
            ) p (text)
        ) AS blocking_text
    FROM
    (
     
    SELECT  r.session_id,
                s.host_name,
                s.login_name,
                r.start_time,
                r.sql_handle,
                r.blocking_session_id,
                SUM(r.reads) AS totalReads,
                SUM(r.writes) AS totalWrites,
                SUM(r.cpu_time) AS totalCPU,
               SUM(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb
       FROM    sys.dm_exec_requests r
        JOIN    sys.dm_exec_sessions s ON s.session_id = r.session_id
        JOIN    sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id
        WHERE   r.status IN ('running', 'runnable', 'suspended')
          and r.blocking_session_id <> 0
        GROUP BY    r.session_id,
                    s.host_name,
                    s.login_name,
                    r.start_time,
                    r.sql_handle,
                    r.blocking_session_id
    ) x
    


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Wednesday, May 13, 2015 5:42 AM
  • Read_committed_snapshot is the database option that affects behavior of the readers in READ COMMITTED isolation level. When you enable this option on database level (requires exclusive db access to change it), all your readers in READ COMMITTED isolation level will start to use row-versioning reading old(committed) versions from the version store rather than being blocked by (S)/(X) locks incompatibility. This option does not require any code changes (assuming you readers are using READ COMMITTED). It would not change behavior of writers (writers still block each other) nor behavior of readers in any other transaction isolation level.

      

    Speaking of consistency, RCSI gives you statement level consistency – readers ignores the data changes done after statement has been started. Snapshot – transaction level consistency – session deals with “snapshot” of the data at the moment transaction started. Again, it could lead to error 3960 in the system with volatile data.

     

    Downsides:

    1.       Excessive tempdb usage due to version store activity. Think about session that deletes 1M rows. All those rows must be copied to version store regardless of session transaction isolation level and/or if there are other sessions that running in optimistic isolation levels at the moment when deletion started.
    2.       Extra fragmentation – SQL Server adds 14-byte version tag (version store pointer) to the rows in the data files when they are modified. This tag stayed until index is rebuild
    3.       Development challenges – again, error 3960 with snapshot isolation level. Another example in both isolation levels – trigger or code based referential integrity. You can always solve it by adding with (READCOMMITTED) hint if needed.

     

    While switching to RCSI could be good emergency technique to remove blocking between readers and writers (if you can live with overhead AND readers are using read committed), I would suggest to find root cause of the blocking. Confirm that you have locking issues – check if there are shared lock waits in wait stats, that there is no lock escalations that block readers, check that queries are optimized, etc.  


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, May 13, 2015 7:28 AM