MyBatis Update database deadlock Get database connection pool wait _java

Recently learn to test MyBatis, a single deletion and change check are no problem, the last use MVN test when found a few problems:

1.update failed because of database deadlock

2.select waiting because the connection connection pool was used up and needed to wait

Get

1. To dare to explore, perseverance is victory. Just see the error when the direct Meng, because the error can not see out, belong to the frame internal error, in hesitation is not directly sleep

Feel, after all, it's almost 12 o'clock. Finally give me a little bit to find the problem.

2. Ditto, dare to go deep into the code you do not understand, dare to study the code that does not understand.

3. Distance from a qualified farmer, because more and more learn more and more loopholes, their own code everywhere is a pit. So be sure to write it down.

These two issues are documented below.

1.mysql Database Deadlock

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

Database deadlock is a recurring problem for transactional databases such as SQL Server, MySQL, and so on. Unless the database deadlock problem occurs frequently, causing the user to not operate, the database deadlock problem is generally not serious. You can do try-catch in your application. So how does data deadlock occur?

InnoDB Implements row locks (row level lock), divided into shared locks (S) and mutexes (X).

• Shared locks are used for transaction read row.
• Mutual-exclusion locks are used for transaction update or delete row.

When client a holds shared lock s and requests mutual exclusion lock X, client B holds the mutex X and requests shared lock S. In the above case, a database deadlock will occur. If it's not clear enough, take a look at the example below.

Double Open 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 = LOCK in SHARE MODE;
+----+-------+-----------+
| id | name | author_id
| +----+-------+-----------+ | | testa |
+----+-------+-----------+
1 row in Set (0.00 sec)

Client B:

Open transaction, attempt to delete id=12:

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

The delete operation requires a mutex (x), but the mutex X and shared lock s are incompatible. So the delete transaction is placed in the lock request queue and Client B is blocked.

At this point client a also wants to delete 12:

mysql> DELETE from blog WHERE id =;
Query OK, 1 row Affected (0.00 sec)

Unlike the reference article, the deletion was successful, but client B made a mistake:

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

So I tried to delete 13, which is blocked:

In my MyBatis test code, a commit is OK because the previous test did not commit a deadlock. Here, I want to say, the database is all returned to the teacher, about the lock and the business needs to be reviewed.

Number of database connections DataSource in 2.Mybatis

When I mvn test, I found that there was a query for the 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 milliseconds for connection.

So, sure enough to wait for some time before the implementation of success. Tracking the source, find this log will understand. First, 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}"/> </ Datasource> </environment> When the number of connections to the database connection pool runs out, wait 2s to get: while (conn = = null) {synchronized (state) {if (!state.i Dleconnections.isempty ()) {//Pool has available connection conn = State.idleConnections.remove (0); if (log.isdebugenabl Ed ()) {Log.debug ("Checked out Connection" + conn.getrealhashcode () + "from Pool.");}
else {//Pool does not have available connection if (State.activeConnections.size () < poolmaximumactiveconnections) {
Can Create new Connection conn = new Pooledconnection (Datasource.getconnection (), this); if (log.isdebugenabled ()) {Log.debug ("Created connection" + conn.getrealhashcode () + ".");} else {//cannotCreate New connection Pooledconnection oldestactiveconnection = state.activeConnections.get (0);
Long longestcheckouttime = Oldestactiveconnection.getcheckouttime ();
if (Longestcheckouttime > Poolmaximumcheckouttime) {//Can claim overdue connection
state.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 wait try {if (!countedwait) {state.hadtowaitcount++; countedwait = true;} IF (log.isdebugenabled ()) {Log.debug ("waiting as long as" + pooltimetowait + "milliseconds 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 (). Ro
Llback ();
} 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 () +") is returned from the PO
OL, getting another connection. ");}
state.badconnectioncount++;
localbadconnectioncount++;
conn = null; if (localbadconnectioncOunt > (poolmaximumidleconnections + 3)) {if (log.isdebugenabled ()) {Log.debug ("pooleddatasource:could not get a goo
D connection to the database. ");}
throw new SQLException ("Pooleddatasource:could not get a good connection to the database"); }
}
}
}
}

When the number of connections is less than 10 back to create, more than 10 will wait, or the error.

The above is a small set to introduce the MyBatis update database deadlock to get database connection pool waiting, hope to help everyone, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!