我每 30 秒进行一次大批量插入我目前在运行 sp_whoisactive 时看到很多阻塞在过去的两周里,我们从 2016 年升级到 sql server ent 2017(应用了最新更新)我不是确定这是否相关,或者数据库现在是否更忙。
从我的批次中,我在 whoisactive 中看到了这个报告
Delete from [dbo].[tblStagingTrackingData] Where ipkStagingTrackingID in(Select ifkStagingTrackingID from #AboutToInsert)
该临时表不被其他任何东西使用,并且阻塞发生在完全不相关的表上
这是完整的批处理 sql
ALTER PROCEDURE [dbo].[sp_BatchInsertInCommonTrackingTable]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRANSACTION
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Create a batch table to insert records
Create Table #AboutToInsert ([ID] [bigint] IDENTITY(1,1) NOT NULL Primary Key,[ifkStagingTrackingID] [bigint] NOT NULL,[vpkDeviceID] [bigint] NULL,
[vLongitude] [varchar](20) NULL,[vLatitude] [varchar](20) NULL,[vHeading] [int] NULL,[vReportID] [int] NULL,[vOdometer] [decimal](12, 3) NULL,
[vVehicleSpeed] [int] NULL,[vTextMessage] [nvarchar](250) NULL,[ifkDriverID] [int] NULL,[bIsIgnitionOn] [bit] NULL,
[bIsEngineOn] [bit] NULL,[dGPSDateTime] [datetime] NULL,[cInput1] [char](1) NULL,[cInput2] [char](1) NULL,[cInput3] [char](1) NULL,[cInput4] [char](1) NULL,
[cInput5] [char](1) NULL,[cInput6] [char](1) NULL,[vTempSensor1] [varchar](6) NULL,[vTempSensor2] [varchar](6) NULL,[nAltitude] [varchar](7) NULL,[iHdop] [int] NULL,
[iBatteryBackup] [int] NULL,[vAnalog1] [varchar](10) NULL,[vAnalog2] [varchar](10) NULL,[cOutput1] [char](1) NULL,[cOutput2] [char](1) NULL,[vRoadSpeed] [varchar](4) NULL,
[bIsGsmLocation] [bit] NOT NULL Default (0),[vAnalog1Raw] [nvarchar](6) NULL,[vAnalog2Raw] [nvarchar](6) NULL,[Cell_MMC] [varchar](20) NULL,[Cell_MNC] [varchar](20) NULL,
[Cell_Lac] [varchar](20) NULL,[Cell_ID] [varchar](20) NULL,[Fuel1_Litres] [smallint] NULL,[Fuel1_Raw] [float] NULL,[SensorData] [varchar](max) NULL,[additionalEventInfo] [varchar](255) NULL,
[ObdData] [varchar](max) NULL,[TripType] [smallint] NULL,[Attributes] [varchar](max) NULL,[EngineHours] [bigint] NULL,[DbDate] [datetime2](7) NULL,[RawData] [varchar](4000) NULL,
[isLastRecord] [bit] NOT NULL default (0)
)
--Create a Latest Records table
Create Table #LatestRecordsToInsert ([ID] [bigint] IDENTITY(1,1) NOT NULL Primary Key,[ifkStagingTrackingID] [bigint] NOT NULL,[vpkDeviceID] [bigint] NULL,
[vLongitude] [varchar](20) NULL,[vLatitude] [varchar](20) NULL,[vHeading] [int] NULL,[vReportID] [int] NULL,[vOdometer] [decimal](12, 3) NULL,
[vVehicleSpeed] [int] NULL,[vTextMessage] [nvarchar](250) NULL,[ifkDriverID] [int] NULL,[bIsIgnitionOn] [bit] NULL,
[bIsEngineOn] [bit] NULL,[dGPSDateTime] [datetime] NULL,[cInput1] [char](1) NULL,[cInput2] [char](1) NULL,[cInput3] [char](1) NULL,[cInput4] [char](1) NULL,
[cInput5] [char](1) NULL,[cInput6] [char](1) NULL,[vTempSensor1] [varchar](6) NULL,[vTempSensor2] [varchar](6) NULL,[nAltitude] [varchar](7) NULL,[iHdop] [int] NULL,
[iBatteryBackup] [int] NULL,[vAnalog1] [varchar](10) NULL,[vAnalog2] [varchar](10) NULL,[cOutput1] [char](1) NULL,[cOutput2] [char](1) NULL,[vRoadSpeed] [varchar](4) NULL,
[bIsGsmLocation] [bit] NOT NULL Default (0),[vAnalog1Raw] [nvarchar](6) NULL,[vAnalog2Raw] [nvarchar](6) NULL,[Cell_MMC] [varchar](20) NULL,[Cell_MNC] [varchar](20) NULL,
[Cell_Lac] [varchar](20) NULL,[Cell_ID] [varchar](20) NULL,[Fuel1_Litres] [smallint] NULL,[Fuel1_Raw] [float] NULL,[SensorData] [varchar](max) NULL,[additionalEventInfo] [varchar](255) NULL,
[ObdData] [varchar](max) NULL,[TripType] [smallint] NULL,[Attributes] [varchar](max) NULL,[EngineHours] [bigint] NULL,[DbDate] [datetime2](7) NULL,[RawData] [varchar](4000) NULL,
[isLastRecord] [bit] NOT NULL default (0)
)
--Create ##LatestRecordsDirty table to keep track of the insert Latest Records
Create Table #LatestRecordsDirty(Id int identity(1,1), ifkCommonTrackingID Bigint, IMEI Bigint, GpsDateTime DateTime, DriverID int)
--Create #LatestRecordscleaned table to remove non latest
Create Table #LatestRecordsCleaned(Id int identity(1,1), ifkCommonTrackingID Bigint, IMEI Bigint, GpsDateTime DateTime, DriverID int)
Create Table #LatestRecordsJustInserted(Id int identity(1,1), ifkCommonTrackingID Bigint, IMEI Bigint, GpsDateTime DateTime, DriverID int)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Get latest records from staging table, and put in #LatestRecordsToInsert ( these might contain duplicates)
--Insert into #LatestRecordsToInsert
print 'start [tblStagingTrackingData] to #LatestRecordsToInsert'
Insert into #LatestRecordsToInsert([ifkStagingTrackingID],[vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
[bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
[vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
[ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData],[isLastRecord])
--Values
Select TOP 100000 [ipkStagingTrackingID],[vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
[bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
[vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
[ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData],[isLastRecord]
From [dbo].[tblStagingTrackingData] where [isLastRecord] = 1 order by dGPSDateTime asc
print 'end [tblStagingTrackingData] to #LatestRecordsToInsert'
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
print 'Fill Dirty'
INSERT INTO #LatestRecordsDirty(ifkCommonTrackingID, IMEI, GpsDateTime, DriverID)
select ifkStagingTrackingID, vpkDeviceID, dGPSDateTime, ifkDriverID FROM #LatestRecordsToInsert
print 'Fill Cleaned'
-- clean #LatestRecordsToInsert
--Filter Latest Records to remove multiple records for a single device
Insert into #LatestRecordsCleaned(ifkCommonTrackingID, IMEI, GpsDateTime, DriverID)
Select ifkStagingTrackingID, vpkDeviceID, dGPSDateTime, ifkDriverID From(
Select ifkStagingTrackingID, vpkDeviceID, dGPSDateTime, ifkDriverID,
row_number() over(partition by vpkDeviceID order by dGPSDateTime Desc) as Row From #LatestRecordsToInsert
) as A Where Row = 1
--select * from #LatestRecordsCleaned
-------------------------------------------------------------------------------------------------
--remove those from #latestRecords that are duplicate imei and put into bulk table for later
print 'delete from Dirty'
Delete from #LatestRecordsDirty Where ifkCommonTrackingID in(Select ifkCommonTrackingID from #LatestRecordsCleaned);
print 'populate #AboutToInsert with dirty latest'
--Insert into #AboutToInsert
Insert into #AboutToInsert([ifkStagingTrackingID],[vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
[bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
[vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
[ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData],[isLastRecord])
--Values
Select [ifkStagingTrackingID],[vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
[bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
[vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
[ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData],[isLastRecord]
From #LatestRecordsToInsert
where [ifkStagingTrackingID] in(Select ifkCommonTrackingID from #LatestRecordsDirty);
print 'end populate #AboutToInsert with dirty latest'
-------------------------------------------------------------------------------------------------
--Insert into Common Table from #LatestRecordsToInsert table and OUTput the Id's etc for updating latest record on devices table ( Not Distict IMEIs)
print 'start #LatestRecordsToInsert to tblCommonTrackingData - output into #LatestRecords'
Insert into tblCommonTrackingData([vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
[bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
[vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
[ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData])
--Insert the inserted records to the #LatestRecords table
OUTPUT inserted.ipkCommanTrackingID, inserted.vpkDeviceID, inserted.dGPSDateTime, inserted.ifkDriverID
INTO #LatestRecordsJustInserted
--Values
Select [vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
[bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
[vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
[ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData]
From #LatestRecordsToInsert
where [ifkStagingTrackingID] in(Select ifkCommonTrackingID from #LatestRecordsCleaned);
print 'end #LatestRecordsToInsert to tblCommonTrackingData - output into #LatestRecords'
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
print 'start update wlt_tblDevices from #LatestRecordsCleaned'
--Update the Device Table with the latest records
Update wlt_tblDevices
SET LastRecord=B.ifkCommonTrackingID, LastGpsDateTime =B.GpsDateTime
FROM wlt_tblDevices A
Inner join #LatestRecordsJustInserted B on B.IMEI= A.imeinumber;
print 'end update wlt_tblDevices from #LatestRecordsCleaned'
print 'start update wlt_tblAssets_Driver from #LatestRecordsCleaned'
--Update Drivers Table with the latest record
Update wlt_tblAssets_Driver
SET LastRecord=B.ifkCommonTrackingID
FROM wlt_tblAssets_Driver A
Inner join #LatestRecordsJustInserted B on B.DriverID= A.ipkDriverID
where B.DriverID is not null;
print 'end update wlt_tblAssets_Driver from #LatestRecordsCleaned'
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
print 'start [tblStagingTrackingData] to #AboutToInsert'
--Insert into #AboutToInsert
Insert into #AboutToInsert([ifkStagingTrackingID],[vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
[bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
[vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
[ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData],[isLastRecord])
--Values
Select TOP 100000 [ipkStagingTrackingID],[vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
[bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
[vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
[ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData],[isLastRecord]
From [dbo].[tblStagingTrackingData]
where [isLastRecord] = 0 order by dGPSDateTime asc;
print 'end [tblStagingTrackingData] to #AboutToInsert'
print 'start #AboutToInsert to tblCommonTrackingData'
--Insert into Common Table from #AboutToInsert table
Insert into tblCommonTrackingData([vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
[bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
[vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
[ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData])
--Values
Select [vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
[bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
[vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
[ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData]
From #AboutToInsert;
print 'end #AboutToInsert to tblCommonTrackingData'
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Delete all the records insert to the common tracking table from the StagingTrackingTable
print 'start delete [tblStagingTrackingData]'
Delete from [dbo].[tblStagingTrackingData] Where ipkStagingTrackingID in(Select ifkStagingTrackingID from #AboutToInsert);
Delete from [dbo].[tblStagingTrackingData] Where ipkStagingTrackingID in(Select ifkStagingTrackingID from #LatestRecordsToInsert);
print 'end delete [tblStagingTrackingData]'
--Drop the temporal tables
Drop Table #AboutToInsert
Drop Table #LatestRecordsToInsert
Drop Table #LatestRecordsDirty
Drop Table #LatestRecordsCleaned
Drop Table #LatestRecordsJustInserted
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMMIT
END TRY
BEGIN CATCH
print 'Rolling back'
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
ROLLBACK
END CATCH
END
sp_whoisactive 抱怨从 [tblStagingTrackingData] 中删除,但这是最后的操作之一,就在此之前,此语句是我希望锁定的:
插入 tblCommonTrackingData
似乎锁定发生在插入此表之后,
我需要做某种交易来阻止这种阻塞吗?或者有什么东西可以保持这张桌子上的锁,直到整个批次完成?
您尚未提交事务,并且所有排他锁(以及如果在可重复读取或可序列化中的所有共享锁)都将保留到事务结束。