menu
文章851
标签121
分类10
© 2026 Karp 的技术博客
京ICP备15065734号-3

Mysql 死锁问题查询记录
2023年9月08日 236 汉字 21 围观 0 评论 数据库 mysql

先来查看 死锁日志

*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2023-09-08 13:02:43 0x7f224b8f0700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 0 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1913166 srv_active, 0 srv_shutdown, 1788 srv_idle
srv_master_thread log flush and writes: 1914954
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 36736282
OS WAIT ARRAY INFO: signal count 286633583
RW-shared spins 0, rounds 192061476, OS waits 6425935
RW-excl spins 0, rounds 612396810, OS waits 6259209
RW-sx spins 50861198, rounds 296338326, OS waits 1184937
Spin rounds per wait: 192061476.00 RW-shared, 612396810.00 RW-excl, 5.83 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-09-08 11:58:01 0x7f224c6df700
*** (1) TRANSACTION:
TRANSACTION 1818540369, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 1315 lock struct(s), heap size 123088, 46885 row lock(s), undo log entries 3134
MySQL thread id 21203269, OS thread handle 139785450133248, query id 22714701081 127.0.0.1 databases updating

DELETE FROM table_order WHERE  `status` > 2  AND `is_archive` = 1  AND `update_time` < 1694173681653
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:


RECORD LOCKS space id 484 page no 5890 n bits 112 index PRIMARY of table `sdatabase`.`table_order` trx id 1818540369 lock_mode X waiting
Record lock, heap no 31 PHYSICAL RECORD: n_fields 54; compact format; info bits 0
 0: len 8; hex 0000000005xxxxx; asc      o  ;;

*** (2) TRANSACTION:
TRANSACTION 1818540612, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
9 lock struct(s), heap size 1136, 9 row lock(s), undo log entries 20
MySQL thread id 21199366, OS thread handle 139785287890688, query id 22714706392 127.0.0.1 databases updating

UPDATE table_order SET `status` = 2 `update_time` = 1694174281908 WHERE `uid` = '2748'  AND `order_id` = 91205746  AND `status` in ( 1, 2 )  AND `num` >= 69

*** (2) HOLDS THE LOCK(S): 
RECORD LOCKS space id 484 page no 5890 n bits 112 index PRIMARY of table `sdatabase`.`table_order` trx id 1818540612 lock_mode X locks rec but not gap
Record lock, heap no 31 PHYSICAL RECORD: n_fields 54; compact format; info bits 0
 0: len 8; hex 00000000056xxxx; asc      o  ;;


Record lock, heap no 32 PHYSICAL RECORD: n_fields 54; compact format; info bits 0
 0: len 8; hex 000000000xxxx; asc      o  ;;


Record lock, heap no 38 PHYSICAL RECORD: n_fields 54; compact format; info bits 0
 0: len 8; hex 00000000056fb08b; asc      o  ;;
 

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 484 page no 5890 n bits 112 index PRIMARY of table `sdatabase`.`table_order` trx id 1818540612 lock_mode X locks rec but not gap waiting
Record lock, heap no 13 PHYSICAL RECORD: n_fields 54; compact format; info bits 0
 0: len 8; hex 00000000056fb072; asc      o r;;

从上面的日志中可以看出到两张同表的死锁

SQL 1 (HOLDS THE LOCK(S))

UPDATE table_order SET `status` = 2 `update_time` = 1694174281908 WHERE `uid` = '2748'  AND `order_id` = 91205746  AND `status` in ( 1, 2 )  AND `num` >= 69
``
### SQL 2 (WAITING FOR THIS LOCK TO BE GRANTED)

DELETE FROM table_order WHERE status > 2 AND is_archive = 1 AND update_time < 1694173681653

先下表索引

PRIMARY KEY (order_id),
KEY idx_origin_ts (status,num),
KEY idx_uid (uid),

从上面的表索引 可以看出 `SQL 1` 使用的是 主键索引 拿锁也是行级锁.

`SQL 2` 理论上应该能用到 `idx_origin_ts` 索引, 但实际上并没有用到索引, `SQL2`是没用的索引.
没用到索引的原因比较简单 因为字段重复内容过多 基数较大索引, mysql优化器认为全表扫描比走索引快，它就会放弃索引。

解决问题就简单了, 偷懒弄个覆盖索引 `status`,`is_archive` , `update_time` 构建联合索引

Supervisor Event Listener 任务监控与告警
2023年9月03日 575 汉字 26 围观 0 评论 编程语言 python Supervisor

场景描述

Supervisor 脚本定时重启脚本任务, 任务中存在多个子进程, 重启服务基本都是能一波带走所有子进程.
但这次发现有个服务重启失败, 查看原因是存在子进程未kill掉, 子进程占用服务端口, 重启服务端口被占用最后服务BACKOFF了, 原因就是子进程没杀掉, 莫得办法. 下面配置也都是有的,因为之前踩过坑.没想到还是有问题.

;使用supervisorctl停止时，子进程也会一起停止
stopasgroup=true
;向进程组发送kill信号，包括子进程
killasgroup=true

为了监控这种服务异常拉起失败的场景, 百度一下 Supervisor 是有一个 事件监听的 Event Listener.

Event Types 由 Supervisor 官方定义，覆盖了进程运行生命周期的各种状态。

下面翻译一些常用的类型 :

|Event |解释|
|-|-|
|PROCESS_STATE| 进程状态发生改变
|PROCESS_STATE_STARTING| 进程状态从其他状态转换为正在启动(Supervisord的配置项中有startsecs配置项，是指程序启动时需要程序至少稳定运行x秒才认为程序运行正常，在这x秒中程序状态为正在启动)|
|PROCESS_STATE_RUNNING |进程从正在启动状态转换为正在运行状态|
|PROCESS_STATE_BACKOFF |进程从正在启动状态转换为启动失败状态，Supervisor 正在重启该进程|
|PROCESS_STATE_STOPPING |进程从正在运行状态或正在启动状态转换为正在停止状态|
|PROCESS_STATE_EXITED |进程从正在运行状态转换为退出状态，expected 退出码，如果是 0 表示进程异常退出，1 表示进程正常退出。|
|PROCESS_STATE_STOPPED |进程从正在停止状态转换为已停止状态|
|PROCESS_STATE_FATAL |进程从启动失败状态(BACKOFF)转换为失败状态(FATAL). 意味着 startretries 尝试次数已达上限，Supervisor 已放弃重启该进程。|
|PROCESS_LOG |进程产生日志输出，被管理的进程需配置，stdout_events_enabled=true or stderr_events_enabled=true 这个事件通知才会生效。|
|PROCESS_LOG_STDOUT |进程产生标准输出，被管理的进程需配置，stdout_events_enabled=true|
|PROCESS_LOG_STDERR |进程产生错误输出，被管理的进程需配置，stderr_events_enabled=true|

Supervisor 配置如下

[eventlistener:phplistener]
command=/usr/bin/python3 /opt/webserver/listener.py   
events=PROCESS_STATE_EXITED,PROCESS_STATE_FATAL  ; 监控事件

stdout_logfile=/opt/weblogs/phplistener_stdout.log
stderr_logfile=/opt/weblogs/phplistener_stderr.log

需要特别注意的是 这个eventlistener 配置修改后不能通过 supervisorctl update 更新配置. 要吗remove后在update/add ,要不就reread.

/opt/webserver/listener.py 文件

# encoding: utf-8
import sys
import requests
import time  # 步骤1：导入时间模块


def write_stdout(s):
    # Only eventlistener protocol messages may be sent to stdout
    sys.stdout.write(s)
    sys.stdout.flush()

def write_stderr(s):
    sys.stderr.write(s)
    sys.stderr.flush()

def send_message(message):
    url = 'https://xxxx/send_message'
    params = {'content': message}
    response = requests.post(url, params=params)
    return response


def main():
    while True:
        write_stdout('READY\n')
        line = sys.stdin.readline()
        write_stderr(line)
        headers = dict([x.split(':') for x in line.split() ])
        eventname = headers['eventname']

        data = sys.stdin.read(int(headers['len']))
        write_stderr(data+'\n')
        datas = dict([y.split(':') for y in data.split() ])

        processname = datas['processname']
        from_state = datas['from_state']

        # Customize this part to perform specific actions based on the event data
        event_message = "【 Supervisor Listener 服务异常 】 \n\n 服务名称 : "+processname+"\n Event : "+eventname+"\n State : "+from_state+"\n\n"+line+"\n"+data+"\n"

        # 仅报警失败的服务
        if eventname == 'PROCESS_STATE_FATAL':

            response = send_message(event_message)
            if response.status_code == 200:
                write_stderr("Message sent successfully.\n")
            else:
                write_stderr("Failed to send message.\n")

        write_stdout('RESULT 2\nOK')

if __name__ == '__main__':
    main()
">