華為云高斯db線上死鎖問題
前言
線上高斯db在業(yè)務(wù)高峰期出現(xiàn)上萬條死鎖苇经，華為云給出了死鎖日志（ps 阿里云是直接可以在控制臺(tái)看到的）
死鎖日志
RECORD LOCKS space id 6534 page no 112806 n bits 568 index idx_a of table `XXX`.`XXXX` trx id 756319989 lock_mode X locks gap before rec
Record lock, heap no 474 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 80000000000010ba; asc ;;
1: len 8; hex 8000000000006b31; asc k1;;
2: len 8; hex 80000000002ec2be; asc . ;;
日志已經(jīng)影去公司相關(guān)信息彰导，看到明顯的gap lock導(dǎo)致的死鎖饲梭。數(shù)據(jù)庫設(shè)置的默認(rèn)隔離級別是read committed煌张。依稀記得rc級別不會(huì)有g(shù)ap lock的。查詢mysql官網(wǎng)
gap lock
Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.
與記憶中的基本一致娃豹。我們沒有使用外鍵也沒有使用唯一鍵檢查焚虱，是不會(huì)存在gap lock的。
再次跟華為云確認(rèn)他們與mysql 8.0行為是一致的懂版。
華為云排查
華為云排查后告知我們數(shù)據(jù)庫默認(rèn)隔離級別是對的鹃栽，原因是我們的session 每次都設(shè)置了會(huì)話的隔離級別為rr。代碼中我們都是使用spring 申明式事務(wù)做事務(wù)躯畴，排查后并沒有發(fā)現(xiàn)使用rr級別民鼓。我們跟華為云溝通后，華為云的意思是讓我排查下spring的源碼蓬抄，查看下是不是哪里設(shè)置了事務(wù)的隔離級別丰嘉。
排查源碼
org.springframework.transaction.support.AbstractPlatformTransactionManager[getTransaction(TransactionDefinition definition)]
org.springframework.jdbc.datasource.DataSourceTransactionManager[doBegin(Object transaction, TransactionDefinition definition)]
org.springframework.jdbc.datasource.DataSourceUtils[prepareConnectionForTransaction(Connection con, TransactionDefinition definition)]
com.mysql.cj.jdbc.ConnectionImpl[setTransactionIsolation(int level)]
包和類名中跨號內(nèi)是方法
這邊只貼spring和jdbc中設(shè)置隔離級別部分源碼
// 非默認(rèn)級別的情況下去設(shè)置隔離級別
if (definition != null && definition.getIsolationLevel() != TransactionDefinition.ISOLATION_DEFAULT) {
if (logger.isDebugEnabled()) {
logger.debug("Changing isolation level of JDBC Connection [" + con + "] to " +
definition.getIsolationLevel());
}
int currentIsolation = con.getTransactionIsolation();
if (currentIsolation != definition.getIsolationLevel()) {
previousIsolationLevel = currentIsolation;
con.setTransactionIsolation(definition.getIsolationLevel());
}
}
jdbc
switch (level) {
case java.sql.Connection.TRANSACTION_NONE:
throw SQLError.createSQLException(Messages.getString("Connection.24"), getExceptionInterceptor());
case java.sql.Connection.TRANSACTION_READ_COMMITTED:
sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED";
break;
case java.sql.Connection.TRANSACTION_READ_UNCOMMITTED:
sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
break;
case java.sql.Connection.TRANSACTION_REPEATABLE_READ:
sql = "SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ";
break;
case java.sql.Connection.TRANSACTION_SERIALIZABLE:
sql = "SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE";
break;
default:
throw SQLError.createSQLException(Messages.getString("Connection.25", new Object[] { level }),
MysqlErrorNumbers.SQL_STATE_DRIVER_NOT_CAPABLE, getExceptionInterceptor());
}
spring默認(rèn)是不會(huì)去設(shè)置事務(wù)的隔離級別,設(shè)置隔離級別是jdbc實(shí)現(xiàn)的。所以spring也是沒問題的嚷缭。
最終結(jié)論
業(yè)務(wù)側(cè)并沒有問題饮亏，只能只華為云的問題了耍贾。
最后運(yùn)維排查發(fā)現(xiàn)華為云的數(shù)據(jù)庫代理默認(rèn)的隔離級別居然是repeatable-read，設(shè)備是read-committed路幸，也就是代理層每次會(huì)設(shè)置事務(wù)的隔離級別荐开。坑爹華為云简肴。
