# 使用CS隔离级别
SELECT * FROM ORDERS WITH CS;

# 设置默认隔离级别
SET CURRENT ISOLATION = CS;

# 使用SKIP LOCKED
SELECT * FROM ORDERS
WHERE ORDER_STATUS = ‘PENDING’
SKIP LOCKED DATA
FETCH FIRST 10 ROWS ONLY;

# 使用FOR UPDATE锁定
SELECT * FROM ORDERS
WHERE ORDER_ID = ‘O001’
FOR UPDATE WITH RS;

# 查看表锁设置
SELECT
TABNAME,
LOCKSIZE
FROM SYSCAT.TABLES
WHERE TABNAME = ‘ORDERS’;

# 使用最小锁模式
UPDATE ORDERS
SET ORDER_STATUS = ‘PAID’
WHERE ORDER_ID = ‘O001’
WITH RS USE AND KEEP UPDATE LOCKS;

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

# 监控锁等待
SELECT
LOCK_NAME,
LOCK_OBJECT_TYPE,
LOCK_WAIT_MODE,
TABNAME,
AGENT_ID_HOLDING_LK
FROM SYSIBMADM.LOCKWAITS;

UPDATE ACCOUNT
SET BALANCE = BALANCE + 100
WHERE ACCOUNT_ID = ‘A002’;
END;

# 避免长事务
# 不要在事务中执行耗时操作

# 批量提交
DECLARE v_count INTEGER DEFAULT 0;

FOR i AS cur CURSOR FOR
SELECT ORDER_ID FROM ORDERS WHERE ORDER_STATUS = ‘NEW’
DO
UPDATE ORDERS
SET ORDER_STATUS = ‘PROCESSING’
WHERE ORDER_ID = i.ORDER_ID;

SET v_count = v_count + 1;

IF MOD(v_count, 100) = 0 THEN
COMMIT;
END IF;
END FOR;

COMMIT;

# 检查更新结果
IF SQLCODE = 100 THEN
— 版本不匹配，重试
END IF;

# 使用悲观锁
SELECT * FROM ORDERS
WHERE ORDER_ID = ‘O001’
FOR UPDATE WITH RS;

UPDATE ORDERS
SET ORDER_STATUS = ‘PAID’
WHERE ORDER_ID = ‘O001’;

COMMIT;

# 使用存储过程封装事务
CREATE OR REPLACE PROCEDURE SP_TRANSFER(
IN p_from_account VARCHAR(20),
IN p_to_account VARCHAR(20),
IN p_amount DECIMAL(18, 2),
OUT p_result_code VARCHAR(10)
)
LANGUAGE SQL
BEGIN
DECLARE v_balance DECIMAL(18, 2);

SET p_result_code = ‘SUCCESS’;

START TRANSACTION;

SELECT BALANCE INTO v_balance
FROM ACCOUNT
WHERE ACCOUNT_ID = p_from_account
WITH RS USE AND KEEP UPDATE LOCKS;

IF v_balance < p_amount THEN SET p_result_code = 'FAIL'; ROLLBACK; RETURN; END IF; UPDATE ACCOUNT SET BALANCE = BALANCE - p_amount WHERE ACCOUNT_ID = p_from_account; UPDATE ACCOUNT SET BALANCE = BALANCE + p_amount WHERE ACCOUNT_ID = p_to_account; COMMIT; END;

# 配置连接池
UPDATE DATABASE MANAGER CONFIGURATION USING NUM_POOLAGENTS 50;
UPDATE DATABASE MANAGER CONFIGURATION USING MAX_POOLAGENTS 100;

# 查看连接配置
GET DATABASE MANAGER CONFIGURATION;

# 监控连接使用
SELECT
AGENT_ID,
APPL_NAME,
APPL_STATUS,
CLIENT_PID
FROM SYSIBMADM.APPLICATIONS;

# 监控连接池
SELECT
POOL_CONFIG,
POOL_SIZE,
POOL_FREE
FROM SYSIBMADM.SNAPDBM;

# 设置空闲超时
UPDATE DATABASE MANAGER CONFIGURATION USING IDLE_TIMEOUT 600;

# 断开空闲连接
FORCE APPLICATION ALL;

# 断开指定连接
FORCE APPLICATION (12345);

# 监控长时间运行的连接
SELECT
AGENT_ID,
APPL_NAME,
UOW_START_TIME,
CURRENT TIMESTAMP – UOW_START_TIME AS DURATION
FROM SYSIBMADM.APPLICATIONS
WHERE UOW_START_TIME < CURRENT TIMESTAMP - 1 HOUR ORDER BY DURATION DESC; # 创建连接池监控脚本 #!/bin/bash # monitor_connections.sh db2 connect to FGEDB db2 "SELECT COUNT(*) AS TOTAL_CONNECTIONS, COUNT(CASE WHEN APPL_STATUS = 'UOWWAIT' THEN 1 END) AS IDLE_CONNECTIONS, COUNT(CASE WHEN UOW_START_TIME < CURRENT TIMESTAMP - 1 HOUR THEN 1 END) AS LONG_RUNNING FROM SYSIBMADM.APPLICATIONS" db2 connect reset