Mybatis update database deadlock to obtain database connection pool waiting

Recently, I learned to test mybatis, and there was no problem with adding, deleting, modifying and checking alone. Finally, I found several problems when using mvn test:

1. Update failed because the database is deadlocked

2.select wait, because the connection connection pool is used up and needs to wait

get:

1. Be brave to explore, and persistence is victory. When I first saw the error, I was confused because I couldn't see the error at all. It was an error reported within the framework. I was hesitating whether to sleep directly

I feel it, after all, it's almost 12 o'clock. Finally, I found the problem a little bit.

2. Same as above, you must dare to dig into codes that you don’t understand and dare to study codes that you don’t understand.

3. Becoming farther and farther away from a qualified coder, because the more you learn, the more you feel the loopholes, and your code is full of pitfalls. So, be sure to record it.

The following records of these two issues.

1. MySQL database deadlock

Here, thanks to http://www.cnblogs.com/lin-xuan/p/5280614.html, I found the answer. Here, I will recreate it:

Database deadlock is a common problem encountered by transactional databases (such as SQL Server, MySql, etc.). Unless the database deadlock problem frequently occurs and the user cannot operate, the database deadlock problem is generally not serious. Just try-catch in the application. So how does data deadlock occur?

InnoDB implements row level locks, which are divided into shared locks (S) and mutex locks (X).

•Shared lock is used for transaction read line.
• Mutex is used for transaction update or delete one line.

When client A holds the shared lock S and requests the mutex X; at the same time, client B holds the mutex X and requests the shared lock S. In the above situation, a database deadlock will occur. If it is not clear enough, please see the example below.

Double opening two mysql clients

Client A:

Turn on the transaction and lock the shared lock S when id=12:

 mysql> START TRANSACTION;Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM blog WHERE id = 12 LOCK IN SHARE MODE;+----+-------+-----------+| id | name | author_id |+----+-------+-----------+| 12 | testA | 50 |+----+-------+-----------+1 row in set (0.00 sec)

Client B:

Start the transaction and try to delete id=12:

 mysql> START TRANSACTION;Query OK, 0 rows affected (0.00 sec)mysql> DELETE FROM blog WHERE id = 12;

The deletion operation requires a mutex (X), but the mutex X and the shared lock S are incompatible. Therefore, the delete transaction is placed in the lock request queue, and client B is blocked.

At this time, client A also wants to delete 12:

 mysql> DELETE FROM blog WHERE id = 12;Query OK, 1 row affected (0.00 sec)

Unlike the reference article, the deletion was successful, but client B had an error:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

So, I tried to delete 13, and it all blocked:

In my mybatis test code, because the previous test did not have commit, it caused deadlock, and it was OK after commit. Here, I want to say that the database has been returned to the teacher, and the locks and transactions need to be reviewed again.

2. Number of database connections to datasource in Mybatis

When I mvn test, I found a query test print log:

2016-07-21 23:43:53,356 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Opening JDBC Connection
2016-07-21 23:43:53,356 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Waiting as long as 20000 million seconds for connection.

So, after waiting for a while, the execution was successful. Track the source code and find this log to understand. First of all, the database connection configuration I use here is mybatis default:

 <environment id="development"><transactionManager type="JDBC"/><dataSource type="POOLED"><property name="driver" value="${jdbc.driver}"/><property name="url" value="${jdbc.url}"/><property name="username" value="${jdbc.username}"/><property name="password" value="${jdbc.password}"/><property name="password" value="${jdbc.password}"/><dataSource></environment> After the number of connections in the database connection pool is used up, you have to wait 2 seconds before getting it: while (conn == null) {synchronized (state) {if (!state.idleConnections.isEmpty()) {// Pool has available connectionconn = state.idleConnections.remove(0);if (log.isDebugEnabled()) {log.debug("Checked out connection " + conn.getRealHashCode() + " from pool.");}} else {// Pool does not have available connectionif (state.activeConnections.size() < poolMaximumActiveConnections) {// Can create new connectionconn = new PooledConnection(dataSource.getConnection(), this);if (log.isDebugEnabled()) {log.debug("Created connection " + conn.getRealHashCode() + ".");}} else {// Cannot create new connectionPooledConnection oldestActiveConnection = state.activeConnections.get(0); long longestCheckoutTime = oldestActiveConnection.getCheckoutTime();if (longestCheckoutTime > poolMaximumCheckoutTime) {// Can claim overdue connectionstate.claimedOverdueConnectionCount++;state.accumulatedCheckoutTimeOfOverdueConnections += longestCheckoutTime;state.accumulatedCheckoutTime += longestCheckoutTime;state.activeConnections.remove(oldestActiveConnection);if (!oldestActiveConnection.getRealConnection().getAutoCommit()) {try {oldestActiveConnection.getRealConnection().rollback();} catch (SQLException e) {log.debug("Bad connection. Could not roll back");} }conn = new PooledConnection(oldestActiveConnection.getRealConnection(), this);oldestActiveConnection.invalidate();if (log.isDebugEnabled()) {log.debug("Claimed overdue connection " + conn.getRealHashCode() + ".");}} else {// Must waittry {if (!countedWait) {state.hadToWaitCount++;countedWait = true;}if (log.isDebugEnabled()) {log.debug("Waiting as long as " + poolTimeToWait + " millionseconds for connection.");} long wt = System.currentTimeMillis();state.wait(poolTimeToWait);state.accumulatedWaitTime += System.currentTimeMillis() - wt;} catch (InterruptedException e) {break;}}}}}if (conn != null) {if (conn.isValid()) {if (!conn.getRealConnection().getAutoCommit()) {conn.getRealConnection().rollback();}conn.setConnectionTypeCode(assembleConnectionTypeCode(dataSource.getUrl(), username, password));conn.setCheckoutTimestamp(System.currentTimeMillis());conn.setLastUsedTimestamp(System.currentTimeMillis());state.activeConnections.add(conn);state.requestCount++;state.accumulatedRequestTime += System.currentTimeMillis() - t;} else {if (log.isDebugEnabled()) {log.debug("A bad connection (" + conn.getRealHashCode() + ") was returned from the pool, getting another connection.");}state.badConnectionCount++;localBadConnectionCount++;conn = null;if (localBadConnectionCount > (poolMaximumIdleConnections + 3)) {if (log.isDebugEnabled()) {log.debug("PooledDataSource: Could not get a good connection to the database.");}}}}}}

When the number of connections is less than 10, it will wait for more than 10 connections, otherwise an error will be reported.

The above is the Mybatis update database deadlock to obtain database connection pool waiting. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to everyone in time. Thank you very much for your support to Wulin.com website!