sql—在执行存储过程时触发死锁

dzhpxtsq  于 2021-07-29  发布在  Java
关注(0)|答案(0)|浏览(272)

我在两张table上有触发器。在触发器内部，有一个存储过程执行，然后是一个update语句。两个表的触发器引用相同的存储过程和update语句。
然而，不总是，但有几次我得到死锁错误如下。
我试图理解死锁，但无法找出什么是错误的，因为我是处理死锁问题的新手。
编辑：
在触发器中执行存储过程的目的是更新正在交易的客户的“未付款”。数据库中大约有15个表，为客户保存各种类型的事务。e、 g收据、销售、付款、采购等是表格。我试图归档的是，如果事务记录在这些表中的任何一个表中，请使用触发器来计算客户未付款。触发器内部使用的存储过程检查所有15个表中的所有事务并返回未付金额，然后触发器中的下一个语句使用此值更新customermaster表中该特定客户的记录。
简言之：存储过程查找特定客户事务的所有15个表，并返回浮点值。
在所有15个表中使用触发器来执行此存储过程，以获取未完成的值，并在这15个表中的每个事务之后更新customermaster表中的值。
我如何解决这个僵局？

  1. deadlock-list
  2.  deadlock victim=process269c4decca8
  4.   process-list
  6.    process id=process269c4decca8 taskpriority=0 logused=436 waitresource=PAGE: 7:1:1066036  waittime=4697 ownerId=84823833 transactionname=INSERT lasttranstarted=2020-07-17T14:09:40.623 XDES=0x26ab7dc9c90 lockMode=S schedulerid=2 kpid=10976 status=suspended spid=564 sbid=0 ecid=4 priority=0 trancount=0 lastbatchstarted=2020-07-17T14:09:40.620 lastbatchcompleted=2020-07-17T14:09:40.630 lastattention=1900-01-01T00:00:00.630 clientapp=.Net SqlClient Data Provider hostname=COLLECTION_S01 hostpid=8596 isolationlevel=read committed (2) xactid=84823833 currentdb=7 lockTimeout=4294967295 clientoption1=671219744 clientoption2=128056
  7.     executionStack
  8.      frame procname=demo.dbo.SP_GET_Outstanding_on_CustomerID_LedgerID line=14 stmtstart=796 stmtend=19944 sqlhandle=0x03000700e5c546737bebfe00f9ab000001000000000000000000000000000000000000000000000000000000
  9. Select Round(SUM(B.Debit),1) - Round(SUM(B.Credit),1) as Outstanding from
  10. (
  11. SELECT ISNULL(SUM(Debit),0) as 'Debit',ISNULL(SUM(Credit),0)  as 'Credit' from  
  12. (
  13. --declare @ledgerid as float = 5492
  14. Select Sum(Cast(CNamount as Decimal(28,2))) as Debit,0 as Credit
  15. From ServiceStock as SS Left Join demouniversal.dbo.DistributorMaster D on (SS.SendDistributorID = D.DistributorID) Left Join ExpenseLedgerDetails ED on D.DistributorID= ED.DistributorID 
  16. Where ED.LedgerID=@ledgerid 
  17. UNION ALL
  18. Select 0 as Debit,Sum(Cast((P.BilledQty * P.UnitNLC) as Decimal(28,2))) as Credit
  19. From pur_vir_inw_det P left join pur_vir_inw_mst V on P.VPINo = V.VPINo left join demouniversal.dbo.DistributorMaster D on (V.CompanyID = D.DistributorID) Left Join ExpenseLedgerDetails ED on D.DistributorID= ED.DistributorID 
  20. Where ED.LedgerID=@ledgerid
  21. Union all
  22. Select 0 as Debit,Sum(IsNull(Freight,0) + IsNull(SGSTAmt,0) + IsNull(CGSTAmt,0) - ISNULL(V.Discount,0)) as Credit
  23. From pur_vir_inw_mst V  
  24. left join demouniv     
  25.      frame procname=adhoc line=1 stmtstart=74 stmtend=352 sqlhandle=0x010008006b50070c20fc71396b02000000000000000000000000000000000000000000000000000000000000
  26. EXEC demo.[dbo].[SP_GET_Outstanding_on_CustomerID_LedgerID] @ledgerid = 4955, @customerid = 4955, @ResultValue2= @ResultValue4 outpu     
  27.      frame procname=demouniversal.dbo.SP_GET_Outstanding_on_CustomerID_For_All_database line=59 stmtstart=5272 stmtend=5708 sqlhandle=0x0300080053f6d95222360101f9ab000001000000000000000000000000000000000000000000000000000000
  28. insert into @temp exec ('declare @ResultValue4 decimal (16,2) EXEC '+@db+'.[dbo].[SP_GET_Outstanding_on_CustomerID_LedgerID] @ledgerid = '+@LedgerID+', @customerid = '+@cust_id_T+', @ResultValue2= @ResultValue4 output'     
  29.      frame procname=demo.dbo.Update_OS_SP_ExpJournal line=35 stmtstart=3816 stmtend=4062 sqlhandle=0x03000700aa4e4c550e7f0101f9ab000000000000000000000000000000000000000000000000000000000000
  30. EXEC demouniversal.[dbo].[SP_GET_Outstanding_on_CustomerID_For_All_database] null, @in_FromLedgerId, @return_status outpu     
  31.      frame procname=adhoc line=1 stmtstart=204 stmtend=586 sqlhandle=0x02000000c012c63838e02bd6dca13d67f09290998bcb26fd0000000000000000000000000000000000000000
  32. unknown     
  33.      frame procname=adhoc line=1 stmtend=412 sqlhandle=0x0200000020cd1b370590d65507e440a267d97d962bda59710000000000000000000000000000000000000000
  34. unknown     
  35.     inputbuf
  36. INSERT INTO demo.dbo.ExpenseJournal (EmployeeID,TransactionDate,FromLedger,ToLedger,Amount,Remarks,Type,RefName,Dflag,ID) VALUES (1658,'2020-07-15',4955,2936,350000,'being cash deposite',1,'',0,44520)    
  38.    process id=process269e701e4e8 taskpriority=0 logused=1256 waitresource=PAGE: 7:1:541270  waittime=4734 ownerId=84826380 transactionname=user_transaction lasttranstarted=2020-07-17T14:09:40.753 XDES=0x26a01d2c490 lockMode=S schedulerid=1 kpid=2724 status=suspended spid=303 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2020-07-17T14:09:41.200 lastbatchcompleted=2020-07-17T14:09:40.750 lastattention=1900-01-01T00:00:00.750 clientapp=.Net SqlClient Data Provider hostname=CASH_S05 hostpid=4268 loginname=ims isolationlevel=read committed (2) xactid=84826380 currentdb=8 lockTimeout=4294967295 clientoption1=671219744 clientoption2=128056
  39.     executionStack
  40.      frame procname=adhoc line=1 stmtend=3096 sqlhandle=0x0200000018ac5a254532fb705262b54a8e0a4b9689bc29ac0000000000000000000000000000000000000000
  41. unknown     
  42.      frame procname=demouniversal.dbo.SP_GET_Outstanding_on_CustomerID_For_All_database line=41 stmtstart=4356 stmtend=4408 sqlhandle=0x0300080053f6d95222360101f9ab000001000000000000000000000000000000000000000000000000000000
  43. INSERT INTO @t1 exec (@sql     
  44.      frame procname=demo.dbo.Update_OS_SP_Receipt line=16 stmtstart=1486 stmtend=1728 sqlhandle=0x03000700a908b74c60cc2e01f9ab000000000000000000000000000000000000000000000000000000000000
  45. EXEC demouniversal.[dbo].[SP_GET_Outstanding_on_CustomerID_For_All_database] @in_customerId, NULL, @return_status Outpu     
  46.      frame procname=adhoc line=1 stmtstart=696 stmtend=1770 sqlhandle=0x02000000a1f53a36574a899cbd5dca29c8d01e26bb8988860000000000000000000000000000000000000000
  47. unknown     
  48.      frame procname=adhoc line=1 stmtend=1370 sqlhandle=0x020000003f4b62186ff8b7e2ab4637ce6fb107b9df1659cc0000000000000000000000000000000000000000
  49. unknown     
  50.     inputbuf
  51. Insert into Receipt (StoreID,ReceiptNo,Date,RTime,InvoiceNo,Cashier,SalesEx,CustomerID,DCustomerID,DCustomerName,DMobile,DPhone,DAddress,DCity,BCustomerName,BMobile,BPhone,BAddress,BCity,BDOB,PaymentMode,Amount,IsAdvancedReceipt,CODID,FromInvOrCOD,AmountExclVAT,UpdatedDate,RecTallyflag,Remarks) values ('S05','RC299970','2020-07-17',CONVERT(VARCHAR(8), GETDATE(), 108),'IV478531',845,480,350986,350986,'ABC XYZ','9999999999','9999999999','Test',1102,'ABC XYZ','9999999999','9999999999','Test',1102,'1996-12-14',0,5891,0,-1,1,0,CONVERT(VARCHAR(23), GETDATE(), 120),0,'')    
  53.    process id=process26a5ae8c108 taskpriority=0 logused=10000 waittime=4692 schedulerid=6 kpid=10760 status=suspended spid=564 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2020-07-17T14:09:40.620 lastbatchcompleted=2020-07-17T14:09:40.630 lastattention=1900-01-01T00:00:00.630 clientapp=.Net SqlClient Data Provider hostname=COLLECTION_S01 hostpid=8596 loginname=ims isolationlevel=read committed (2) xactid=84823833 currentdb=7 lockTimeout=4294967295 clientoption1=671219744 clientoption2=128056
  54.     executionStack
  55.      frame procname=demo.dbo.SP_GET_Outstanding_on_CustomerID_LedgerID line=14 stmtstart=796 stmtend=19944 sqlhandle=0x03000700e5c546737bebfe00f9ab000001000000000000000000000000000000000000000000000000000000
  56. Select Round(SUM(B.Debit),1) - Round(SUM(B.Credit),1) as Outstanding from
  57. (
  58. SELECT ISNULL(SUM(Debit),0) as 'Debit',ISNULL(SUM(Credit),0)  as 'Credit' from  
  59. (
  60. --declare @ledgerid as float = 5492
  61. Select Sum(Cast(CNamount as Decimal(28,2))) as Debit,0 as Credit
  62. From ServiceStock as SS Left Join demouniversal.dbo.DistributorMaster D on (SS.SendDistributorID = D.DistributorID) Left Join ExpenseLedgerDetails ED on D.DistributorID= ED.DistributorID 
  63. Where ED.LedgerID=@ledgerid 
  64. UNION ALL
  65. Select 0 as Debit,Sum(Cast((P.BilledQty * P.UnitNLC) as Decimal(28,2))) as Credit
  66. From pur_vir_inw_det P left join pur_vir_inw_mst V on P.VPINo = V.VPINo left join demouniversal.dbo.DistributorMaster D on (V.CompanyID = D.DistributorID) Left Join ExpenseLedgerDetails ED on D.DistributorID= ED.DistributorID 
  67. Where ED.LedgerID=@ledgerid
  68. Union all
  69. Select 0 as Debit,Sum(IsNull(Freight,0) + IsNull(SGSTAmt,0) + IsNull(CGSTAmt,0) - ISNULL(V.Discount,0)) as Credit
  70. From pur_vir_inw_mst V  
  71. left join demouniv     
  72.      frame procname=adhoc line=1 stmtstart=74 stmtend=352 sqlhandle=0x010008006b50070c20fc71396b02000000000000000000000000000000000000000000000000000000000000
  73. EXEC demo.[dbo].[SP_GET_Outstanding_on_CustomerID_LedgerID] @ledgerid = 4955, @customerid = 4955, @ResultValue2= @ResultValue4 outpu     
  74.      frame procname=demouniversal.dbo.SP_GET_Outstanding_on_CustomerID_For_All_database line=59 stmtstart=5272 stmtend=5708 sqlhandle=0x0300080053f6d95222360101f9ab000001000000000000000000000000000000000000000000000000000000
  75. insert into @temp exec ('declare @ResultValue4 decimal (16,2) EXEC '+@db+'.[dbo].[SP_GET_Outstanding_on_CustomerID_LedgerID] @ledgerid = '+@LedgerID+', @customerid = '+@cust_id_T+', @ResultValue2= @ResultValue4 output'     
  76.      frame procname=demo.dbo.Update_OS_SP_ExpJournal line=35 stmtstart=3816 stmtend=4062 sqlhandle=0x03000700aa4e4c550e7f0101f9ab000000000000000000000000000000000000000000000000000000000000
  77. EXEC demouniversal.[dbo].[SP_GET_Outstanding_on_CustomerID_For_All_database] null, @in_FromLedgerId, @return_status outpu     
  78.      frame procname=adhoc line=1 stmtstart=204 stmtend=586 sqlhandle=0x02000000c012c63838e02bd6dca13d67f09290998bcb26fd0000000000000000000000000000000000000000
  79. unknown     
  80.      frame procname=adhoc line=1 stmtend=412 sqlhandle=0x0200000020cd1b370590d65507e440a267d97d962bda59710000000000000000000000000000000000000000
  81. unknown     
  82.     inputbuf
  83. INSERT INTO demo.dbo.ExpenseJournal (EmployeeID,TransactionDate,FromLedger,ToLedger,Amount,Remarks,Type,RefName,Dflag,ID) VALUES (1658,'2020-07-15',4955,2936,350000,'being cash deposite',1,'',0,44520)    
  85.   resource-list
  86.    pagelock fileid=1 pageid=1066036 dbid=7 subresource=FULL objectname=demo.dbo.receipt id=lock26b09d53500 mode=IX associatedObjectId=72057594114211840
  87.     owner-list
  88.      owner id=process269e701e4e8 mode=IX
  90.     waiter-list
  91.      waiter id=process269c4decca8 mode=S requestType=wait
  92.    pagelock fileid=1 pageid=541270 dbid=7 subresource=FULL objectname=demo.dbo.ExpenseLedgerDetails id=lock2694e2aa100 mode=SIX associatedObjectId=72057594121224192
  94.     owner-list
  95.      owner id=process26a5ae8c108 mode=SIX
  97.     waiter-list
  98.      waiter id=process269e701e4e8 mode=S requestType=wait
  99.    exchangeEvent id=Pipe26aaaf70b80 WaitType=e_waitPipeGetRow nodeId=214
  101.     owner-list
  102.      owner id=process269c4decca8
  103.     waiter-list
  104.      waiter id=process26a5ae8c108
sqlsql-server

暂无答案！

目前还没有任何答案，快来回答吧！

我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com