Sql-Server

SQL Server 中的 ODBC 連接鎖定表

  • January 19, 2017

我們有一個略低於現代的 ERP 系統，後端目前安裝在 SQL Server 2008 R2 上。我們通過管理授權（並非總是如此）完全不切實際的做法是允許許多使用者隨意從該系統中提取數據，以便將數據處理成他們喜歡的報告風格（BI、Powerquery、Access , Excel 等)

儘管所有這些生成報告的使用者都只有選擇權限，但還有一些其他應用程序能夠實際操作數據以執行諸如 EDI、自動數據輸入等目的。

從邏輯上講，這會在表上創建許多競爭條件。先到先得，或者更糟糕的是，仍在服務，請稍候，或者只是不…我相信它會進一步導致多年來無法解釋的數據損壞，需要供應商協助糾正。因為並非所有應用程序都經過正確編碼，以預測其他事情可能會打斷它自己的私人意圖的可能性。

因此，任務是在我最近繼承的系統上盡可能地證明/糾正/減輕它。

我的第一直覺告訴我，如果不同供應商的產品之間沒有某種 IPC/錯誤/事務控制以及他們修復它的意願、對原始碼的控製或限制它的能力，這是無法從 DBA 修復的從角度來看，這只是導致應該預期的潛在不良副作用的不良做法。

所以在這一點上我所能做的就是證明它，看看如果有證據，我可以在管理層的心中徵求某種行為改變，堅持必須這樣。

第一步，我正在執行一個連續的分析器輸出到磁碟，儘管資源密集，但我必須能夠返回並查看送出的內容、送出者以及它產生的影響。我還在使用 perfmon 日誌記錄和 PAL 來嘗試在伺服器上交叉引用密集型行為，“當處理器和記憶體掛鉤時，SQL 伺服器在那個實例上做了什麼（很可能為編寫的一個 GUI 提供服務，性能不佳，十個表連接，非索引查詢，一些報告編寫器/工具組合在一起”

剛剛發生了其中一種情況，我們在 ERP 系統中有一個使用者無法執行某項功能，我們辨識出登錄到數據庫的使用者沒有使用 ERP 軟體（使用 ODBC、具有僅選擇訪問權限的 SQL 使用者，以及MS Access），讓他們斷開連接並且功能通過。管理層拒絕相信他們是相關的，因為他們“應該一直在處理不同的表格，並且使用者只有選擇訪問權限”並且我沒有在它發生之前的瞬間的個人資料歷史記錄。

因此，所有這些都歸結為對 DBA 研究員的一個問題，考慮到證明應用程序 A 受到應用程序 B 的不利影響的任務，您必須提供什麼建議*？*

不幸的是，我們在我工作的地方也處理了可能的問題。如果這是一個問題，不了解數據庫的人會在 MS Access 中“實時”打開一個 SQL 表。起初，事情可能看起來很短暫，因為它們可能會快速打開和關閉 Access。但也有一些時候，當他們外出吃午飯或度過長周末時，他們在實時 SQL 表上保持 MS Access 處於打開狀態 - 阻止嘗試使用數據庫的應用程序。很明顯，阻塞會對任何試圖使用阻塞的 SQL 行、表或數據庫的應用程序產生不利影響。

為了證明任何事情，我們真正需要做的就是從中擷取數據sp_who2以找到阻塞鏈。

話雖如此，當我離開控制台時，我仍然使用我十多年前編寫的以下程式碼（我的意思是我仍在使用 DBCC InputBuffer）來查找這些討厭的問題——如果需要（請在測試/開發環境，看看它是否滿足您的需求）。雖然肯定不是勞斯萊斯的套路，但它仍然對我有用。

這段程式碼本質上是在一個新的數據庫 DBADMIN 中創建一個表 BlockingProcesses 來儲存阻塞資訊。兩個儲存過程sp__Maint_BlockWatch一起sp__Maint_Blockingprocesses工作來尋找阻塞器。SQL 作業每分鐘執行一次以檢查塊。您可能需要更改以下程式碼中的一些小事情，例如需要執行作業的使用者、可能的調度等。

CREATE DATABASE DBADMIN
GO

USE [dbadmin]
GO

SET ANSI_NULLS ON
GO   
SET QUOTED_IDENTIFIER ON
GO    
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BlockingProcesses]
(
   [PK] [int] IDENTITY(1,1) NOT NULL,
   [last_batch] [datetime] NULL,
   [spid] [int] NULL,
   [BlockedTotal] [int] NULL,
   [LoginName] [varchar](128) NULL,
   [DBName] [varchar](128) NULL,
   [HostName] [varchar](128) NULL,
   [Program_Name] [varchar](255) NULL,
   [CPU] [bigint] NULL,
   [Physical_IO] [bigint] NULL,
   [Memusage] [bigint] NULL,
   [Open_tran] [int] NULL,
   [EventInfo] [varchar](max) NULL,
   [InsertTime] [smalldatetime] NULL,
   [Params] [varchar](500) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]    
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BlockingProcesses] 
  ADD DEFAULT (getdate()) FOR [InsertTime]
GO

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp__Maint_BlockWatch]
AS
   declare @blocker smallint

   DECLARE blocker_cursor CURSOR FOR 
        select distinct blocked 
        from sysprocesses 
        where blocked != 0

   OPEN blocker_cursor
   FETCH NEXT FROM blocker_cursor INTO @blocker

   WHILE (@@fetch_status <> -1)
   BEGIN
     IF (@@fetch_status = -2)
     BEGIN
 FETCH NEXT FROM blocker_cursor INTO @blocker
          CONTINUE
     END

 exec sp__Maint_Blockingprocesses @blocker

FETCH NEXT FROM blocker_cursor INTO @blocker
   END
DEALLOCATE blocker_cursor


GO

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[sp__Maint_BlockingProcesses]    Script Date: 09/20/2016 13:31:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE procedure [dbo].[sp__Maint_BlockingProcesses]
@spid smallint
as
declare
@blockedTotal int, 
@loginame varchar(128), 
@dbid int,
@hostname varchar(128),
@program_name varchar(128),
@cpu bigint,
@physical_io bigint,
@memusage bigint,
@open_tran int,
@dbname varchar(128),
@EventInfo varchar(255),
@Last_batch datetime
create table #temp
(
EventType varchar(50),
Parameters int,
EventInfo varchar(255)
)

insert into #temp
exec ('dbcc inputbuffer('+@spid+')')

select @EventInfo=eventinfo from #temp
select @blockedTotal= count(*) from sysprocesses where blocked = @spid
select @loginame=loginame, @dbid=dbid, @hostname=hostname, @program_name=program_name, @cpu=cpu,
@physical_io = physical_io, @memusage=[memusage], @open_tran=open_tran, @Last_batch=last_batch
from sysprocesses where spid=@spid
select @dbname = name from sysdatabases where dbid=@dbid
insert into dbadmin..blockingprocesses(last_batch,spid, BlockedTotal, LoginName, DBName, HostName, Program_Name, CPU, Physical_IO, Memusage, Open_tran, EventInfo)
values(@last_batch, @spid, @BlockedTotal, @LogiName, @DBName, @HostName, @Program_Name, @CPU, @Physical_IO, @Memusage, @Open_tran, @EventInfo)
drop table #temp



GO



USE [msdb]
GO

/****** Object:  Job [_Monitor Blocks]    Script Date: 09/20/2016 13:29:34 ******/
BEGIN TRANSACTION

DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 09/20/2016 13:29:34 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'_Monitor Blocks', 
       @enabled=1, 
       @notify_level_eventlog=2, 
       @notify_level_email=0, 
       @notify_level_netsend=0, 
       @notify_level_page=0, 
       @delete_level=0, 
       @description=N'No description available.', 
       @category_name=N'[Uncategorized (Local)]', 
       @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Look For Blocking]    Script Date: 09/20/2016 13:29:34 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Look For Blocking', 
       @step_id=1, 
       @cmdexec_success_code=0, 
       @on_success_action=1, 
       @on_success_step_id=0, 
       @on_fail_action=2, 
       @on_fail_step_id=0, 
       @retry_attempts=0, 
       @retry_interval=1, 
       @os_run_priority=0, @subsystem=N'TSQL', 
       @command=N'exec sp__Maint_BlockWatch', 
       @database_name=N'master', 
       @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run Once a Minute', 
       @enabled=1, 
       @freq_type=4, 
       @freq_interval=1, 
       @freq_subday_type=4, 
       @freq_subday_interval=1, 
       @freq_relative_interval=0, 
       @freq_recurrence_factor=0, 
       @active_start_date=20060918, 
       @active_end_date=99991231, 
       @active_start_time=0, 
       @active_end_time=235959
       --, @schedule_uid=N'bf518f11-c5ba-438a-8afd-e4e33e8dad1e'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
   IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

引用自：https://dba.stackexchange.com/questions/143160

相關問答

Sql-Server

使用 OLEDB 或 ODBC 連接到 SQL Server？

  • January 17, 2022
檢視問答
Sql-Server

將列添加到生產表

  • January 14, 2020
檢視問答
Sql-Server

SET LOCK_TIMEOUT，是基於會話還是基於語句？

  • July 26, 2018
檢視問答
Sql-Server

“簽出”記錄以進行處理的策略

  • March 15, 2018
檢視問答
Sql-Server

在回滾期間，鎖定升級是否反轉

  • May 6, 2017
檢視問答
Sql-Server

訪問 - SQL Server：無法生成 SSPI 上下文

  • January 26, 2017
檢視問答