none
請問單純的查詢程式為何會發生dead lock RRS feed

  • 問題

  • dear all,

    資料庫:SQL SERVER 2008, OS: WINDOWS2008 64bit

    我有一個查詢程式,同一時間內可能有多人會執行,常常發生deadlock的狀況,程式裡只有單純的SELECT指令,
    下列偵測到的兩個發生衝突的session,都是同一個查詢程式,
    請問select指令不是只會有Share lock嗎?為何在LOG裡看到有IX lock?
    只是select而已為何會發生deadlock呢?謝謝.

    以下的XML訊息是我抓出來的deadlock錯誤訊息,請參考.

      <?xml version="1.0" encoding="utf-8" ?> 
    - <deadlock-list>
    - <deadlock victim="process4009948">
    - <process-list>
    - <process id="process4009948" taskpriority="0" logused="1196" waitresource="PAGE: 
    21:1:916625" waittime="4315" ownerId="2059675785" transactionname="user_transaction" 
    lasttranstarted="2012-11-16T10:10:50.440" XDES="0x7d9cb0e90" lockMode="S" 
    schedulerid="9" kpid="10784" status="suspended" spid="3540" sbid="0" ecid="0" priority="0" 
    trancount="1" lastbatchstarted="2012-11-16T10:10:51.427" 
    lastbatchcompleted="2012-11-16T10:10:51.407" clientapp=".Net SqlClient Data Provider" 
    hostname="HT1" hostpid="23452" loginname="USER01" isolationlevel="read committed (2)" 
    xactid="2059675785" currentdb="21" lockTimeout="4294967295" clientoption1="671088672"
     clientoption2="128056">
    - <executionStack>
      <frame procname="adhoc" line="1" stmtstart="198" sqlhandle="0x020000002df9a61c5246f8f6aff5ac4f566bafdb9a0de779">SELECT 
    COUNT(*) AS [value] FROM [dbo].[TRANF1] AS [t0] WHERE ([t0].[HHNo] = @p0) AND 
    (([t0].[ApType] = @p1) OR ([t0].[ApType] = @p2) OR ([t0].[ApType] = @p3) OR 
    ([t0].[ApType] = @p4)) AND ([t0].[SNo] <> @p5) AND (([t0].[DataStatus] = @p6) OR 
    ([t0].[DataStatus] = @p7) OR ([t0].[DataStatus] = @p8)) AND (([t0].[CreateDay] < @p9) 
    OR (([t0].[CreateDay] = @p10) AND ([t0].[CreateTime] < @p11)))</frame> 
      <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown
    </frame> 
      </executionStack>
      <inputbuf>(@p0 int,@p1 int,@p2 int,@p3 int,@p4 int,@p5 int,@p6 int,@p7 int,@p8 int,@p9 
    int,@p10 int,@p11 int)SELECT COUNT(*) AS [value] FROM [dbo].[TRANF1] AS [t0] WHERE 
    ([t0].[HHNo] = @p0) AND (([t0].[ApType] = @p1) OR ([t0].[ApType] = @p2) OR 
    ([t0].[ApType] = @p3) OR ([t0].[ApType] = @p4)) AND ([t0].[SNo] <> @p5) AND 
    (([t0].[DataStatus] = @p6) OR ([t0].[DataStatus] = @p7) OR ([t0].[DataStatus] = @p8)) 
    AND (([t0].[CreateDay] < @p9) OR (([t0].[CreateDay] = @p10) AND ([t0].[CreateTime] < 
    @p11)))</inputbuf> 
      </process>
    - <process id="process3f43948" taskpriority="0" logused="1196" waitresource="PAGE: 
    21:1:916625" waittime="4954" ownerId="2059643491" transactionname="user_transaction" 
    lasttranstarted="2012-11-16T10:10:40.853" XDES="0xe2fe05a10" lockMode="S" 
    schedulerid="2" kpid="7700" status="suspended" spid="6054" sbid="0" ecid="7" priority="0" 
    trancount="0" lastbatchstarted="2012-11-16T10:10:40.963" 
    lastbatchcompleted="2012-11-16T10:10:40.933" clientapp=".Net SqlClient Data Provider" 
    hostname="HT1" hostpid="23452" isolationlevel="read committed (2)" xactid="2059643491" 
    currentdb="21" lockTimeout="4294967295" clientoption1="671088672" 
    clientoption2="128056">
    - <executionStack>
      <frame procname="adhoc" line="1" stmtstart="198" sqlhandle="0x020000002df9a61c5246f8f6aff5ac4f566bafdb9a0de779">SELECT 
    COUNT(*) AS [value] FROM [dbo].[TRANF1] AS [t0] WHERE ([t0].[HHNo] = @p0) AND 
    (([t0].[ApType] = @p1) OR ([t0].[ApType] = @p2) OR ([t0].[ApType] = @p3) OR [t0].[ApType] = @p4)) AND ([t0].[SNo] <> @p5) AND (([t0].[DataStatus] = @p6) OR 
    ([t0].[DataStatus] = @p7) OR ([t0].[DataStatus] = @p8)) AND (([t0].[CreateDay] < @p9) 
    OR (([t0].[CreateDay] = @p10) AND ([t0].[CreateTime] < @p11)))</frame> 
      <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown
    </frame> 
      </executionStack>
      <inputbuf /> 
      </process>
    - <process id="processcb522e2c8" taskpriority="0" logused="10000" waittime="14718" 
    schedulerid="4" kpid="12232" status="suspended" spid="6054" sbid="0" ecid="0" priority="0" 
    trancount="1" lastbatchstarted="2012-11-16T10:10:40.963" 
    lastbatchcompleted="2012-11-16T10:10:40.933" clientapp=".Net SqlClient Data Provider" 
    hostname="HT1" hostpid="23452" loginname="USER01" isolationlevel="read committed (2)" 
    xactid="2059643491" currentdb="21" lockTimeout="4294967295" clientoption1="671088672" 
    clientoption2="128056">
    - <executionStack>
      <frame procname="adhoc" line="1" stmtstart="198" sqlhandle="0x020000002df9a61c5246f8f6aff5ac4f566bafdb9a0de779">SELECT 
    COUNT(*) AS [value] FROM [dbo].[TRANF1] AS [t0] WHERE ([t0].[HHNo] = @p0) AND 
    (([t0].[ApType] = @p1) OR ([t0].[ApType] = @p2) OR ([t0].[ApType] = @p3) OR 
    ([t0].[ApType] = @p4)) AND ([t0].[SNo] <> @p5) AND (([t0].[DataStatus] = @p6) OR 
    ([t0].[DataStatus] = @p7) OR ([t0].[DataStatus] = @p8)) AND (([t0].[CreateDay] < @p9) 
    OR (([t0].[CreateDay] = @p10) AND ([t0].[CreateTime] < @p11)))</frame> 
      <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown</frame> 
      </executionStack>
      <inputbuf>(@p0 int,@p1 int,@p2 int,@p3 int,@p4 int,@p5 int,@p6 int,@p7 int,@p8 int,@p9 
    int,@p10 int,@p11 int)SELECT COUNT(*) AS [value] FROM [dbo].[TRANF1] AS [t0] WHERE ([t0].[HHNo] = @p0) AND (([t0].[ApType] = @p1) OR ([t0].[ApType] = @p2) OR
     ([t0].[ApType] = @p3) OR ([t0].[ApType] = @p4)) AND ([t0].[SNo] <> @p5) AND
     (([t0].[DataStatus] = @p6) OR ([t0].[DataStatus] = @p7) OR ([t0].[DataStatus] = @p8))
     AND (([t0].[CreateDay] < @p9) OR (([t0].[CreateDay] = @p10) AND ([t0].[CreateTime] < 
    @p11)))</inputbuf> 
      </process>
      </process-list>
    - <resource-list>
    - <pagelock fileid="1" pageid="916625" dbid="21" objectname="DB1.dbo.TRANF1" id="lock4e9af3d80" mode="IX" associatedObjectId="72057594124894208">
    - <owner-list>
      <owner id="processcb522e2c8" mode="IX" /> 
      </owner-list>
    - <waiter-list>
      <waiter id="process4009948" mode="S" requestType="convert" /> 
      </waiter-list>
      </pagelock>
    - <pagelock fileid="1" pageid="916625" dbid="21" objectname="DB1.dbo.TRANF1" id="lock4e9af3d80" mode="IX" associatedObjectId="72057594124894208">
    - <owner-list>
      <owner id="process4009948" mode="IX" /> 
      </owner-list>
    - <waiter-list>
      <waiter id="process3f43948" mode="S" requestType="wait" /> 
      </waiter-list>
      </pagelock>
    - <exchangeEvent id="Pipe50f200500" WaitType="e_waitPipeGetRow" nodeId="2">
    - <owner-list>
      <owner id="process3f43948" /> 
      </owner-list>
    - <waiter-list>
      <waiter id="processcb522e2c8" /> 
      </waiter-list>
      </exchangeEvent>
      </resource-list>
      </deadlock>
      </deadlock-list>



    • 已編輯 weiping 2012年11月19日 上午 03:50
    2012年11月16日 上午 03:34

解答

所有回覆

  • Is it part of sp? What's object 72057594124894208?
    2012年11月16日 上午 04:37
  • 若是不考慮transaction,不知道有沒試過(NOLOCK)?
    2012年11月16日 上午 05:06
  • 您好,
    看起來TRANF1應該是被長的交易給Lock了,
    要等交易結束才會回傳正確的資料。
    不然您就要改成dirty read的方式,
    在SQL中加入WITH (NOLOCK)。

    您可以使用Extended Events來看會比較清楚.

    SQL Server 2008使用Extended Events SSMS Addin + Performance Dashboard Reports來監看系統


    以上說明若有錯誤請指教,謝謝。
    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

    2012年11月16日 上午 05:15
  • 您好,
    可以RUN一下,看看72057594124894208是什麼嗎?

    SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE partition_id = 72057594124894208

    可透過SQL PROFILER去勾選 Locks下的Deadlock graph,來看會比較清楚哦。


    以上說明若有錯誤請指教,謝謝。
    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/


    • 已編輯 亂馬客 2012年11月16日 上午 06:36
    2012年11月16日 上午 06:28
  • 執行結果是TRANF1,謝謝.

    下列圖形是用SQL PROFILER產生的Deadlock graph

    • 已編輯 weiping 2012年11月16日 上午 07:21
    2012年11月16日 上午 06:42
  • 您在百敬老師的BLOG上有提問,老師已經回答了,不妨可以去看看。

    http://byronhu.wordpress.com/2011/05/18/%e9%99%90%e5%ae%9a%e8%b3%87%e6%96%99%e8%a1%a8%e5%83%85%e8%83%bd%e7%94%a8-table-lock/


    以上說明若有錯誤請指教,謝謝。
    歡迎參觀我的BLOG - 積沙成塔

    • 已標示為解答 weiping 2012年11月19日 上午 02:34
    2012年11月16日 上午 08:42
    版主
  • You should find out what statement did 5204 and 9021 run.
    • 已編輯 rmiao 2012年11月16日 下午 04:19
    2012年11月16日 下午 04:18
  • 您好,

    您移到5204 及 9021 的圈圈上,會顯示它的SQL,請問它的SQL是什麼呢?

    看起來是試圖要去拿一個排它的鎖。


    以上說明若有錯誤請指教,謝謝。
    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

    2012年11月16日 下午 06:24
  • 只是select的话是不会发生死锁的,LZ可以按照達斯汀大侠和乱马客大侠的话,使用with(nolock)

    至于为什麽会有IX LOCK意向排他锁,我觉得在读取表数据之前肯定是有人对表数据进行修改,这个人不是使用你的程式来访问数据表的

    共享锁的解释:

    共享锁:共享锁(S锁)允许并发事务在封闭式并发控制下读取select资源。资源上存在共享锁(S锁)时,任何其他事务都不能修改数据

    因为已经有IX锁了,所以你就不能获取共享锁,读取表数据

    如果一个事务申请了在某个资源上的排他锁(X锁),则在他释放排他锁(X锁)之前,其他事务均无法获取该资源的任何类型(共享,更新,排他)
    的锁。

    另一种情况是,如果一个事务已经获得了某个资源上的共享锁(S锁),则即使第一个事务尚未完成,其他事务也可以获取该项的共享锁,
    更新锁(U锁)。但是,在第一个事务释放共享锁之前,其他事务无法获取排他锁

    就是说有人正在修改表数据,使用排他锁,而你的程式要读取表数据使用共享锁,你的程式等待排他锁的释放,而修改数据的那个人等待你的共享锁的释放


    给我写信: QQ我:点击这里给我发消息

    2012年11月17日 上午 04:32
  • --查询阻塞与死锁的sql语句
    --1、
    --排在前两位的等待状态有下面几个:asynch_io_,completion,io_completion,logmgr,writelog,pageiolatch_x
    --这些等待状态意味着有I/O等待
    --如果排在前两位的等待状态以这样开头:LCK_M_?? 说明系统经常有阻塞
    SELECT TOP 2 [wait_type] FROM sys.[dm_os_wait_stats] ORDER BY [wait_time_ms] DESC


    --2、阻塞发生频率
    EXEC [sys].[sp_helpdb] @dbname = gposdb -- sysname 要查询阻塞的数据库 
    SELECT * FROM sys.[dm_db_index_operational_stats](10,NULL,NULL,null) --根据上面的dbid填入第一个参数

    --3、开启阻塞事件报告
    EXEC [sys].[sp_configure] @configname = 'blocked process threshold', -- varchar(35)
        @configvalue = 1 -- int
        RECONFIGURE

    --4、平均阻塞时间 建议阀值>100ms
    EXEC [sys].[sp_helpdb] @dbname = gposdb -- sysname 要查询阻塞的数据库 
    SELECT [row_lock_wait_in_ms],[page_lock_wait_in_ms],[page_latch_wait_in_ms],[page_io_latch_wait_in_ms] FROM sys.[dm_db_index_operational_stats](10,NULL,NULL,null) --根据上面的dbid填入第一个参数

    --5、查询当前数据库上所有用户表格在row lock上发生阻塞的频率
    --查询当前数据库上所有用户表在row lock上发生的阻塞频率
    use GPOSDB   --要查询阻塞的数据库
    DECLARE @dbid INT
    SELECT @dbid=DB_ID()
    SELECT 
    dbid=database_id,
    objectname=OBJECT_NAME(s.object_id),
    indexname=i.name,
    i.index_id,
    partition_number,
    row_lock_count,
    row_lock_wait_count,
    [block%]=CAST(100*row_lock_wait_count/(1+row_lock_count)AS NUMERIC(15,2)),
    row_lock_wait_in_ms,
    [avg row lock waits in ms]=CAST(1*row_lock_wait_in_ms/(1+row_lock_wait_count)AS NUMERIC(15,2)) 
    FROM sys.dm_db_index_operational_stats(@dbid,NULL,NULL,null) AS s,
    sys.indexes AS i
    WHERE OBJECTPROPERTY(s.object_id,'IsUserTable')=1
    AND i.object_id=s.object_id
    AND i.index_id=s.index_id
    ORDER BY row_lock_wait_count DESC


    2012年11月17日 上午 06:11
  • --锁模式兼容性:
    --请求模式                   IS          S               U          IX          SIX            X
    --意向共享(IS)          是          是             是         是          是           否
    --共享(S)                 是          是             是         否          否           否
    --更新(U)                 是          是             否         否          否           否
    --意向排他((IX)         是          否             否         是          否           否
    --意向排他共享(SIX)  是          否             否         否          否           否
    --排他(X)                 否          否             否         否          否           否

                                   IS           S                U          IX           SIX           X
    --意向共享(IS)        是          是             是          是          是            否
    --共享(S)               是          是             是          否          否            否


                                       IS          S                U          SIX            X
    IX  意向排他((IX)       是          否              否          否           否

    你的程式的select语句使用了意向共享(IS) 锁,而其他人要修改表数据意向排他((IX)锁需要你释放意向共享(IS) 锁,最后结果就是死锁


    给我写信: QQ我:点击这里给我发消息








    2012年11月17日 上午 06:59
  • 2012年11月18日 上午 08:08
    版主
  • dear  ricoisme,

    感謝您還附上範例說明,很詳盡,我分析了一下程式的查詢指令,確實是有用到平行處理,我再把solution提供AP人員參考,謝謝您.


    • 已編輯 weiping 2012年11月19日 上午 02:43
    2012年11月19日 上午 02:43