linux - 如何在 MSSQL 中找到导致死锁的进程

Question

我正在从车载计算机到第三方应用程序的延迟到达和离开，该应用程序使用从 OBC 收集的信息生成 XML 文件。

我试图将数据库中某些事务的状态更新到所有第 3 方应用程序以处理信息，但它没有帮助。

以下是 Arrival 和 Depart OBC 接口 ( ) 的 MIF 调度程序代码中逻辑的高级时间顺序MPNL.Services:mapPacosEvent：

• 履行：

  Select uniqkey, status, vehicle_number, created_datetime, msn, base_msn, message_type, form_id, message_text from ALLINBOUNDMESSAGES where 
  status IN (0,6,7,8,9) AND (form_id = '003' OR form_id = '005') AND ((message_type = 'pacos') OR (message_type = 'form'))
  ORDER BY created_datetime
  

• 对于来自 message_text 的上述查询的每一行结果，获取 dispatch_number。例如

  Vehicle 103184 has arrived at Stop #5 (310466-000) at 2019-07-19 08:00:14 (local time).^886727^5^310466-000^41.412191^-73.454032^68139.250^401773.3
  

  这里 dispatch_id 是 886727）

• 履行：

  select dispatch_id, driver from dispatch where dispatch_number='${dispatch_number}' ORDER BY created_datetime DESC
  

• 履行：

  select status,dispatch_number,stop_number,scheduled_arrival,stopprofile_id,planned5, planned4 ,planned3 ,planned2 from dispstops where dispatch_id='${dispatch_id}' AND stop_number='${stop_number}' AND status IN (${StopStatusValue}) ORDER BY Status Asc ,stop_number DESC.
  

  这里从MPNLInboundInterfaces_ConfigFile.xml文件中读取 Stop_number 和 StopStatusVaues。

• 履行：

  select DISTINCT t1.login ,t1.login_datime_gmt from vehicleloginxref t1
  join [elogevents] t2 ON ((t1.vehicle_number = t2.vehicle_number) AND (t1.login = t2.driverid))
  where((t1.status = '${status}') AND (t1.userstatus = '${userstatus}') AND (t2.eid = '${eid}') AND (t1.vehicle_number='${vehicle_number}') AND (t1.logout_datime_gmt IS NULL)) ORDER BY login_datime_gmt DESC
  

  这里的输入是status和。eidvehicle_number

• 履行：

  select primary driver detail from TPNE using below query
    select secondary_driver, carrier_driver_id   from driver where secondary_driver != '' and secondary_driver is not null AND mark_for_deletion!='1'
  

• 履行：

  Select TP_company_ID,LAST_KNOWN_FACILITY_ALIAS_ID,CARRIER_ID from DRIVER where Carrier_Driver_ID ='${DriverID}' AND mark_for_deletion!='1'
  

• 履行：

  Select Last known FacilityAliasID from TPE
                             Select TP_company_ID,LAST_KNOWN_FACILITY_ALIAS_ID,CARRIER_ID from DRIVER where Carrier_Driver_ID ='${DriverID}' AND mark_for_deletion!='1'
                             select carrier_code from carrier_code where TP_company_ID= '${TPCompanyID}' and CARRIER_ID='${CARRIER_ID}'
                             select gmtoffset from opcenters where opcenter = (select opCenter from vehicles where vehicle_number = ’${vehicle_number}' )
                             select time_zone_name from time_zone where time_zone_id = (select min(time_zone_id)  from time_zone where gmt_offset = '${gmtoffset}')
                             select uniqkey,vehicle_number,created_datetime,msn,base_msn,form_id,dispatchstop,on_dock_time,trailer,pro,seal,detention_hours,auto_latlong,
  auto_location,auto_odometer,userflag1,userflag2,userfield1,userfield2,dispatch_number,stop_number from ${tablename1} where msn='${msn}' AND 
  base_msn='${base_msn}' AND vehicle_number='${vehicle_number}' AND userflag1 NOT in ('${successStatus}','${errorStatus}')
  

• 调度程序服务使用从数据库中提取的信息组合和 xml MPNL.Services:mapPacosEvent，Arrival_OBC并将其发送到 MIF。Depart_OBCTPNEPeopleNet

• 一旦发送到MIF成功调度程序的消息ALLINBOUNDMESSAGES使用以下查询更新表：

  Update status=’1’ where uniqkey=’’ and vehicle_number=’’ and message_type=’’ and form_id=’’;
  

• 如果发送Arrival_OBC和Depart_OBCxmls 失败，调度程序使用以下查询更新 ALLINBOUNDMESSAGES 表：

  Update status=’10’ where uniqkey=’’ and vehicle_number=’’ and message_type=’’ and form_id=’’
  

错误：

2019-07-17 14:02:00 EDT [ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ADA.1.316] Cannot execute the SQL statement "UPDATE  allinboundmessages SET status = ?  WHERE uniqkey = ? AND vehicle_number = ? AND message_type = ? AND form_id = ?". "
(40001/1205) Transaction (Process ID 78) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
Transaction (Process ID 78) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2019-07-17 14:02:00 EDT [ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service MPNL.AdapterServices:updateAllInboundMessageRecs.
[ADA.1.316] Cannot execute the SQL statement "UPDATE  allinboundmessages SET status = ?  WHERE uniqkey = ? AND vehicle_number = ? AND message_type = ? AND form_id = ?". "
(40001/1205) Transaction (Process ID 78) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
Transaction (Process ID 78) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

标签： linux

Answer 1

如果您运行存储的 proc sp_who2，它将显示哪些进程被阻塞以及哪些进程正在执行阻塞。