文章详细解释了SQLServer中SETANSI_NULLS和SETQUOTED_IDENTIFIER的作用，以及如何检查和处理死锁，包括使用sp_who_lock和p_killspid存储过程进行查看和解决死锁问题。同时介绍了临时表的使用和测试死锁的方法。
文章目录
sqlserver代码格式化网站
https://www.dpriver.com/pp/sqlformat.htm
存储过程中的SET ANSI_NULLS ON有什么用？
1）SET ANSI_NULLS ON：null与null不相等
2）SET ANSI_NULLS OFF：null与null相等
最后，如果开启了SET ANSI_NULLS ON，想要判断是不是为null，该怎么判断呢？这个时候可以使用is null，也可以使用isnull()函数判断null以及空白。
存储过程中SET QUOTED_IDENTIFIER ON有什么用？
1）SET QUOTED_IDENTIFIER ON：与系统关键字重复的，允许使用双引号括起来，就能正常使用。注意是双引号
2）SET QUOTED_IDENTIFIER OFF：与系统关键字重复的，不允许使用
if @@error <> 0是什么意思
@@error表示上一条SQL语句执行结果是否有错，如果@@error的值不等于0，即是出错的情况就要采取措施，比如回滚。
临时表
这是创建临时表的语法。以下为SQL使用帮助中关于临时表的说明：
可以创建本地和全局临时表。本地临时表仅在当前会话中可见；全局临时表在所有会话中都可见。
本地临时表的名称前面有一个编号符 (#table_name)，而全局临时表的名称前面有两个编号符 (##table_name)。
除非使用 DROP TABLE 语句显式除去临时表，否则临时表将在退出其作用域时由系统自动除去：
当存储过程完成时，将自动除去在存储过程中创建的本地临时表。由创建表的存储过程执行的所有嵌套存储过程都可以引用此表。但调用创建此表的存储过程的进程无法引用此表。
所有其它本地临时表在当前会话结束时自动除去。
全局临时表在创建此表的会话结束且其它任务停止对其引用时自动除去。任务与表之间的关联只在单个 Transact-SQL 语句的生存周期内保持。换言之，当创建全局临时表的会话结束时，最后一条引用此表的 Transact-SQL 语句完成后，将自动除去此表。
在指定数据库xxx中创建查看死锁的存储过程
这个是一个在用的sp
USE [xxx]
go
/****** Object: StoredProcedure [dbo].[sp_who_lock] Script Date: 2022/7/8 9:35:49 ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
ALTER PROCEDURE [dbo].[Sp_who_lock]
AS
BEGIN
DECLARE @spid INT
DECLARE @blk INT
DECLARE @count INT
DECLARE @index INT
DECLARE @lock TINYINT
SET @lock=0
CREATE TABLE #temp_who_lock
(
--自增字段
id INT IDENTITY(1, 1),
spid INT,
blk INT
)
--@@error表示上一条SQL语句执行结果是否有错，如果@@error的值不等于0，即是出错的情况就要采取措施，比如回滚。
IF @@error <> 0
RETURN @@error
INSERT INTO #temp_who_lock
(spid,
blk)
SELECT 0,
blocked
FROM (SELECT *
FROM master..sysprocesses
WHERE blocked > 0)a
WHERE NOT EXISTS(SELECT *
FROM master..sysprocesses
WHERE a.blocked = spid
AND blocked > 0)
UNION
SELECT spid,
blocked
FROM master..sysprocesses
WHERE blocked > 0
IF @@error <> 0
RETURN @@error
SELECT @count = Count(*),
@index = 1
FROM #temp_who_lock
IF @@error <> 0
RETURN @@error
IF @count = 0
BEGIN
SELECT '没有阻塞和死锁信息'
RETURN 0
END
WHILE @index <= @count
BEGIN
IF EXISTS(SELECT 1
FROM #temp_who_lock a
WHERE id > @index
AND EXISTS(SELECT 1
FROM #temp_who_lock
WHERE id <= @index
AND a.blk = spid))
BEGIN
SET @lock=1
SELECT @spid = spid,
@blk = blk
FROM #temp_who_lock
WHERE id = @index
SELECT '引起数据库死锁的是: '
+ Cast(@spid AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
SELECT @spid,
@blk
DBCC inputbuffer(@spid)
DBCC inputbuffer(@blk)
END
SET @index=@index + 1
END
IF @lock = 0
BEGIN
SET @index=1
WHILE @index <= @count
BEGIN
SELECT @spid = spid,
@blk = blk
FROM #temp_who_lock
WHERE id = @index
IF @spid = 0
SELECT '引起阻塞的是:'
+ Cast(@blk AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
ELSE
SELECT '进程号SPID：'
+ Cast(@spid AS VARCHAR(10)) + '被'
+ '进程号SPID：'
+ Cast(@blk AS VARCHAR(10))
+ '阻塞,其当前进程执行的SQL语法如下'
DBCC inputbuffer(@spid)
DBCC inputbuffer(@blk)
SET @index=@index + 1
END
END
DROP TABLE #temp_who_lock
RETURN 0
END
以下为另外一种方法：
在master中创建查看死锁的存储过程
IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = Object_id(N'[dbo].[sp_who_lock]')
AND Objectproperty(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_who_lock]
go
USE master
go
CREATE PROCEDURE Sp_who_lock
AS
BEGIN
DECLARE @spid INT,
@bl INT,
@intTransactionCountOnEntry INT,
@intRowcount INT,
@intCountProperties INT,
@intCounter INT
CREATE TABLE #tmp_lock_who
(
id INT IDENTITY(1, 1),
spid SMALLINT,
bl SMALLINT
)
IF @@ERROR <> 0
RETURN @@ERROR
INSERT INTO #tmp_lock_who
(spid,
bl)
SELECT 0,
blocked
FROM (SELECT *
FROM sysprocesses
WHERE blocked > 0) a
WHERE NOT EXISTS(SELECT *
FROM (SELECT *
FROM sysprocesses
WHERE blocked > 0) b
WHERE a.blocked = spid)
UNION
SELECT spid,
blocked
FROM sysprocesses
WHERE blocked > 0
IF @@ERROR <> 0
RETURN @@ERROR
-- 找到临时表的记录数
SELECT @intCountProperties = Count(*),
@intCounter = 1
FROM #tmp_lock_who
IF @@ERROR <> 0
RETURN @@ERROR
IF @intCountProperties = 0
SELECT '现在没有阻塞和死锁信息' AS message
-- 循环开始
WHILE @intCounter <= @intCountProperties
BEGIN
-- 取第一条记录
SELECT @spid = spid,
@bl = bl
FROM #tmp_lock_who
WHERE id = @intCounter
BEGIN
IF @spid = 0
SELECT '引起数据库死锁的是: '
+ Cast(@bl AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
ELSE
SELECT '进程号SPID：'
+ Cast(@spid AS VARCHAR(10)) + '被'
+ '进程号SPID：'
+ Cast(@bl AS VARCHAR (10))
+ '阻塞,其当前进程执行的SQL语法如下'
DBCC inputbuffer (@bl )
END
-- 循环指针下移
SET @intCounter = @intCounter + 1
END
DROP TABLE #tmp_lock_who
RETURN 0
END
在master中创建删除指定数据库的死锁的存储过程
USE master
go
IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = Object_id(N'[dbo].[p_killspid]')
AND Objectproperty(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[p_killspid]
go
CREATE PROC P_killspid @dbname VARCHAR(200) --要关闭进程的数据库名
AS
DECLARE @sql NVARCHAR(500)
DECLARE @spid NVARCHAR(20)
DECLARE #tb CURSOR FOR
SELECT spid=Cast(spid AS VARCHAR(20))
FROM master..sysprocesses
WHERE dbid = Db_id(@dbname)
OPEN #tb
FETCH next FROM #tb INTO @spid
WHILE @@fetch_status = 0
BEGIN
EXEC('kill '+@spid)
FETCH next FROM #tb INTO @spid
END
CLOSE #tb
DEALLOCATE #tb
go
查看死锁
exec master..sp_who_lock
删除死锁
exec master..p_killspid 'xxx'
xxx 产生死锁的数据库名
测试死锁
创建测试数据
CREATE TABLE Lock1(C1 int default(0));
CREATE TABLE Lock2(C1 int default(0));
INSERT INTO Lock1 VALUES(1);
INSERT INTO Lock2 VALUES(1);
开两个查询窗口，分别执行下面两段sql
--Query 1
Begin Tran
Update Lock1 Set C1=C1+1;
WaitFor Delay '00:01:00';
SELECT * FROM Lock2
Rollback Tran;
--Query 2
Begin Tran
Update Lock2 Set C1=C1+1;
WaitFor Delay '00:01:00';
SELECT * FROM Lock1
Rollback Tran;
扫一扫
178
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
