locked
How can a select statement result in being chosen as deadlock victim? RRS feed

  • Question

  • Hi,

    I'm confused:

    IMHO, if the transaction isolation level is set to "READ COMMITTED", it should be impossible to get a "deadlock victim" exception on simple SELECT statements from a VB.NET application. Yet this is what we are seeing.

    So apparently, despite the transaction level READ COMMITTED, SQL server waits for a transaction from another process, even if all I'm doing is firing a SELECT???? Seems wrong to me...

    Thanks in advance for clarifactions.

    Will de Haan


    1*! Galletto

    Friday, September 28, 2012 8:47 AM

Answers

  • Hi again,

    browsing through  some of the links, I came across the READ_COMMITTED_SNAPSHOT database option. If I understand it correctly, setting this option to ON will induce the behaviour I'm looking for: SELECTs will get the data from the situation BEFORE transactioned updates from other processes.

    Correct?


    1*! Galletto


    Yes that's right. But switting this option on will add over head on TempDB. I have this option on in few places.

    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Friday, September 28, 2012 11:11 AM
    • Marked as answer by galletto_nl Friday, September 28, 2012 11:18 AM
    Friday, September 28, 2012 11:11 AM

All replies

  • Hi Galletto,

    This is because READ COMMITED snapshot lock the table. If you want your SELECT's, not to be block then use READ UNCOMMITED ISOLAION LEVEL or use NOLOCK. I'm not big fan of this approach but if this is your requirement then go ahead and do that. For more information, see Concurrency Series: Basics of Transaction Isolation Levels.


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Friday, September 28, 2012 8:58 AM
    Friday, September 28, 2012 8:58 AM
  • Hello Basit (or others),

    Is this a typical SQL server thing?

    I have never come across such problems on Oracle databases. Why would I want to lock anything on a SELECT statement? The idea behind READ COMMITTED is that I don't see the uncommitted data until it's been committed by the other process, so I expect to get the data as it was before the transaction was started by the other process.


    1*! Galletto

    Friday, September 28, 2012 9:13 AM
  • Did you captured the deadlock information via Profiler or trace flags...May be this is due to the memory requirement which is causing the deadlock. 

    Have a look at the below article and if possible post your deadlock graph

    http://msdn.microsoft.com/en-us/library/ms178104(v=SQL.90).aspx


    Thanks and regards, Rishabh K

    Friday, September 28, 2012 9:31 AM
  • Hello Basit (or others),

    Is this a typical SQL server thing?

    I have never come across such problems on Oracle databases. Why would I want to lock anything on a SELECT statement? The idea behind READ COMMITTED is that I don't see the uncommitted data until it's been committed by the other process, so I expect to get the data as it was before the transaction was started by the other process.


    1*! Galletto

    Hi Galletto_nl,

    Yes this is typical in SQL Server, I'm not Oracle expert so can't comment on that. READ COMMITED ISOLATION level is the default ISOLATION of SQL Server. The reason behind using this is to ensure you are not having dirty reads while data is being updated. For more information, click here.

    SQL Server also allows you to set the transaction ISOLATION level. For example, in your case if you don't want SELECT statements to lock table then use the following statement on top of your script or query:

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED 
    SELECT * FROM YourTable


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Friday, September 28, 2012 9:37 AM
    Friday, September 28, 2012 9:37 AM
  • Hi again,

    browsing through  some of the links, I came across the READ_COMMITTED_SNAPSHOT database option. If I understand it correctly, setting this option to ON will induce the behaviour I'm looking for: SELECTs will get the data from the situation BEFORE transactioned updates from other processes.

    Correct?


    1*! Galletto

    Friday, September 28, 2012 11:07 AM
  • Hi again,

    browsing through  some of the links, I came across the READ_COMMITTED_SNAPSHOT database option. If I understand it correctly, setting this option to ON will induce the behaviour I'm looking for: SELECTs will get the data from the situation BEFORE transactioned updates from other processes.

    Correct?


    1*! Galletto


    Yes that's right. But switting this option on will add over head on TempDB. I have this option on in few places.

    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Friday, September 28, 2012 11:11 AM
    • Marked as answer by galletto_nl Friday, September 28, 2012 11:18 AM
    Friday, September 28, 2012 11:11 AM
  • OK, We'll give that a try then.

    Stanx!


    1*! Galletto

    Friday, September 28, 2012 11:23 AM