SQLSERVER2008R2正确使用索引 - 李华丽 - 博客园

2020-06-18mikel阅读(816)

来源： SQLSERVER2008R2正确使用索引 – 李华丽 – 博客园

T1表 10000000万条数据,（插入时间36分钟，count(*)查询19秒，空间占用670M左右）

1.真正充分的利用索引
比如like ‘张%’ 就是符合SARG(符合扫描参数)标准
而like ‘%张’ 就不符合该标准

通配符%在字符串首字符的使用会导致索引无法使用，虽然实际应用中很难避免这样用，但还是应该对这种现象有所了解，至少知道此种用法性能是很低下的。

**********************************************

2.“非”操作符不满足SARG形式，使得索引无法使用
不满足SARG形式的语句最典型的情况就是包括非操作符的语句，如：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等。
如果使用not 或者 <>,最好转换成别的方法，比如例子如下：

T1表 10000000万条数据,构建如下:（插入时间36分钟，count(*)查询19秒，空间占用670M左右）

DECLARE @i INT
SET @i = 1
WHILE @i<1000000
BEGIN
INSERT INTO t1 VALUES (‘zhang’+CONVERT(char(50), @i),’3.2’,77);
SET @i + 1;
END

三种查询方式：

SELECT * FROM t1 WHERE id <>300000
SELECT * FROM t1 WHERE id NOT IN (300000)
SELECT * FROM t1 WHERE id >299999 AND id < 300001

在执行计划中可以明显看出，使用最后一种方式而不是前面两种方式进行查询。
网上是这么说的，但自己做的试验100W条数据，开销计划是一样的。

*********************************************

3. 函数运算不满足SARG形式，使得索引无法使用
例：下列SQL条件语句中的列都建有恰当的索引，但执行速度却非常慢：

select * from record where substring(card_no,1,4)=′5378′(13秒)
select * from record where amount/30< 1000（11秒）
select * from record where convert(char(10),date,112)=′19991201′（10秒）

分析：

where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行全表扫描，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索，因此将SQL重写成下面这样：

select * from record where card_no like ′5378%′（< 1秒）
select * from record where amount < 1000*30（< 1秒）
select * from record where date= ′1999/12/01′ （< 1秒）

你会发现SQL明显快很多

待测试…….

**********************************************

4.尽量不要对建立了索引的字段，作任何的直接处理

select * from employs where first_name + last_name =’beill cliton’;

无法使用索引，改为：

select * from employee where
first_name = substr(‘beill cliton’,1,instr(‘beill cliton’,’ ‘)-1)
and
last_name = substr(‘beill cliton’,instr(‘beill cliton’,’ ‘)+1)

则可以使用索引

***********************************************

5.不同类型的索引效能是不一样的，应尽可能先使用效能高的
比如：数字类型的索引查找效率高于字符串类型，定长字符串char，nchar的索引效率高于变长字符串varchar,nvarchar的索引。
应该将
where username=’张三’ and age>20
改进为
where age>20 and username=’张三’
注意：此处，SQL的查询分析优化功能可以做到自动重排条件顺序，但还是建议预先手工排列好。

**************************************************

6.某些情况下IN 的作用与OR 相当，且都不能充分利用索引
例：表stuff有200000行，id_no上有非群集索引，请看下面这个SQL：
select count(*) from stuff where id_no in(′0′,′1′) （23秒）
我们期望它会根据每个or子句分别查找，再将结果相加，这样可以利用id_no上的索引；但实际上,它却采用了”OR策略”，即先取出满足每个or子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉重复行，最后从这个临时表中计算结果。因此，实际过程没有利用id_no 上索引，并且完成时间还要受tempdb数据库性能的影响。
实践证明，表的行数越多，工作表的性能就越差，当stuff有620000行时，执行时间会非常长！如果确定不同的条件不会产生大量重复值，还不如将or子句分开：

select count(*) from stuff where id_no=′0′
select count(*) from stuff where id_no=′1′

得到两个结果，再用union作一次加法合算。因为每句都使用了索引，执行时间会比较短，

select count(*) from stuff where id_no=′0′
union
select count(*) from stuff where id_no=′1′

从实践效果来看，使用union在通常情况下比用or的效率要高的多，而exist关键字和in关键字在用法上类似，性能上也类似，都会产生全表扫描，效率比较低下，根据未经验证的说法，exist可能比in要快些。

***************************************************

7.使用变通的方法提高查询效率

like关键字支持通配符匹配，但这种匹配特别耗时。例如：select * from customer where zipcode like “21_ _ _”，即使在zipcode字段上已建立了索引，在这种情况下也可能还是采用全表扫描方式。如果把语句改为：select * from customer where zipcode >“21000”，在执行查询时就会利用索引，大大提高速度。但这种变通是有限制的，不应引起业务意义上的损失，对于邮政编码而言，zipcode like “21_ _ _” 和 zipcode >“21000” 意义是完全一致的。

*********************************************************人各有志,但富贵在天，人生允许彷徨,但不允许蹉跎.

8.order by按聚集索引列排序效率最高
排序是较耗时的操作，应尽量简化或避免对大型表进行排序，如缩小排序的列的范围，只在有索引的列上排序等等。
我们来看：（gid是主键，fariqi是聚合索引列）
select top 10000 gid,fariqi,reader,title from tgongwen
用时：196 毫秒。扫描计数 1，逻辑读 289 次，物理读 1 次，预读 1527 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc
用时：4720毫秒。扫描计数 1，逻辑读 41956 次，物理读 0 次，预读 1287 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
用时：4736毫秒。扫描计数 1，逻辑读 55350 次，物理读 10 次，预读 775 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc
用时：173毫秒。扫描计数 1，逻辑读 290 次，物理读 0 次，预读 0 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc
用时：156毫秒。扫描计数 1，逻辑读 289 次，物理读 0 次，预读 0 次。

同时，按照某个字段进行排序的时候，无论是正序还是倒序，速度是基本相当的。

********************************************************

9.关于节省数据查询系统开销方面的措施
(1)使用TOP尽量减少取出的数据量
(2)字段提取要按照“需多少、提多少”的原则，避免“select *”
字段大小越大，数目越多，select所耗费的资源就越多，比如取int类型的字段就会比取char的快很多。我们每少提取一个字段，数据的提取速度就会有相应的提升。提升的幅度根据舍弃的字段的大小来判断
(3)count(*) 与 count(字段) 方法比较
用 count(*)和用 count(主键)的速度是相当的，而count(*)却比其他任何除主键以外的字段汇总速度要快，而且字段越长，汇总速度就越慢。如果用 count(*)， SQL SERVER会自动查找最小字段来汇总。当然，如果您直接写count(主键)将会来的更直接些
(4)有嵌套查询时，尽可能在内层过滤掉数据
如果一个列同时在主查询和where子句中出现，很可能当主查询中的列值改变之后，子查询必须重新查询一次。而且查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行
(5)多表关联查询时，需注意表顺序，并尽可能早的过滤掉数据
在使用Join进行多表关联查询时候，应该使用系统开销最小的方案。连接条件要充份考虑带有索引的表、行数多的表，并注意优化表顺序；说的简单一点，就是尽可能早的将之后要做关联的数据量降下来。

一般情况下，SQLServer 会对表的连接作出自动优化。例如：
select name,no from A
join B on A. id=B.id
join C on C.id=A.id
where name=’wang’
尽管A表在From中先列出，然后才是B,最后才是C。但sql server可能会首先使用c表。它的选择原则是相对于该查询限制为单行或少数几行，就可以减少在其他表中查找的总数据量。绝大多数情况下，sql server 会作出最优的选择，但如果你发觉某个复杂的联结查询速度比预计的要慢，就可以使用SET FORCEPLAN语句强制sql server按照表出现顺序使用表。如上例加上：SET FORCEPLAN ON…….SET FORCEPLAN OFF 表的执行顺序将会按照你所写的顺序执行。在查询分析器中查看2种执行效率，从而选择表的连接顺序。SET FORCEPLAN的缺点是只能在存储过程中使用

SQL Server死锁问题：事务(进程 ID x)与另一个进程被死锁在锁 | 通信缓冲区资源上并且已被选作死锁牺牲品。请重新运行该事务。 - A汉克先生 - 博客园

2020-06-18mikel阅读(4347)

来源： SQL Server死锁问题：事务(进程 ID x)与另一个进程被死锁在锁 | 通信缓冲区资源上并且已被选作死锁牺牲品。请重新运行该事务。 – A汉克先生 – 博客园

### The error occurred while setting parameters
### SQL: update ERP_SCjh_zzc_pl set IF_TONGBU=1 where (IF_TONGBU=0 or IF_TONGBU is null) and djno=? and djlb=?
### Cause: com.microsoft.SQLServer.jdbc.SQLServerException: 事务(进程 ID 191)与另一个进程被死锁在锁 | 通信缓冲区资源上，并且已被选作死锁牺牲品。请重新运行该事务。
; SQL []; 事务(进程 ID 191)与另一个进程被死锁在锁 | 通信缓冲区资源上，并且已被选作死锁牺牲品。请重新运行该事务。; nested exception is com.microsoft.SQLServer.jdbc.SQLServerException: 事务(进程 ID 191)与另一个进程被死锁在锁 | 通信缓冲区资源上，并且已被选作死锁牺牲品。请重新运行该事务。
[2019-08-24 17:26:52,077] [qtp1891502635-26] [ERROR] [ExceptionController.java:26] ==>Exception Msg:

感觉这篇文章写的不错，读了三遍不是太明白，记录下来：
https://www.cnblogs.com/happyhippy/archive/2008/11/14/1333922.html

解决办法：

1. SQLServer自动选择一条SQL作死锁的牺牲品；
2. 按同一顺序访问对象；
3. SELECT语句加with (onlock)提示；
4. 使用较低的隔离级别
5. 在SQL前加SET LOCK_TIMEOUT timeout_period，当请求锁超过设定的timeout_period时间后，就会终止当前SQL的执行
6. 使用基于行版本控制的隔离级别
7. 使用绑定连接

锁相关：https://www.cnblogs.com/wynn0123/p/4633776.html

SQL Server死锁总结 - Silent Void - 博客园

2020-06-18mikel阅读(687)

来源： SQL Server死锁总结 – Silent Void – 博客园

死锁原理

根据操作系统中的定义：死锁是指在一组进程中的各个进程均占有不会释放的资源，但因互相申请被其他进程所站用不会释放的资源而处于的一种永久等待状态。

死锁的四个必要条件：
互斥条件(Mutual exclusion)：资源不能被共享，只能由一个进程使用。
请求与保持条件(Hold and wait)：已经得到资源的进程可以再次申请新的资源。
非剥夺条件(No pre-emption)：已经分配的资源不能从相应的进程中被强制地剥夺。
循环等待条件(Circular wait)：系统中若干进程组成环路，该环路中每个进程都在等待相邻进程正占用的资源。

对应到SQL Server中，当在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁；这些资源可能是：单行(RID，堆中的单行)、索引中的键(KEY，行锁)、页(PAG，8KB)、区结构(EXT，连续的8页)、堆或B树(HOBT) 、表(TAB，包括数据和索引)、文件(File，数据库文件)、应用程序专用资源(APP)、元数据(METADATA)、分配单元(Allocation_Unit)、整个数据库(DB)。一个死锁示例如下图所示：

说明：T1、T2表示两个任务；R1和R2表示两个资源；由资源指向任务的箭头(如R1->T1，R2->T2)表示该资源被改任务所持有；由任务指向资源的箭头(如T1->S2，T2->S1)表示该任务正在请求对应目标资源；
其满足上面死锁的四个必要条件：
(1).互斥：资源S1和S2不能被共享，同一时间只能由一个任务使用；
(2).请求与保持条件：T1持有S1的同时，请求S2；T2持有S2的同时请求S1；
(3).非剥夺条件：T1无法从T2上剥夺S2，T2也无法从T1上剥夺S1；
(4).循环等待条件：上图中的箭头构成环路，存在循环等待。

死锁排查

(1). 使用SQL Server的系统存储过程sp_who和sp_lock，可以查看当前数据库中的锁情况；进而根据objectID(@objID)(SQL Server 2005)/ object_name(@objID)(Sql Server 2000)可以查看哪个资源被锁，用dbcc ld(@blk)，可以查看最后一条发生给SQL Server的Sql语句；

CREATE Table #Who(spid int,
ecid int,
status nvarchar(50),
loginname nvarchar(50),
hostname nvarchar(50),
blk int,
dbname nvarchar(50),
cmd nvarchar(50),
request_ID int);

CREATE Table #Lock(spid int,
dpid int,
objid int,
indld int,
[Type] nvarchar(20),
Resource nvarchar(50),
Mode nvarchar(10),
Status nvarchar(10)
);

INSERT INTO #Who
EXEC sp_who active –看哪个引起的阻塞，blk
INSERT INTO #Lock
EXEC sp_lock –看锁住了那个资源id，objid

DECLARE @DBName nvarchar(20);
SET @DBName=’NameOfDataBase’

SELECT #Who.* FROM #Who WHERE dbname=@DBName
SELECT #Lock.* FROM #Lock
JOIN #Who
ON #Who.spid=#Lock.spid
AND dbname=@DBName;

–最后发送到SQL Server的语句
DECLARE crsr Cursor FOR
SELECT blk FROM #Who WHERE dbname=@DBName AND blk<>0;
DECLARE @blk int;
open crsr;
FETCH NEXT FROM crsr INTO @blk;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
dbcc inputbuffer(@blk);
FETCH NEXT FROM crsr INTO @blk;
END;
close crsr;
DEALLOCATE crsr;

–锁定的资源
SELECT #Who.spid,hostname,objid,[type],mode,object_name(objid) as objName FROM #Lock
JOIN #Who
ON #Who.spid=#Lock.spid
AND dbname=@DBName
WHERE objid<>0;

DROP Table #Who;
DROP Table #Lock;

(2). 使用 SQL Server Profiler 分析死锁: 将 Deadlock graph 事件类添加到跟踪。此事件类使用死锁涉及到的进程和对象的 XML 数据填充跟踪中的 TextData 数据列。SQL Server 事件探查器可以将 XML 文档提取到死锁 XML (.xdl) 文件中，以后可在 SQL Server Management Studio 中查看该文件。

避免死锁

上面1中列出了死锁的四个必要条件，我们只要想办法破其中的任意一个或多个条件，就可以避免死锁发生，一般有以下几种方法(FROM Sql Server 2005联机丛书)：
(1).按同一顺序访问对象。(注：避免出现循环)
(2).避免事务中的用户交互。(注：减少持有资源的时间，较少锁竞争)
(3).保持事务简短并处于一个批处理中。(注：同(2)，减少持有资源的时间)
(4).使用较低的隔离级别。(注：使用较低的隔离级别（例如已提交读）比使用较高的隔离级别（例如可序列化）持有共享锁的时间更短，减少锁竞争)
(5).使用基于行版本控制的隔离级别：2005中支持快照事务隔离和指定READ_COMMITTED隔离级别的事务使用行版本控制，可以将读与写操作之间发生的死锁几率降至最低：
SET ALLOW_SNAPSHOT_ISOLATION ON –事务可以指定 SNAPSHOT 事务隔离级别;
SET READ_COMMITTED_SNAPSHOT ON –指定 READ_COMMITTED 隔离级别的事务将使用行版本控制而不是锁定。默认情况下(没有开启此选项，没有加with nolock提示)，SELECT语句会对请求的资源加S锁(共享锁)；而开启了此选项后，SELECT不会对请求的资源加S锁。
注意：设置 READ_COMMITTED_SNAPSHOT 选项时，数据库中只允许存在执行 ALTER DATABASE 命令的连接。在 ALTER DATABASE 完成之前，数据库中决不能有其他打开的连接。数据库不必一定要处于单用户模式中。
(6).使用绑定连接。(注：绑定会话有利于在同一台服务器上的多个会话之间协调操作。绑定会话允许一个或多个会话共享相同的事务和锁(但每个回话保留其自己的事务隔离级别)，并可以使用同一数据，而不会有锁冲突。可以从同一个应用程序内的多个会话中创建绑定会话，也可以从包含不同会话的多个应用程序中创建绑定会话。在一个会话中开启事务(begin tran)后，调用exec sp_getbindtoken @Token out;来取得Token，然后传入另一个会话并执行EXEC sp_bindsession @Token来进行绑定(最后的示例中演示了绑定连接)。

死锁处理方法：

(1). 根据2中提供的sql，查看那个spid处于wait状态，然后用kill spid来干掉(即破坏死锁的第四个必要条件:循环等待)；当然这只是一种临时解决方案，我们总不能在遇到死锁就在用户的生产环境上排查死锁、Kill sp，我们应该考虑如何去避免死锁。

(2). 使用SET LOCK_TIMEOUT timeout_period(单位为毫秒)来设定锁请求超时。默认情况下，数据库没有超时期限(timeout_period值为-1，可以用SELECT @@LOCK_TIMEOUT来查看该值，即无限期等待)。当请求锁超过timeout_period时，将返回错误。timeout_period值为0时表示根本不等待，一遇到锁就返回消息。设置锁请求超时，破环了死锁的第二个必要条件(请求与保持条件)。

服务器: 消息 1222，级别 16，状态 50，行 1
已超过了锁请求超时时段。

(3). SQL Server内部有一个锁监视器线程执行死锁检查，锁监视器对特定线程启动死锁搜索时，会标识线程正在等待的资源；然后查找特定资源的所有者，并递归地继续执行对那些线程的死锁搜索，直到找到一个构成死锁条件的循环。检测到死锁后，数据库引擎选择运行回滚开销最小的事务的会话作为死锁牺牲品，返回1205 错误，回滚死锁牺牲品的事务并释放该事务持有的所有锁，使其他线程的事务可以请求资源并继续运行。

两个死锁示例及解决方法

5.1 SQL死锁

(1). 测试用的基础数据：

CREATE TABLE Lock1(C1 int default(0));
CREATE TABLE Lock2(C1 int default(0));
INSERT INTO Lock1 VALUES(1);
INSERT INTO Lock2 VALUES(1);

(2). 开两个查询窗口，分别执行下面两段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;

上面的SQL中有一句WaitFor Delay ’00:01:00’，用于等待1分钟，以方便查看锁的情况。

(3). 查看锁情况

在执行上面的WaitFor语句期间，执行第二节中提供的语句来查看锁信息：

Query1中，持有Lock1中第一行(表中只有一行数据)的行排他锁(RID:X)，并持有该行所在页的意向更新锁(PAG:IX)、该表的意向更新锁(TAB:IX)；Query2中，持有Lock2中第一行(表中只有一行数据)的行排他锁(RID:X)，并持有该行所在页的意向更新锁(PAG:IX)、该表的意向更新锁(TAB:IX)；

执行完Waitfor，Query1查询Lock2，请求在资源上加S锁，但该行已经被Query2加上了X锁；Query2查询Lock1，请求在资源上加S锁，但该行已经被Query1加上了X锁；于是两个查询持有资源并互不相让，构成死锁。

(4). 解决办法

a). SQL Server自动选择一条SQL作死锁牺牲品：运行完上面的两个查询后，我们会发现有一条SQL能正常执行完毕，而另一个SQL则报如下错误：

服务器: 消息 1205，级别 13，状态 50，行 1
事务（进程 ID xx）与另一个进程已被死锁在 lock 资源上，且该事务已被选作死锁牺牲品。请重新运行该事务。

这就是上面第四节中介绍的锁监视器干活了。

b). 按同一顺序访问对象：颠倒任意一条SQL中的Update与SELECT语句的顺序。例如修改第二条SQL成如下：

–Query2
Begin Tran
SELECT * FROM Lock1–在Lock1上申请S锁
WaitFor Delay ’00:01:00′;
Update Lock2 Set C1=C1+1;–Lock2:RID:X
Rollback Tran;

当然这样修改也是有代价的，这会导致第一条SQL执行完毕之前，第二条SQL一直处于阻塞状态。单独执行Query1或Query2需要约1分钟，但如果开始执行Query1时，马上同时执行Query2，则Query2需要2分钟才能执行完；这种按顺序请求资源从一定程度上降低了并发性。

c). SELECT语句加With(NoLock)提示：默认情况下SELECT语句会对查询到的资源加S锁(共享锁)，S锁与X锁(排他锁)不兼容；但加上With(NoLock)后，SELECT不对查询到的资源加锁(或者加Sch-S锁，Sch-S锁可以与任何锁兼容)；从而可以是这两条SQL可以并发地访问同一资源。当然，此方法适合解决读与写并发死锁的情况，但加With(NoLock)可能会导致脏读。

SELECT * FROM Lock2 WITH(NOLock)
SELECT * FROM Lock1 WITH(NOLock)

d). 使用较低的隔离级别。SQL Server 2000支持四种事务处理隔离级别(TIL)，分别为：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE；SQL Server 2005中增加了SNAPSHOT TIL。默认情况下，SQL Server使用READ COMMITTED TIL，我们可以在上面的两条SQL前都加上一句SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED，来降低TIL以避免死锁；事实上，运行在READ UNCOMMITTED TIL的事务，其中的SELECT语句不对结果资源加锁或加Sch-S锁，而不会加S锁；但还有一点需要注意的是：READ UNCOMMITTED TIL允许脏读，虽然加上了降低TIL的语句后，上面两条SQL在执行过程中不会报错，但执行结果是一个返回1，一个返回2，即读到了脏数据，也许这并不是我们所期望的。

e). 在SQL前加SET LOCK_TIMEOUT timeout_period，当请求锁超过设定的timeout_period时间后，就会终止当前SQL的执行，牺牲自己，成全别人。

f). 使用基于行版本控制的隔离级别(SQL Server 2005支持)：开启下面的选项后，SELECT不会对请求的资源加S锁，不加锁或者加Sch-S锁，从而将读与写操作之间发生的死锁几率降至最低；而且不会发生脏读。啊

SET ALLOW_SNAPSHOT_ISOLATION ON
SET READ_COMMITTED_SNAPSHOT ON

g). 使用绑定连接(使用方法见下一个示例。)

5.2 程序死锁(SQL阻塞)

看一个例子：一个典型的数据库操作事务死锁分析，按照我自己的理解，我觉得这应该算是C#程序中出现死锁，而不是数据库中的死锁；下面的代码模拟了该文中对数据库的操作过程：

//略去的无关的code
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
string sql1 = “Update Lock1 SET C1=C1+1”;
string sql2 = “SELECT * FROM Lock1”;
ExecuteNonQuery(tran, sql1); //使用事务:事务中Lock了Table
ExecuteNonQuery(null, sql2); //新开一个connection来读取Table

public static void ExecuteNonQuery(SqlTransaction tran, string sql)
{
SqlCommand cmd = new SqlCommand(sql);
if (tran != null)
{
cmd.Connection = tran.Connection;
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
}
else
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
}
}

执行到ExecuteNonQuery(null, sql2)时抛出SQL执行超时的异常，下图从数据库的角度来看该问题：

代码从上往下执行，会话1持有了表Lock1的X锁，且事务没有结束，回话1就一直持有X锁不释放；而会话2执行select操作，请求在表Lock1上加S锁，但S锁与X锁是不兼容的，所以回话2的被阻塞等待，不在等待中，就在等待中获得资源，就在等待中超时。。。从中我们可以看到，里面并没有出现死锁，而只是SELECT操作被阻塞了。也正因为不是数据库死锁，所以SQL Server的锁监视器无法检测到死锁。

我们再从C#程序的角度来看该问题：

C#程序持有了表Lock1上的X锁，同时开了另一个SqlConnection还想在该表上请求一把S锁，图中已经构成了环路；太贪心了，结果自己把自己给锁死了。。。

虽然这不是一个数据库死锁，但却是因为数据库资源而导致的死锁，上例中提到的解决死锁的方法在这里也基本适用，主要是避免读操作被阻塞，解决方法如下：

a). 把SELECT放在Update语句前：SELECT不在事务中，且执行完毕会释放S锁；
b). 把SELECT也放加入到事务中：ExecuteNonQuery(tran, sql2);
c). SELECT加With(NOLock)提示：可能产生脏读；
d). 降低事务隔离级别：SELECT语句前加SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED；同上，可能产生脏读；
e). 使用基于行版本控制的隔离级别（同上例）。
g). 使用绑定连接：取得事务所在会话的token，然后传入新开的connection中；执行EXEC sp_bindsession @Token后绑定了连接，最后执行exec sp_bindsession null;来取消绑定；最后需要注意的四点是：
(1). 使用了绑定连接的多个connection共享同一个事务和相同的锁，但各自保留自己的事务隔离级别；
(2). 如果在sql3字符串的“exec sp_bindsession null”换成“commit tran”或者“rollback tran”，则会提交整个事务，最后一行C#代码tran.Commit()就可以不用执行了(执行会报错，因为事务已经结束了-,-)。
(3). 开启事务(begin tran)后，才可以调用exec sp_getbindtoken @Token out来取得Token；如果不想再新开的connection中结束掉原有的事务，则在这个connection close之前，必须执行“exec sp_bindsession null”来取消绑定连接，或者在新开的connectoin close之前先结束掉事务(commit/tran)。
(4). (Sql server 2005 联机丛书)后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。请改用多个活动结果集 (MARS) 或分布式事务。

tran = connection.BeginTransaction();
string sql1 = “Update Lock1 SET C1=C1+1″;
ExecuteNonQuery(tran, sql1); //使用事务:事务中Lock了测试表Lock1
string sql2 = @”DECLARE @Token varchar(255);
exec sp_getbindtoken @Token out;
SELECT @Token;”;
string token = ExecuteScalar(tran, sql2).ToString();
string sql3 = “EXEC sp_bindsession @Token;Update Lock1 SET C1=C1+1;exec sp_bindsession null;”;
SqlParameter parameter = new SqlParameter(“@Token”, SqlDbType.VarChar);
parameter.Value = token;
ExecuteNonQuery(null, sql3, parameter); //新开一个connection来操作测试表Lock1
tran.Commit();

附：锁兼容性(FROM SQL Server 2005 联机丛书)

锁兼容性控制多个事务能否同时获取同一资源上的锁。如果资源已被另一事务锁定，则仅当请求锁的模式与现有锁的模式相兼容时，才会授予新的锁请求。如果请求锁的模式与现有锁的模式不兼容，则请求新锁的事务将等待释放现有锁或等待锁超时间隔过期。

ASP.NET MVC＋Redis （准备工作） - BBSMAX

2020-06-17mikel阅读(900)

来源： ASP.NET MVC＋Redis （准备工作） – BBSMAX

GitHub仓库创建

在GitHub上创建一个自己的仓库，选择好开发工具，添加.gitignore和readme文件。

gitignore文件和readme文件是很容易理解的。

有了gitignore文件之后，在提交的时候有很好的拦截作用，确保仓库的简洁。
readme是md文件，即支持markdown语言，对项目说明有很好的展示效果。

项目创建完成之后，将项目克隆至本地路径中。

ASP.NETMvc+Redis项目创建

1. 起手式目前阶段项目还不需要宇宙第一IDE VisualStudio（其实我是嫌VS太笨重了），所以VisualStudio Code在克隆好的路径中打开。
  
  说到VS Code我还是挺感谢它的，之所以放弃使用已久的notepad ++是因为对微软的东西似乎有一种奇妙的感情。接触了VS Code后让我对所有的命令行工具有了很浓厚的兴趣，准备下一个阶段就学习一下Linux。
2. 创建一个ASP.NETCore Mvc项目使用VS Code创建项目DotNet CLI是唯一选择。打开终端视图使用命令即可完成创建
  1. dotnet new mvc
  需要说明的一点是，创建完项目后可在项目文件中看到项目已经引用了Microsoft.AspNetCore.All包。这个包包含了AspNetCore开发的大部分程序集，不需要额外引用，所有程序集版本也是跟随Microsoft.AspNetCore.All的。

创建Redis工具通过浏览Redis官网发现支持C#的客户端有很多的，大部分都已经支持DotNet Core了。我的选择是开源，没有次数限制的StackExchange.Redis。
如果项目不打算使用Microsoft.AspNetCore.All的话需要nuget官网中找到Redis客户端的引用命令,如果命令末尾不指定版本号的话默认为最新版本。
1. dotnet add package StackExchange.Redis
- 创建一个工具类文件夹Common用处存放所有Redis操作的类和其他具有共通意义的类文件。
- 创建一个类文件，可以使用DotNet Core命令dotnet new class也可以通过VS Code的图像化创建。创建文件之后要修改文件名和命名空间。
- 引入Redis的命名空间，完成Redis工具类代码编写。
1. using System;
2. using StackExchange.Redis;
3. using System.Collections.Generic;
4. namespace Blog.Common
5. {
6. public static class RedisCommon
7. {
9. private static ConnectionMultiplexer redis;
11. public static ConnectionMultiplexer GetConnection()
12. {
13. if (redis == null)
14. {
15. //Redis的连接地址如果不是本机格式为ip:port
16. redis = ConnectionMultiplexer.Connect("localhost");
18. }
19. return redis;
20. }
22. //扩展方法
23. public static Dictionary<string, string> ToDic(this HashEntry[] hash)
24. {
25. Dictionary<string, string> dic = new Dictionary<string, string>();
26. if (hash.Length == 0)
27. return dic;
28. foreach (var item in hash)
29. {
30. dic.Add(item.Name, item.Value);
31. }
32. return dic;
33. }
34. }
35. }
目前这只是一个简单的工具类，只是对外提供了Redis的客户端对象，如果以后有什么需要附加的方法写到这里就行了。

另外在这里我利用C#语法糖给HashEntry数组写了一个扩展方法，写这个方法的初衷是在编码过程中总是有会遇到把HashEntry数组转换成Dictionary的过程，而每每到这个时候就会需要一个导入redis包的过程，而且这段代码也是冗余的。
至此ASP.NETMvc + Redis 的所有准备工作已经完成，接下来准备开发符合书中内容的blog网站。

ASP.NET MVC配置Redis服务 - BBSMAX

2020-06-16mikel阅读(897)

来源： ASP.NET MVC配置Redis服务 – BBSMAX

1、下载并安装Redis。官网并未提供Windows安装版，所以去Github 下载

下载地址： https://github.com/MicrosoftArchive/redis/releases

2、双击运行msi安装文件，在中会有几个配置的地方，除了安装位置可以改变外，其它的建议就按照默认的安装，不要更改

3安装完成后，可以看到你刚选择文件夹里面已经有了很多东西。可以看到.zip解压后的文件和msi安装的文件是一模一样，所以直接解压zip也是可以的。在安装的文件夹下，按住shift键+鼠标右键，选择在此处打开命令窗口（也可以选择设置环境变量或者打开命令窗口，然后再进入当前目录）

4、如果在命令行窗口输入redis-server.exe redis.windows.conf指令执行报错误[13164] 27 Dec 20:57:07.820 # Creating Server TCP listening socket 127.0.0.1:637 9: bind: No error。那么可以输入如下的命令依次执行第一条指令：redis-cli.exe，第二条指令：shutdown第三条指令：exit

5、在命令行窗口输入redis-server.exe redis.windows.conf，就会显示Redis服务器的信息，就代表Redis服务器已经启动了。

6、如果要练习客户端命令等，需要另外启动一个命令窗口，同样在同一个目录里，输入命令Redis-cli.exe就可以连接上了（如果没有改默认配置的端口号），如果改了，就输入更加详细的命令：redis-cli.exe -h 127.0.0.1 -p 6379就可以，根据这种格式对应改自己的修改的配置即可

7、设置Redis服务

①由于上面虽然启动了redis，但是只要一关闭cmd窗口，redis就会消失。所以要把redis设置成windows下的服务。

也就是设置到这里，首先发现是没用这个Redis服务的。

②设置服务的命令如下：

redis–server —service–install redis.windows–service.conf —loglevel verbose

搞定，收工！

如何在C#Asp.Net MVC使用Redis缓存 - BBSMAX

2020-06-16mikel阅读(738)

来源： 如何在C#Asp.Net MVC使用Redis缓存 – BBSMAX

为什么要在ASP.NET MVC项目中使用Redis缓存呢？系统是按照高负载高并发来设计的，这就涉及服务器集群带来的问题，Session存储验证码或登录信息，在系统登录的时候，可能展示登录界面和存储验证码是一台服务器，登录验证的时候又是另外一个服务器，就会造成验证码找不到、重复登录等现象，所以必须系统公用的信息数据存储在一个地方，所有的服务器都从这个地方获取，这时我们就要用到Redis了，为什么要用Redis？Redis有什么优点？请自行度娘。下面我跟小伙伴们说说如何在C#ASP.NET MVC使用Redis，希望对遇到同样问题的小伙伴少走弯路，谢谢。

首先，下载Windows版的Redis3.2.1，官网不提供Windows版本下载，解压后的文件如下图，远程连接我已经配置好了，密码是123456，运行Redis我也做了一个名为StartUp的批处理文件，就不用每次运行Redis都要进入Dos界面了。

Redis启动完成

其次，在项目中引用RedisHelper类库，工具–Nuget程序包管理–管理解决方案的Nuget程序包–联机，搜索“RedisHelper”，选中下图红框选项安装，我已经安装了右上角会有个绿色的钩，安装过程可能需要10-15分钟，安装好之后就变需要配置Redis连接信息，在web.config文件appSetting节点添加主Redis服务器<add key="RedisHostServers" value="123456@127.0.0.1:6379?db=1" />，从Redis服务器的连接字符串<add key="RedisSlaveServers" value="192.168.0.105:6379?db=1"/>，其中123456代表密码，没有密码可以把123456@去掉，127.0.0.1:6379是Redis服务的IP和端口，db=1代表那个数据库。

然后，使用RedisHelper类库，存储、获取、删除数据，存储RedisHelper.Set(Key, Value, DateTime),DateTime不提供默认是-1，不会过期，如果存储一样的Key之前的值会被覆盖；获取RedisHelper.Get<T>(Key)，T是当初存储是什么类型，如果值不存在返回null;删除RedisHelper.Remove(key);

最后，安装redis-desktop-manager查看数据，连接Redis服务器，输入IP，端口，有密码输密码，没有密码留空，测试连接是否连接成功，Redis默认创建16个数据库，如下图

文件下载

windows-redis3.2.1 redis-desktop-manager

sql server在高并发状态下同时执行查询与更新操作时的死锁问题_ajianchina的博客-CSDN博客

2020-06-16mikel阅读(679)

来源： (1条消息)sql server在高并发状态下同时执行查询与更新操作时的死锁问题_ajianchina的博客-CSDN博客

最近在项目上线使用过程中使用SQLServer的时候发现在高并发情况下，频繁更新和频繁查询引发死锁。通常我们知道如果两个事务同时对一个表进行插入或修改数据，会发生在请求对表的X锁时，已经被对方持有了。由于得不到锁，后面的Commit无法执行，这样双方开始死锁。但是select语句和update语句同时执行，怎么会发生死锁呢？看完下面的分析，你会明白的…

首先看到代码中使用的查询的方法Select

/// <summary>
/// 根据学生ID查询教师信息。用于前台学生评分主页面显示
/// </summary>
/// <param name=”enTeacherCourseStudent”>教师课程学生关系实体：StudentID</param>
public DataTable QueryTeacherByStudent(TeacherCourseStudentLinkEntity enTeacherCourseStudent)
{
//TODO:QueryTeacherByStudent string strSQL = “SELECT ID, CollegeTeacherID,CollegeTeacherName,TeacherID,TeacherCode,” +
//”TeacherName,CourseID,CourseName,CourseTypeID,CourseTypeName,” +
//”StudentID,StudentName,IsEvluation FROM TA_TeacherCourseStudentLink WITH(NOLOCK) ” +
//”WHERE StudentID = @StudentID”;
//根据学生ID查询该学生对哪些教师评分的SQL语句
string strSQL = “SELECT ID, CollegeTeacherID,CollegeTeacherName,TeacherID,TeacherCode,” +
“TeacherName,CourseID,CourseName,CourseTypeID,CourseTypeName,” +
“StudentID,StudentName,IsEvluation FROM TA_TeacherCourseStudentLink WITH(NOLOCK) “ +
“WHERE StudentID = @StudentID”;
//参数
SqlParameter[] para = new SqlParameter[] {
new SqlParameter(“@StudentID”,enTeacherCourseStudent.StudentID) //学生ID
};
//执行带参数的sql查询语句或存储过程
DataTable dtStuTeacher = sqlHelper.ExecuteQuery(strSql, para, CommandType.Text);
//返回查询结果
return dtStuTeacher;
}

更新方法

/// <summary>
/// 学生对教师评分完毕，是否评估由N变为Y
/// </summary>
/// <param name=”enTeacherCourseStudent”>教师课程学生关系实体：StudentID、TeacherID、CourseID</param>
/// <return>是否修改成功，true成功，false失败</return>
public Boolean EditIsEvaluation(TeacherCourseStudentLinkEntity enTeacherCourseStudent, SqlConnection sqlCon, SqlTransaction sqlTran)
{
//更改是否评估字段为”Y”的sql语句
string strSql = “UPDATE TA_TeacherCourseStudentLink WITH(UPDLOCK) SET IsEvluation=’Y’ WHERE TeacherID=@TeacherID AND StudentID=@StudentID AND CourseID=@CourseID”;
//参数
SqlParameter[] paras = new SqlParameter[]{
new SqlParameter(“@TeacherID”,enTeacherCourseStudent.TeacherID), //教师ID
new SqlParameter(“@StudentID”,enTeacherCourseStudent.StudentID), //学生ID
new SqlParameter(“@CourseID”,enTeacherCourseStudent.CourseID) //课程ID
};
//李社河添加2014年12月29日
Boolean flagModify = false;
try
{
//执行带参数的增删改sql语句或存储过程
flagModify = sqlHelper.ExecNoSelect(strSql, paras, CommandType.Text, sqlCon, sqlTran);
}
catch (Exception e)
{
throw e;
}
//返回修改结果
return flagModify;
}

现在分析，在数据库系统中，死锁是指多个用户（进程）分别锁定了一个资源，并又试图请求锁定对方已经锁定的资源，这就产生了一个锁定请求环，导致多个用户（进程）都处于等待对方释放所锁定资源的状态。还有一种比较典型的死锁情况是当在一个数据库中时，有若干个长时间运行的事务执行并行的操作，当查询分析器处理一种非常复杂的查询例如连接查询时，那么由于不能控制处理的顺序，有可能发生死锁现象。

那么，什么导致了死锁？

现象图

通过查询SQLServer的事务日志视图，发生的错误日志视图知道是在高并发的情况下引发的update和select发生的死锁，接下来我们看例子;

CREATE PROC p1 @p1 int AS
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
GO
CREATE PROC p2 @p1 int AS
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1
GO

p1没有insert，没有delete，没有update，只是一个select，p2才是update。那么，什么导致了死锁？

需要从事件日志中，看sql的死锁信息：
Spid X is running this query (line 2 of proc [p1], inputbuffer “… EXEC p1 4 …”):
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
Spid Y is running this query (line 2 of proc [p2], inputbuffer “EXEC p2 4”):
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
The SELECT is waiting for a Shared KEY lock on index t1.cidx. The UPDATE holds a conflicting X lock.
The UPDATE is waiting for an eXclusive KEY lock on index t1.idx1. The SELECT holds a conflicting S lock.

首先，我们看看p1的执行计划。怎么看呢？可以执行set statistics profile on，这句就可以了。下面是p1的执行计划

SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
|–Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [t1].[c1]))
|–Index Seek(OBJECT:([t1].[idx1]), SEEK:([t1].[c2] >= [@p1] AND [t1].[c2] <= [@p1]+(1)) ORDERED FORWARD)
|–Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[t1].[c1] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)

我们看到了一个nested loops，第一行，利用索引t1.c2来进行seek，seek出来的那个rowid，在第二行中，用来通过聚集索引来查找整行的数据。这是什么？就是bookmark lookup啊！为什么？因为我们需要的c2、c3不能完全的被索引t1.c1带出来，所以需要书签查找。

好，我们接着看p2的执行计划。

UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
|–Clustered Index Update(OBJECT:([t1].[cidx]), OBJECT:([t1].[idx1]), SET:([t1].[c2] = [Expr1004]))
|–Compute Scalar(DEFINE:([Expr1013]=[Expr1013]))
|–Compute Scalar(DEFINE:([Expr1004]=[t1].[c2]+(1), [Expr1013]=CASE WHEN CASE WHEN …
|–Top(ROWCOUNT est 0)
|–Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[@p1]) ORDERED FORWARD)

通过聚集索引的seek找到了一行，然后开始更新。这里注意的是，update的时候，它会申请一个针对clustered index的X锁的。

实际上到这里，我们就明白了为什么update会对select产生死锁。update的时候，会申请一个针对clustered index的X锁，这样就阻塞住了（注意，不是死锁！）select里面最后的那个clustered index seek。死锁的另一半在哪里呢？注意我们的select语句，c2存在于索引idx1中，c1是一个聚集索引cidx。问题就在这里！我们在p2中更新了c2这个值，所以sqlserver会自动更新包含c2列的非聚集索引：idx1。而idx1在哪里？就在我们刚才的select语句中。而对这个索引列的更改，意味着索引集合的某个行或者某些行，需要重新排列，而重新排列，需要一个X锁。

问题就这样被发现了，就是说，某个query使用非聚集索引来select数据，那么它会在非聚集索引上持有一个S锁。当有一些select的列不在该索引上，它需要根据rowid找到对应的聚集索引的那行，然后找到其他数据。而此时，第二个的查询中，update正在聚集索引上忙乎：定位、加锁、修改等。但因为正在修改的某个列，是另外一个非聚集索引的某个列，所以此时，它需要同时更改那个非聚集索引的信息，这就需要在那个非聚集索引上，加第二个X锁。select开始等待update的X锁，update开始等待select的S锁，死锁，就这样发生鸟。

添加了针对select和update同一个表的非聚集索引解决问题。那么，为什么我们增加了一个非聚集索引，死锁就消失鸟？我们看一下，按照上文中自动增加的索引之后的执行计划：

SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
|–Index Seek(OBJECT:([deadlocktest].[dbo].[t1].[_dta_index_t1_7_2073058421__K2_K1_3]), SEEK:([deadlocktest].[dbo].[t1].[c2] >= [@p1] AND [deadlocktest].[dbo].[t1].[c2] <= [@p1]+(1)) ORDERED FORWARD)

哦，对于clustered index的需求没有了，因为增加的覆盖索引已经足够把所有的信息都select出来。就这么简单。
实际上，在sqlserver 2005中，如果用profiler来抓eventid：1222，那么会出现一个死锁的图，很直观的说。

下面的方法，有助于将死锁减至最少（详细情况，请看SQLServer联机帮助，搜索：将死锁减至最少即可）。

· 按同一顺序访问对象。

· 避免事务中的用户交互。

· 保持事务简短并处于一个批处理中。

· 使用较低的隔离级别。

· 使用基于行版本控制的隔离级别。

– 将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON，使得已提交读事务使用行版本控制。

– 使用快照隔离。

· 使用绑定连接。

下面我们在测试的同时开启trace profiler跟踪死锁视图(locks:deadlock graph).(当然也可以开启跟踪标记,或者应用扩展事件(xevents)等捕捉死锁)

创建测试对象code

create table testklup
( clskey int not null,
nlskey int not null,
cont1 int not null,
cont2 char(3000)
)
create unique clustered index inx_cls on testklup(clskey)
create unique nonclustered index inx_nlcs on testklup(nlskey) include(cont1)
insert into testklup select 1,1,100,‘aaa’
insert into testklup select 2,2,200,‘bbb’
insert into testklup select 3,3,300,‘ccc’

开启会话1 模拟高频update操作

—-模拟高频update操作

declare @i int
set @i=100
while 1=1
begin
update testklup set cont1=@i
where clskey=1
set @i=@i+1
end

开启会话2 模拟高频select操作

—-模拟高频select操作

declare @cont2 char(3000)
while 1=1
begin
select @cont2=cont2 from testklup where nlskey=1
end

此时开启会话2执行一小段时间时我们就可以看到类似错误信息:

而在我们开启的跟踪中捕捉到了死锁.

死锁分析:可以看出由于读进程(108)请求写进程(79)持有的X锁被阻塞的同时,写进程(79)又申请读进程(108)锁持有的S锁.读执行计划图，写执行计划图。

(由于在默认隔离级别下(读提交)读申请S锁只是瞬间过程,读完立即释放,不会等待事务完成),所以在并发,执行频率不高的情形下不易出现.但我们模拟的高频情况使得S锁获得频率非常高,此时就出现了仅仅两个会话,一个读,一个写就造成了死锁现象.

死锁原因:读操作中的键查找造成的额外锁(聚集索引)需求

解决方案:在了解了死锁产生的原因后,解决起来就比较简单了.

我们可以从以下几个方面入手.

a 消除额外的键查找锁需的锁

b 读操作时取消获取锁

a.1我们可以创建覆盖索引使select语句中的查询列包含在指定索引中

CREATE NONCLUSTERED INDEX [inx_nlskey_incont2] ON [dbo].[testklup] ([nlskey] ASC) INCLUDE ( [cont2])

a.2 根据查询需求,分步执行,通过聚集索引获取查询列,避免键查找.’

declare @cont2 char(3000) declare @clskey intwhile 1=1 begin  select @clskey=clskey from testklup where nlskey=1     select @cont2=cont2 from testklup where clskey=@clskey end

b 通过改变隔离级别,使用乐观并发模式,读操作时源行无需锁

declare @cont2 char(3000)
while 1=1
begin
select @cont2=cont2 from testklup with(nolock) where nlskey=1
end

结束语.我们在解决问题时,最好弄清问题的本质原因,通过问题点寻找出适合自己的环境的解决方案再实施.

redis实现分布式锁 |

2020-06-16mikel阅读(783)

来源： redis实现分布式锁 |

以下为测试分布式锁的实现方法。

场景：
比如50个人向B转账，B的余额当前为0，转50次，每次1元，理论来说B的余额应该为50元。
当在高并发的情况下，那么最终结果就不一定是50了。

现在有张表名为balance，3个字段id（自增），name（姓名），balance（余额）

现在用php模拟一下简单的转账操作。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

<?php header("Content-type =>  text/html; charset=utf-8");
// session start
define("SESSION_ON", true);
// define project's config
define("CONFIG", '/conf/web.php');
// Debug switch
define("Debug", true);
// include framework entrance file
include('../common.php');
use pt\framework\Debug\console as debug;
use pt\framework\template as template;
use pt\tool\page as page;
use pt\framework\db as db;
include(COMMON_PATH.'web_func.php');
$db = db::init();
// 查询B用户余额
$balance = "select balance from balance where id = 1;";
$balance = $db -> prepare($balance) -> execute();
$balance = $balance[0]['balance'];
// B余额加1
$SQL = "update balance set balance = :balance + 1 where id = 1;";
$rs = $db -> prepare($SQL) -> execute(array(':balance' => $balance));

使用jmeter并发50测试且50个请求全部成功。去查看数据库。

可以看到数据库中balance字段值为44，不是理论的50元。
原因就是在高并发情况下出现了问题。
比如有2个请求同时从数据库中获得了一样的余额，比如1，第一个请求先进行了update操作但是还没结束，
第二个请求发现第一个请求正在更新数据库，第一个修改请求会将balance表的id=1这条数据处于行锁状态（innodb引擎+索引才能实现行锁）。
这样第二个请求就处于等待状态，等待第一个请求update完成并释放行锁后，再更新id=1的数据，问题来了。
第一个请求已经将余额修改为2，第二个请求也把余额改为2了。（其实并没有真正更新，发现一样就不改了，在SQLyog中会提示(0 row(s) affected)，影响了0行）
（完整的转账应该至少3条记录要通过事务更新，from减，to加，插入资金流水表）

现在为了解决这个问题，引入redis，通过setnx这个方法实现，具体解释请百度。
当redis中存在to（我这里把to的用户id做为key）这个key时，则停止转账。

<?php header("Content-type =>  text/html; charset=utf-8");
// session start
define("SESSION_ON", true);
// define project's config
define("CONFIG", '/conf/web.php');
// debug switch
define("DEBUG", true);
// include framework entrance file
include('../common.php');
use pt\framework\debug\console as debug;
use pt\framework\template as template;
use pt\tool\page as page;
use pt\framework\db as db;
include(COMMON_PATH.'web_func.php');
$db = db::init();
$to = 1;  // 用户id做为key
try
{
    $redis = new Redis();
    $redis -> connect('127.0.0.1', 6379);
    $redis -> auth('111111');
    $connected = $redis -> ping();
}
catch(Exception $e)
{
    echo '连接失败：' . $e -> getMessage();
    exit;
}
// 获取用户余额
$balance = "select balance from balance where id = 1;";
$balance = $db -> prepare($balance) -> execute();
$balance = $balance[0]['balance'];
$expire = 60;  // key超时时间
$key = $to;
$content = 'running';
$rs = $redis -> setnx($key, $content);
if ($rs === true) // 加锁
{
    $redis -> expire($key, $expire);  // 设置key超时时间，防止某个机器加锁以后挂掉造成key死锁（一直存在）。
    $sql = "update balance set balance = :balance + 1 where id = 1;";
    $rs = $db -> prepare($sql) -> execute(array(':balance' => $balance));
    if ($rs === false)
    {
        echo 'add balance failed';
    }
    else
    {
        echo 'add balance success';
    }
    $redis -> delete($to); // 删除锁
}
else
{
    //echo 'lock false , lock by others' . PHP_EOL;
    //echo 'after ' . $redis -> ttl($key) . 's release';
    header('HTTP/1.1 403 Forbidden');  // 这里输出403错误方便jmeter中查看。实际中可能返回个友好的错误提示，比如请重试。
}

再用jmeter并发50测试。

发现有34个请求成功，16个失败，总共50个请求。
再去看数据库中balance字段。余额为34。

这样就通过redis实现了分布式锁，当有多个服务器做负载均衡时，每次转账都会先请求redis，查这个用户是否存在key，防止出现同一时间转账导致结果错误。
如有错误，请指正。

PS：
另外我觉得也可以通过消息队列实现，排队转账。

原创文章，转载请注明。本文链接地址: https://www.rootop.org/pages/4144.html

SQL Server死锁总结 - Silent Void - 博客园

2020-06-16mikel阅读(543)

来源： SQL Server死锁总结 – Silent Void – 博客园

死锁原理

根据操作系统中的定义：死锁是指在一组进程中的各个进程均占有不会释放的资源，但因互相申请被其他进程所站用不会释放的资源而处于的一种永久等待状态。

死锁的四个必要条件：
互斥条件(Mutual exclusion)：资源不能被共享，只能由一个进程使用。
请求与保持条件(Hold and wait)：已经得到资源的进程可以再次申请新的资源。
非剥夺条件(No pre-emption)：已经分配的资源不能从相应的进程中被强制地剥夺。
循环等待条件(Circular wait)：系统中若干进程组成环路，该环路中每个进程都在等待相邻进程正占用的资源。

对应到SQL Server中，当在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁；这些资源可能是：单行(RID，堆中的单行)、索引中的键(KEY，行锁)、页(PAG，8KB)、区结构(EXT，连续的8页)、堆或B树(HOBT) 、表(TAB，包括数据和索引)、文件(File，数据库文件)、应用程序专用资源(APP)、元数据(METADATA)、分配单元(Allocation_Unit)、整个数据库(DB)。一个死锁示例如下图所示：

说明：T1、T2表示两个任务；R1和R2表示两个资源；由资源指向任务的箭头(如R1->T1，R2->T2)表示该资源被改任务所持有；由任务指向资源的箭头(如T1->S2，T2->S1)表示该任务正在请求对应目标资源；
其满足上面死锁的四个必要条件：
(1).互斥：资源S1和S2不能被共享，同一时间只能由一个任务使用；
(2).请求与保持条件：T1持有S1的同时，请求S2；T2持有S2的同时请求S1；
(3).非剥夺条件：T1无法从T2上剥夺S2，T2也无法从T1上剥夺S1；
(4).循环等待条件：上图中的箭头构成环路，存在循环等待。

死锁排查

(1). 使用SQL Server的系统存储过程sp_who和sp_lock，可以查看当前数据库中的锁情况；进而根据objectID(@objID)(SQL Server 2005)/ object_name(@objID)(Sql Server 2000)可以查看哪个资源被锁，用dbcc ld(@blk)，可以查看最后一条发生给SQL Server的Sql语句；

CREATE Table #Who(spid int,
ecid int,
status nvarchar(50),
loginname nvarchar(50),
hostname nvarchar(50),
blk int,
dbname nvarchar(50),
cmd nvarchar(50),
request_ID int);

CREATE Table #Lock(spid int,
dpid int,
objid int,
indld int,
[Type] nvarchar(20),
Resource nvarchar(50),
Mode nvarchar(10),
Status nvarchar(10)
);

INSERT INTO #Who
EXEC sp_who active –看哪个引起的阻塞，blk
INSERT INTO #Lock
EXEC sp_lock –看锁住了那个资源id，objid

DECLARE @DBName nvarchar(20);
SET @DBName=’NameOfDataBase’

SELECT #Who.* FROM #Who WHERE dbname=@DBName
SELECT #Lock.* FROM #Lock
JOIN #Who
ON #Who.spid=#Lock.spid
AND dbname=@DBName;

–最后发送到SQL Server的语句
DECLARE crsr Cursor FOR
SELECT blk FROM #Who WHERE dbname=@DBName AND blk<>0;
DECLARE @blk int;
open crsr;
FETCH NEXT FROM crsr INTO @blk;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
dbcc inputbuffer(@blk);
FETCH NEXT FROM crsr INTO @blk;
END;
close crsr;
DEALLOCATE crsr;

–锁定的资源
SELECT #Who.spid,hostname,objid,[type],mode,object_name(objid) as objName FROM #Lock
JOIN #Who
ON #Who.spid=#Lock.spid
AND dbname=@DBName
WHERE objid<>0;

DROP Table #Who;
DROP Table #Lock;

(2). 使用 SQL Server Profiler 分析死锁: 将 Deadlock graph 事件类添加到跟踪。此事件类使用死锁涉及到的进程和对象的 XML 数据填充跟踪中的 TextData 数据列。SQL Server 事件探查器可以将 XML 文档提取到死锁 XML (.xdl) 文件中，以后可在 SQL Server Management Studio 中查看该文件。

避免死锁

上面1中列出了死锁的四个必要条件，我们只要想办法破其中的任意一个或多个条件，就可以避免死锁发生，一般有以下几种方法(FROM Sql Server 2005联机丛书)：
(1).按同一顺序访问对象。(注：避免出现循环)
(2).避免事务中的用户交互。(注：减少持有资源的时间，较少锁竞争)
(3).保持事务简短并处于一个批处理中。(注：同(2)，减少持有资源的时间)
(4).使用较低的隔离级别。(注：使用较低的隔离级别（例如已提交读）比使用较高的隔离级别（例如可序列化）持有共享锁的时间更短，减少锁竞争)
(5).使用基于行版本控制的隔离级别：2005中支持快照事务隔离和指定READ_COMMITTED隔离级别的事务使用行版本控制，可以将读与写操作之间发生的死锁几率降至最低：
SET ALLOW_SNAPSHOT_ISOLATION ON –事务可以指定 SNAPSHOT 事务隔离级别;
SET READ_COMMITTED_SNAPSHOT ON –指定 READ_COMMITTED 隔离级别的事务将使用行版本控制而不是锁定。默认情况下(没有开启此选项，没有加with nolock提示)，SELECT语句会对请求的资源加S锁(共享锁)；而开启了此选项后，SELECT不会对请求的资源加S锁。
注意：设置 READ_COMMITTED_SNAPSHOT 选项时，数据库中只允许存在执行 ALTER DATABASE 命令的连接。在 ALTER DATABASE 完成之前，数据库中决不能有其他打开的连接。数据库不必一定要处于单用户模式中。
(6).使用绑定连接。(注：绑定会话有利于在同一台服务器上的多个会话之间协调操作。绑定会话允许一个或多个会话共享相同的事务和锁(但每个回话保留其自己的事务隔离级别)，并可以使用同一数据，而不会有锁冲突。可以从同一个应用程序内的多个会话中创建绑定会话，也可以从包含不同会话的多个应用程序中创建绑定会话。在一个会话中开启事务(begin tran)后，调用exec sp_getbindtoken @Token out;来取得Token，然后传入另一个会话并执行EXEC sp_bindsession @Token来进行绑定(最后的示例中演示了绑定连接)。

死锁处理方法：

(1). 根据2中提供的sql，查看那个spid处于wait状态，然后用kill spid来干掉(即破坏死锁的第四个必要条件:循环等待)；当然这只是一种临时解决方案，我们总不能在遇到死锁就在用户的生产环境上排查死锁、Kill sp，我们应该考虑如何去避免死锁。

(2). 使用SET LOCK_TIMEOUT timeout_period(单位为毫秒)来设定锁请求超时。默认情况下，数据库没有超时期限(timeout_period值为-1，可以用SELECT @@LOCK_TIMEOUT来查看该值，即无限期等待)。当请求锁超过timeout_period时，将返回错误。timeout_period值为0时表示根本不等待，一遇到锁就返回消息。设置锁请求超时，破环了死锁的第二个必要条件(请求与保持条件)。

服务器: 消息 1222，级别 16，状态 50，行 1
已超过了锁请求超时时段。

(3). SQL Server内部有一个锁监视器线程执行死锁检查，锁监视器对特定线程启动死锁搜索时，会标识线程正在等待的资源；然后查找特定资源的所有者，并递归地继续执行对那些线程的死锁搜索，直到找到一个构成死锁条件的循环。检测到死锁后，数据库引擎选择运行回滚开销最小的事务的会话作为死锁牺牲品，返回1205 错误，回滚死锁牺牲品的事务并释放该事务持有的所有锁，使其他线程的事务可以请求资源并继续运行。

两个死锁示例及解决方法