SQL SERVER2008 有大神吗，需要帮助，有偿

[bwd1991]

HIS 数据库这几天一直死锁，应该是门诊几个表有问题，但是表、索引都重新创建过了。其他服务器也还原过数据库还是这样，有大神能解决吗？
之前会死锁大约一周 1-2 次，现在是一直锁

问题解决了。。
描述下场景 昨天重建了几个表，索引 应该已经解决问题了，his 厂商开了 SQL Profiler 还把结果插入表了
所以一直死锁，晚上业务量少了没有问题，今早上 IO 到了 70-80M 发现那个表一秒插入 1W+条数据，把这个停了问题解决

[bwd1991]

求帮助 要通宵了 o(╥﹏╥)o

[chuckzhou]

你是用的 sql 还是 oracle ？

[chuckzhou]

哦，sql，开 profiler 看了吗

[bwd1991]

@chuckzhou 看了 开始锁集中在几个表 都导表了 表也重新建了

[redtea]

看到 his 就想到夏某某破坏医疗领域提供公共服务的计算机信息系统，被判刑 5 年多。

[zjlin1984]

估计你的 sql 查询很耗资源

[bwd1991]

@zjlin1984 服务器跑不满 现在是 32 核 300G 内存 之前没问题 今天不知道怎么回事 能提供个思路吗

[chuckzhou]

@bwd1991 先减少排查范围，逐步注释掉并发的 sql，看哪个没了之后不死锁

[bwd1991]

@redtea 应该不是 被攻击的话应该瘫了

[bwd1991]

@chuckzhou 代码没有。。我们十几个测试没问题，门诊量一上来就崩了

[bwd1991]

@chuckzhou 会和索引的 PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF

这些属性有关系吗

[dswyzx]

https://www.cnblogs.com/mq0036/p/12916606.html
我记得锁可以手动清理

[chuckzhou]

@bwd1991 ALLOW_ROW_LOCKS 和 ALLOW_PAGE_LOCKS 改成 on 试试，一般这个都打开的。

[bwd1991]

@chuckzhou o(╥﹏╥)o 上午是开着的 下午才关上 还能有什么原因吗

[chuckzhou]

@bwd1991 死锁的语句是什么啊？

[Leigg]

不要说废话，直接贴日志。

[bwd1991]

@chuckzhou 下面的函数和一个单条更新语句
ALTER PROCEDURE [dbo].[GetIcCardBalance]
@sID varchar(12),
@balance decimal(10,2) out

AS
BEGIN

set @balance = 0

set @balance = (select isnull(balance,0) from ic_register (NOLOCK) where patient_id = @sID) -

isnull((select sum(isnull(charge_price * charge_amount*caoyao_fu,0))

from mz_detail_charge WITH (nolock) where patient_id = @sID and charge_status = '2'),0)

--set @balance = dbo.GetICRealBalance(@sID)

if @@error = 0 return 0

else

return -1

END

[chuckzhou]

@bwd1991 都是 nolock，看不出来有什么问题

[ferock]

@bwd1991 #7

最烦微软系的堆硬件，一点技术含量都没有，系统资源那么丰富量一上来还不是挂，前期代码能力后期运维能力才是关键。

[bwd1991]

@ferock 没办法 医疗行业都是这样

[bwd1991]

@chuckzhou 能加的都加了 o(╥﹏╥)o

[Anybfans]

看下这个字段的数据类型 charge_status 。是不是有隐式数据类型转换

[gBurnX]

两个原因：

1.出现死锁，先看硬件负载。如果硬件负载高，有可能是某个操作需要锁表，同时还需要消耗大量硬件资源，那么在该操作完成前，其他操作会因为锁而竞争或排队，造成假死锁的现象。这种事情可以通过等待来解决。

这个阶段主要看看 CPU 使用率、内存使用率、磁盘活动时间、网络带宽使用率。解决方案是，哪个硬件使用率太高，就升级该硬件。比如，如果磁盘活动时间一直为 100%，而且用的是机械硬盘，那么换 SSD 甚至 M.2 NVME 协议的一线品牌的 SSD 。


2.如果死锁但各硬件负载很低，那么很有可能是出现互相锁住的问题了。解决这个问题，是一件体力活。方法如下：

在程序代码、数据库存储过程或函数等存在代码的位置，以 [Lock] 不区分大小写的方式，找出所有锁定语句所涉及的表名，在 Excel 里把这些表名称列出来。不区分大小写的意思是，有些地方可能是 lock，有些地方可能是 LOCK，或 Lock 等等。

然后把这些表名，在 Excel 里设置一个顺序。比如，在 Excel 里，设置了顺序为：
1 TableA
2 TableB
3 TableC
意思是，先锁 TableA，再锁 TableB，最后锁 TableC 。

按照这个顺序，修改所有涉及到 Lock 的地方。

比如，你发现某个存储过程，锁定顺序为：
......
SELECT * FROM TableC With (TabLockX) WHERE id = 3
......
SELECT * FROM TableB With (TabLockX) WHERE id = 2
......

该存储过程先锁了 TableC，然后再锁了 TableB，这种锁定顺序违背了 Excel，因此调整为：
......
SELECT * FROM TableB With (TabLockX) WHERE id = 2
......
SELECT * FROM TableC With (TabLockX) WHERE id = 3
......


调整时注意不能改变业务逻辑。如果改变了业务逻辑，需要按照 Excel 的锁定顺序，重新实现该存储过程。

把所有涉及到锁的代码或存储过程或函数等，按照这种方式，全部修改，修改完毕后，锁问题应该能解决了。

[bwd1991]

@Anybfans 类型是 char

[bwd1991]

@gBurnX 硬件应该不会 cpu 20%以下 内存不过半
主要是之前一直没有问题 今天突然这样了 有什么原因会导致这样吗 没有升级程序，数据库也没动过

[xioxu]

1. 用 sql profiler 抓取死锁相关日志，查看引起死锁的语句
2. 分析引起死锁的代码，看是否存在争抢资源的可能（一般是肯定存在），否则不会死锁
3. 修改代码对同一资源的获取顺序，保持一致（以规避死锁）

[xioxu]

@bwd1991 问题突发的原因有很多，譬如数据量现在上去了、索引碎片... 导致死锁概率增大，本质上还是代码就有死锁问题导致的

[bwd1991]

@xioxu o(╥﹏╥)o 代码肯定有问题 HIS 开发基本不管

[leeg810312]

死锁通常都是代码逻辑问题。估计你们的 HIS 有些操作是跨好几个表的事务，可能是部分表数据量大了，事务运行时间较长，且事务中还有查询操作，这样在大量请求并发时，很容易产生表锁竞争导致死锁。建议按以下排查
1 、死锁表的数据量是否较大，代码中的 sql 是否较慢，数据量到几百万上千万或更多，优化索引，同时考虑做分区或分表
2 、检查代码中的跨多表事务，尽量在事务外做查询，尽可能减少跨表事务的数量，尽可能缩减跨表事务的跨表范围，整体上降低多表锁竞争的概率
3 、没有触发器和存储过程是最好，有的话都改在代码里实现。这 2 个都是测试困难户，写在数据库里很容易被忘记，也容易让数据产生代码预期外的结果。喜欢存储过程的人倾向写很长跨多表的存储过程，调试起来及其麻烦，且高并发时非常容易产生死锁

[Fule]

你的 `mz_detail_charge` 表里有多少字段？如果远多于 `charge_price`, `charge_amount`, `caoyao_fu`, `patient_id`和`charge_status`，那么试试创建一个覆盖索引:

```
CREATE INDEX IX_mz_detail_charge_price_amount_caoyao_fu ON mz_detail_charge
(
patient_id ASC,
charge_status ASC
)
INCLUDE
(
charge_price,
charge_amount,
caoyao_fu
)
WITH
(
PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
)
```

[realpg]

就是互锁了
报 BUG，让开发改
否则无解

[gBurnX]

@bwd1991

1.不要拍脑袋说硬件应该不会。CPU 、内存、网络用任务管理器去查，磁盘活动时间用资源监视器去查。做事情要仔细，要落地。

2.不要说什么以前没问题，现在突然有问题。只要系统有 bug，什么时候被你们发现，都有可能。bug 发作有早有晚，而且说不定早发作了，只是大家没发现或并不明显。

3.你现在要做的，就是按照我在 23 楼，第 2 点给出的具体方案去进行修改。按这方案你肯定会发现一堆锁定顺序相反的操作 SQL

[cnzjl]

看到这个问题，我以为咱俩是同事

[bwd1991]

@gBurnX 1 、cpu 不到 10% 内存还剩 100G 磁盘读取有问题 巅峰额能到 70-80G
2 、 我们没有源码 只能 sql 跟踪语句 能处理的都处理了 ，我说之前没问题是想问问有什么情况突然导致这样吗，是全库重建索引还是怎么着

[bwd1991]

@bwd1991 #34 数错位数了 70-80M 找到原因了 跟踪语句插入了 排除这个问题了

[zjlin1984]

1. with nolock 先试试
2. 把相关的 sql 语句找出来，看看有没有 join，再看看逻辑读写的次数多少，where 语句的过滤条件是不是都在索引里面。

[gBurnX]

@bwd1991 观察一下磁盘活动时间。

用跟踪器把 SQL 都扒下来，按照我在上面说的，把锁定顺序完全相反的 SQL 找出来，发给开发，让开发修改。

[thtznet]

2021 年了，系统可以更新下了，不要面向数据库表开发，建领域模型，用 ORM 。虽然 ORM 可能效率不是极致的优化，但是比大部分草台班子手写 SQL 还是要靠谱点。

[bwd1991]

@thtznet his 是 ORM 但是现在有一半业务是通过存储过程实现的 HIS 改不动现在都自己维护了