WSO2 Identity Server数据库锁问题求助：UM_USER_ATTRIBUTE更新锁等待解决

问题现象 #

每日使用WSO2 Identity Server时频繁出现PostgreSQL数据库锁等待，具体日志如下：

LOG: process 11976 still waiting for ShareLock on transaction 3041474912 after 1000.041 ms
详情：持有锁的进程：22780。等待队列：11976。
上下文：更新关系"um_user_attribute"中的元组(157,8)时
语句：UPDATE UM_USER_ATTRIBUTE SET UM_ATTR_VALUE=$1 WHERE UM_USER_ID=(SELECT UM_ID FROM UM_USER WHERE UM_USER_ID=$2 AND UM_TENANT_ID=$3) AND UM_ATTR_NAME=$4 AND UM_PROFILE_ID=$5 AND UM_TENANT_ID=$6

LOG: process 7588 still waiting for ExclusiveLock on tuple (158,78) of relation 25217 of database 22641 after 1000.042 ms
详情：持有锁的进程：11118。等待队列：7588, 15295。
语句：UPDATE UM_USER_ATTRIBUTE SET UM_ATTR_VALUE=$1 WHERE UM_USER_ID=(SELECT UM_ID FROM UM_USER WHERE UM_USER_ID=$2 AND UM_TENANT_ID=$3) AND UM_ATTR_NAME=$4 AND UM_PROFILE_ID=$5 AND UM_TENANT_ID=$6

问题分析 #

锁等待核心源于UM_USER_ATTRIBUTE表的并发更新竞争，具体诱因包括：

• 更新语句使用嵌套子查询，查询效率低下，延长事务持有锁的时间
• UM_USER_ATTRIBUTE和UM_USER表缺少针对性索引，导致更新操作扫描大量数据，加剧锁竞争
• 存在长时间未提交/回滚的事务，导致锁被持续占用

解决方案 #

1. 优化更新语句 #

将嵌套子查询改为JOIN方式，减少查询开销，缩短事务执行时长：

UPDATE UM_USER_ATTRIBUTE ua
SET ua.UM_ATTR_VALUE=$1
FROM UM_USER u
WHERE ua.UM_USER_ID = u.UM_ID 
  AND u.UM_USER_ID=$2 
  AND u.UM_TENANT_ID=$3 
  AND ua.UM_ATTR_NAME=$4 
  AND ua.UM_PROFILE_ID=$5 
  AND ua.UM_TENANT_ID=$6;

2. 添加复合索引 #

为高频查询/更新字段创建复合索引，加速数据定位：

-- 优化UM_USER表的用户ID+租户ID查询
CREATE INDEX idx_um_user_userid_tenantid ON UM_USER(UM_USER_ID, UM_TENANT_ID);

-- 优化UM_USER_ATTRIBUTE表的更新条件匹配
CREATE INDEX idx_um_userattr_userid_attr_profile_tenant ON UM_USER_ATTRIBUTE(UM_USER_ID, UM_ATTR_NAME, UM_PROFILE_ID, UM_TENANT_ID);

3. 清理长事务与调整超时配置 #

• 通过PostgreSQL视图定位并终止长时间空闲的事务：

-- 查询空闲事务
SELECT pid, query, state FROM pg_stat_activity WHERE state = 'idle in transaction';
-- 终止无响应事务
SELECT pg_terminate_backend(pid);

• 调整WSO2 IS数据源配置（repository/conf/datasources/master-datasources.xml），设置合理的事务超时与自动提交参数：

<datasource>
  <!-- 其他配置 -->
  <defaultAutoCommit>true</defaultAutoCommit>
  <maxWait>30000</maxWait>
</datasource>

4. 调整数据库锁参数（可选） #

针对高并发场景，可适当调整PostgreSQL锁相关参数（需结合实际负载测试）：

• 增大max_locks_per_transaction，允许单个事务持有更多锁
• 缩短deadlock_timeout，加快死锁检测与处理

内容的提问来源于stack exchange，提问作者Dangal