V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
bwd1991
V2EX  ›  数据库

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

  •  1
     
  •   bwd1991 · 2021-03-29 18:45:23 +08:00 · 2776 次点击
    这是一个创建于 1335 天前的主题,其中的信息可能已经有所发展或是发生改变。
    HIS 数据库这几天一直死锁,应该是门诊几个表有问题,但是表、索引都重新创建过了。其他服务器也还原过数据库还是这样,有大神能解决吗?
    之前会死锁大约一周 1-2 次,现在是一直锁
    第 1 条附言  ·  2021-03-30 10:07:48 +08:00
    问题解决了。。
    描述下场景 昨天重建了几个表,索引 应该已经解决问题了,his 厂商开了 SQL Profiler 还把结果插入表了
    所以一直死锁,晚上业务量少了没有问题,今早上 IO 到了 70-80M 发现那个表一秒插入 1W+条数据,把这个停了问题解决
    39 条回复    2021-03-31 07:29:12 +08:00
    bwd1991
        1
    bwd1991  
    OP
       2021-03-29 18:51:38 +08:00
    求帮助 要通宵了 o(╥﹏╥)o
    chuckzhou
        2
    chuckzhou  
       2021-03-29 19:05:30 +08:00
    你是用的 sql 还是 oracle ?
    chuckzhou
        3
    chuckzhou  
       2021-03-29 19:06:14 +08:00
    哦,sql,开 profiler 看了吗
    bwd1991
        4
    bwd1991  
    OP
       2021-03-29 19:12:20 +08:00
    @chuckzhou 看了 开始锁集中在几个表 都导表了 表也重新建了
    redtea
        5
    redtea  
       2021-03-29 19:12:46 +08:00
    看到 his 就想到夏某某破坏医疗领域提供公共服务的计算机信息系统,被判刑 5 年多。
    zjlin1984
        6
    zjlin1984  
       2021-03-29 19:13:27 +08:00
    估计你的 sql 查询很耗资源
    bwd1991
        7
    bwd1991  
    OP
       2021-03-29 19:15:57 +08:00
    @zjlin1984 服务器跑不满 现在是 32 核 300G 内存 之前没问题 今天不知道怎么回事 能提供个思路吗
    chuckzhou
        8
    chuckzhou  
       2021-03-29 19:16:25 +08:00
    @bwd1991 先减少排查范围,逐步注释掉并发的 sql,看哪个没了之后不死锁
    bwd1991
        9
    bwd1991  
    OP
       2021-03-29 19:16:46 +08:00
    @redtea 应该不是 被攻击的话应该瘫了
    bwd1991
        10
    bwd1991  
    OP
       2021-03-29 19:17:33 +08:00
    @chuckzhou 代码没有。。我们十几个测试没问题,门诊量一上来就崩了
    bwd1991
        11
    bwd1991  
    OP
       2021-03-29 19:19:20 +08:00
    @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
        12
    dswyzx  
       2021-03-29 19:21:48 +08:00
    chuckzhou
        13
    chuckzhou  
       2021-03-29 19:32:27 +08:00
    @bwd1991 ALLOW_ROW_LOCKS 和 ALLOW_PAGE_LOCKS 改成 on 试试,一般这个都打开的。
    bwd1991
        14
    bwd1991  
    OP
       2021-03-29 20:03:07 +08:00
    @chuckzhou o(╥﹏╥)o 上午是开着的 下午才关上 还能有什么原因吗
    chuckzhou
        15
    chuckzhou  
       2021-03-29 20:20:07 +08:00
    @bwd1991 死锁的语句是什么啊?
    Leigg
        16
    Leigg  
       2021-03-29 20:26:34 +08:00 via Android
    不要说废话,直接贴日志。
    bwd1991
        17
    bwd1991  
    OP
       2021-03-29 20:35:00 +08:00
    @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
        18
    chuckzhou  
       2021-03-29 21:20:46 +08:00
    @bwd1991 都是 nolock,看不出来有什么问题
    ferock
        19
    ferock  
       2021-03-29 21:28:50 +08:00 via iPhone
    @bwd1991 #7

    最烦微软系的堆硬件,一点技术含量都没有,系统资源那么丰富量一上来还不是挂,前期代码能力后期运维能力才是关键。
    bwd1991
        20
    bwd1991  
    OP
       2021-03-29 22:00:09 +08:00
    @ferock 没办法 医疗行业都是这样
    bwd1991
        21
    bwd1991  
    OP
       2021-03-29 22:02:56 +08:00
    @chuckzhou 能加的都加了 o(╥﹏╥)o
    Anybfans
        22
    Anybfans  
       2021-03-29 22:44:08 +08:00 via iPhone
    看下这个字段的数据类型 charge_status 。是不是有隐式数据类型转换
    gBurnX
        23
    gBurnX  
       2021-03-29 22:48:06 +08:00   ❤️ 1
    两个原因:

    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
        24
    bwd1991  
    OP
       2021-03-29 23:02:33 +08:00
    @Anybfans 类型是 char
    bwd1991
        25
    bwd1991  
    OP
       2021-03-29 23:03:47 +08:00
    @gBurnX 硬件应该不会 cpu 20%以下 内存不过半
    主要是之前一直没有问题 今天突然这样了 有什么原因会导致这样吗 没有升级程序,数据库也没动过
    xioxu
        26
    xioxu  
       2021-03-29 23:23:28 +08:00
    1. 用 sql profiler 抓取死锁相关日志,查看引起死锁的语句
    2. 分析引起死锁的代码,看是否存在争抢资源的可能(一般是肯定存在),否则不会死锁
    3. 修改代码对同一资源的获取顺序,保持一致(以规避死锁)
    xioxu
        27
    xioxu  
       2021-03-29 23:25:37 +08:00
    @bwd1991 问题突发的原因有很多,譬如数据量现在上去了、索引碎片... 导致死锁概率增大,本质上还是代码就有死锁问题导致的
    bwd1991
        28
    bwd1991  
    OP
       2021-03-29 23:32:44 +08:00
    @xioxu o(╥﹏╥)o 代码肯定有问题 HIS 开发基本不管
    leeg810312
        29
    leeg810312  
       2021-03-29 23:58:18 +08:00 via Android
    死锁通常都是代码逻辑问题。估计你们的 HIS 有些操作是跨好几个表的事务,可能是部分表数据量大了,事务运行时间较长,且事务中还有查询操作,这样在大量请求并发时,很容易产生表锁竞争导致死锁。建议按以下排查
    1 、死锁表的数据量是否较大,代码中的 sql 是否较慢,数据量到几百万上千万或更多,优化索引,同时考虑做分区或分表
    2 、检查代码中的跨多表事务,尽量在事务外做查询,尽可能减少跨表事务的数量,尽可能缩减跨表事务的跨表范围,整体上降低多表锁竞争的概率
    3 、没有触发器和存储过程是最好,有的话都改在代码里实现。这 2 个都是测试困难户,写在数据库里很容易被忘记,也容易让数据产生代码预期外的结果。喜欢存储过程的人倾向写很长跨多表的存储过程,调试起来及其麻烦,且高并发时非常容易产生死锁
    Fule
        30
    Fule  
       2021-03-30 00:10:09 +08:00
    你的 `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
        31
    realpg  
       2021-03-30 00:12:17 +08:00
    就是互锁了
    报 BUG,让开发改
    否则无解
    gBurnX
        32
    gBurnX  
       2021-03-30 01:24:19 +08:00
    @bwd1991

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

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

    3.你现在要做的,就是按照我在 23 楼,第 2 点给出的具体方案去进行修改。按这方案你肯定会发现一堆锁定顺序相反的操作 SQL
    cnzjl
        33
    cnzjl  
       2021-03-30 07:05:08 +08:00
    看到这个问题,我以为咱俩是同事
    bwd1991
        34
    bwd1991  
    OP
       2021-03-30 07:35:31 +08:00
    @gBurnX 1 、cpu 不到 10% 内存还剩 100G 磁盘读取有问题 巅峰额能到 70-80G
    2 、 我们没有源码 只能 sql 跟踪语句 能处理的都处理了 ,我说之前没问题是想问问有什么情况突然导致这样吗,是全库重建索引还是怎么着
    bwd1991
        35
    bwd1991  
    OP
       2021-03-30 08:32:14 +08:00
    @bwd1991 #34 数错位数了 70-80M 找到原因了 跟踪语句插入了 排除这个问题了
    zjlin1984
        36
    zjlin1984  
       2021-03-30 08:32:48 +08:00
    1. with nolock 先试试
    2. 把相关的 sql 语句找出来,看看有没有 join,再看看逻辑读写的次数多少,where 语句的过滤条件是不是都在索引里面。
    gBurnX
        37
    gBurnX  
       2021-03-30 13:33:11 +08:00
    @bwd1991 观察一下磁盘活动时间。

    用跟踪器把 SQL 都扒下来,按照我在上面说的,把锁定顺序完全相反的 SQL 找出来,发给开发,让开发修改。
    thtznet
        38
    thtznet  
       2021-03-30 16:49:03 +08:00
    2021 年了,系统可以更新下了,不要面向数据库表开发,建领域模型,用 ORM 。虽然 ORM 可能效率不是极致的优化,但是比大部分草台班子手写 SQL 还是要靠谱点。
    bwd1991
        39
    bwd1991  
    OP
       2021-03-31 07:29:12 +08:00
    @thtznet his 是 ORM 但是现在有一半业务是通过存储过程实现的 HIS 改不动现在都自己维护了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1291 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 36ms · UTC 17:56 · PVG 01:56 · LAX 09:56 · JFK 12:56
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.