# 查看锁等待
GET SNAPSHOT FOR LOCKS ON FGEDB;

# 查看应用快照
GET SNAPSHOT FOR APPLICATIONS ON FGEDB;

# 查看表锁
SELECT
SUBSTR(TABSCHEMA, 1, 20) AS TABSCHEMA,
SUBSTR(TABNAME, 1, 30) AS TABNAME,
LOCK_MODE,
LOCK_OBJECT_TYPE,
LOCK_STATUS,
AGENT_ID
FROM SYSIBMADM.LOCKS_HELD
ORDER BY TABSCHEMA, TABNAME;

# 查看行锁
SELECT
SUBSTR(TABSCHEMA, 1, 20) AS TABSCHEMA,
SUBSTR(TABNAME, 1, 30) AS TABNAME,
LOCK_MODE,
LOCK_OBJECT_TYPE,
ROW_ID,
AGENT_ID
FROM SYSIBMADM.LOCKS_HELD
WHERE LOCK_OBJECT_TYPE = ‘ROW’
ORDER BY TABSCHEMA, TABNAME;

# 查看锁等待
SELECT
LOCK_NAME,
LOCK_OBJECT_TYPE,
LOCK_WAIT_MODE,
SUBSTR(TABSCHEMA, 1, 20) AS TABSCHEMA,
SUBSTR(TABNAME, 1, 30) AS TABNAME,
AGENT_ID,
AGENT_ID_HOLDING_LK,
LOCK_WAIT_START_TIME
FROM SYSIBMADM.LOCKWAITS;

# 创建死锁事件监控器
CREATE EVENT MONITOR DEADLOCK_MON FOR DEADLOCKS
WRITE TO FILE ‘/db2events/deadlock’
MAXFILES 10
MAXFILESIZE 1000
MANUALSTART;

# 启动事件监控器
SET EVENT MONITOR DEADLOCK_MON STATE = 1;

# 查看事件监控器数据
SELECT * FROM TABLE(EVMON_FORMAT_UE_TO_TABLES(‘DEADLOCK_MON’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, -1, 0)) AS t;

# 查看死锁事件
SELECT
SUBSTR(DEADLOCK_ID, 1, 20) AS DEADLOCK_ID,
DEADLOCK_TIMESTAMP,
SUBSTR(PARTICIPANT_NO, 1, 10) AS PARTICIPANT_NO,
SUBSTR(APPL_NAME, 1, 30) AS APPL_NAME,
SUBSTR(STMT_TEXT, 1, 100) AS STMT_TEXT
FROM DEADLOCK_EVENT
ORDER BY DEADLOCK_TIMESTAMP DESC;

# 停止事件监控器
SET EVENT MONITOR DEADLOCK_MON STATE = 0;

# 删除事件监控器
DROP EVENT MONITOR DEADLOCK_MON;

# 查看当前锁
SELECT
h.AGENT_ID AS HOLDING_AGENT,
w.AGENT_ID AS WAITING_AGENT,
SUBSTR(h.TABSCHEMA, 1, 20) AS TABSCHEMA,
SUBSTR(h.TABNAME, 1, 30) AS TABNAME,
h.LOCK_MODE AS HOLDING_MODE,
w.LOCK_MODE AS WAITING_MODE,
h.LOCK_OBJECT_TYPE
FROM SYSIBMADM.LOCKS_HELD h
INNER JOIN SYSIBMADM.LOCKWAITS w
ON h.LOCK_NAME = w.LOCK_NAME;

# 查看锁等待时间
SELECT
AGENT_ID,
SUBSTR(APPL_NAME, 1, 30) AS APPL_NAME,
LOCK_WAIT_TIME,
LOCK_MODE,
SUBSTR(TABSCHEMA, 1, 20) AS TABSCHEMA,
SUBSTR(TABNAME, 1, 30) AS TABNAME
FROM SYSIBMADM.APPLICATIONS
WHERE LOCK_WAIT_TIME > 0
ORDER BY LOCK_WAIT_TIME DESC;

# 查看应用执行的SQL
SELECT
AGENT_ID,
SUBSTR(APPL_NAME, 1, 30) AS APPL_NAME,
SUBSTR(STMT_TEXT, 1, 200) AS STMT_TEXT,
STMT_EXEC_TIME,
TOTAL_USR_CPU_TIME,
TOTAL_SYS_CPU_TIME
FROM SYSIBMADM.TOP_DYNAMIC_SQL
ORDER BY STMT_EXEC_TIME DESC
FETCH FIRST 20 ROWS ONLY;

# 优化SQL使用合适的隔离级别
# 使用UR（Uncommitted Read）
SELECT * FROM ORDERS WITH UR;

# 使用CS（Cursor Stability）
SELECT * FROM ORDERS WITH CS;

# 使用RS（Read Stability）
SELECT * FROM ORDERS WITH RS;

# 优化更新语句
# 只更新需要的列
UPDATE ORDERS
SET ORDER_STATUS = ‘PAID’,
UPDATE_TIME = CURRENT TIMESTAMP
WHERE ORDER_ID = ‘O001’;

# 使用SKIP LOCKED避免锁等待
SELECT ORDER_ID, ORDER_STATUS
FROM ORDERS
WHERE ORDER_STATUS = ‘PENDING’
SKIP LOCKED DATA
FETCH FIRST 10 ROWS ONLY;

# 配置锁超时
UPDATE DATABASE CONFIGURATION USING LOCKTIMEOUT 30;

# 配置死锁检测
UPDATE DATABASE CONFIGURATION USING DLCHKTIME 10000;

# 配置锁列表大小
UPDATE DATABASE CONFIGURATION USING LOCKLIST 4096;
UPDATE DATABASE CONFIGURATION USING MAXLOCKS 20;

# 禁用锁升级
ALTER TABLE ORDERS LOCKSIZE ROW;

# 强制断开持有锁的连接
FORCE APPLICATION (12345);

# 断开所有连接
FORCE APPLICATION ALL;

# 1. 统一更新顺序
# 所有应用都按相同顺序更新表
UPDATE TABLE_A SET … WHERE …;
UPDATE TABLE_B SET … WHERE …;

# 2. 减少事务持有锁的时间
# 尽量缩短事务长度
BEGIN
UPDATE ORDERS SET ORDER_STATUS = ‘PAID’ WHERE ORDER_ID = ‘O001’;
UPDATE INVENTORY SET QUANTITY = QUANTITY – 1 WHERE PRODUCT_ID = ‘P001’;
COMMIT;
END;

# 3. 避免在事务中进行用户交互
# 不要在事务中间等待用户输入

# 4. 使用更低的隔离级别
# 使用CS代替RR或RS
SET CURRENT ISOLATION = CS;

# 5. 优化索引，避免表锁
CREATE INDEX IDX_ORDER_CUSTOMER ON ORDERS(CUSTOMER_ID);

# 6. 设置合理的锁超时
UPDATE DATABASE CONFIGURATION USING LOCKTIMEOUT 30;

# 7. 分析死锁事件
# 使用死锁事件监控器
SELECT
DEADLOCK_TIMESTAMP,
PARTICIPANT_NO,
APPL_NAME,
STMT_TEXT
FROM DEADLOCK_EVENT
WHERE DEADLOCK_TIMESTAMP > CURRENT TIMESTAMP – 1 HOUR
ORDER BY DEADLOCK_TIMESTAMP DESC;

# 8. 应用级别重试
# 在应用中捕获死锁异常并重试
try:
# 执行事务
except DeadlockException:
# 等待一段时间后重试
time.sleep(1)
# 重试事务