[Mariadb] spring session 사용시 java.sql.SQLException: Deadlock found when trying to get lock 발생

 

Spring Session(SpringBoot) 을 사용하다 간헐적으로 Deadlock이 발생하는 것을 확인하였다.

2022-02-14 11:30:00.144 DEBUG 23456 --- [nio-8080-exec-5] k.c.s.mapper.Mapper.selectCategorySql  : ==> Parameters: 999999999(String), 소분류(String)
2022-02-14 11:30:00.146 ERROR 23456 --- [nio-8080-exec-7] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DeadlockLoserDataAccessException: PreparedStatementCallback; SQL [UPDATE SPRING_SESSION SET SESSION_ID = ?, LAST_ACCESS_TIME = ?, MAX_INACTIVE_INTERVAL = ?, EXPIRY_TIME = ?, PRINCIPAL_NAME = ? WHERE PRIMARY_ID = ?]; (conn=188679) Deadlock found when trying to get lock; try restarting transaction; nested exception is java.sql.SQLTransactionRollbackException: (conn=188679) Deadlock found when trying to get lock; try restarting transaction] with root cause

java.sql.SQLException: Deadlock found when trying to get lock; try restarting transaction
Query is: UPDATE SPRING_SESSION SET SESSION_ID = ?, LAST_ACCESS_TIME = ?, MAX_INACTIVE_INTERVAL = ?, EXPIRY_TIME = ?, PRINCIPAL_NAME = ? WHERE PRIMARY_ID = ?, parameters ['xxxxxxxx-ab42-4dbe-8514-8bb0e1908588',1644805799992,28800,1644834599992,<null>,'xxxxxxxx-10ed-4cc3-bebc-97e589807f01']
java thread: http-nio-8080-exec-7
	at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:163) ~[mariadb-java-client-2.3.0.jar:na]
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:280) ~[mariadb-java-client-2.3.0.jar:na]
	at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:223) ~[mariadb-java-client-2.3.0.jar:na]
	at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:159) ~[mariadb-java-client-2.3.0.jar:na]
	at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeUpdate(MariaDbPreparedStatementClient.java:194) ~[mariadb-java-client-2.3.0.jar:na]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-3.2.0.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-3.2.0.jar:na]
	at org.springframework.jdbc.core.JdbcTemplate.lambda$update$0(JdbcTemplate.java:866) ~[spring-jdbc-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:616) ~[spring-jdbc-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:861) ~[spring-jdbc-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:916) ~[spring-jdbc-5.1.9.RELEASE.jar:5.1.9.RELEASE]
        ... 생략 ...
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[na:na]
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na]
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.22.jar:9.0.22]
	at java.base/java.lang.Thread.run(Thread.java:834) ~[na:na]

 

Spring Session 의 문제인 줄 알았으나 Mairadb 트랜잭션 설정을 변경하면 해결되는 것으로 파악되었다.

현재 Mariadb의 트랜잭션 설정(Transaction Isolation level - tx_isolation)은 기본 설정값인 REPEATABLE-READ 으로 되어 있다.

READ_COMMITED 으로 트랜잭션 설정을 변경하려면 my.cnf 에서 transaction-isolation=READ-COMMITTED 을 작성한 뒤 MariaDB를 재기동하면 영구적용된다.

신뢰도와 성능을 생각해서 적절한 설정값을 지정해야 하는데 경험상 READ_COMMITED 으로 설정하면 운영사이트에서도 문제없이 사용할 수 있는 것을 확인하였다. (하루에 약 천만건정도 데이터가 생성이 되는 사이트)

환경정보

• CentOS 7.6
• MariaDB 10.3.23

 

MariaDB 공식사이트 - https://mariadb.com/kb/en/set-transaction/

SET TRANSACTION

 

트랜잭션 설정관련하여 설명이 잘되어 있는 블로그  - https://jyeonth.tistory.com/32

MySQL Transaction Isolation level: REPEATABLE_READ Mode에서의 Lock 이해