Mybatis-update-Database deadlock-Get database connection pool wait

Recently study test MyBatis, single additions and deletions are no problem, and finally used MVN test when found a few problems:

1. Update failed because the database is deadlocked
2. Select waits because the connection connection pool is exhausted and needs to wait

Get

1. To dare to explore, perseverance is victory. Just see the wrong time directly confused force, because the error completely can not see out, belong to the framework internal error, in hesitation is not directly to sleep, after all, it is almost 12 o'clock. Finally, give me a little bit to find out where the problem lies.
2. Ditto, dare to go into the code that you do not understand, dare to study the code that does not understand.
3. Distance from a qualified yard is getting farther and further away, because the more learning more and more feel flawed, their 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 deadlocks are problems that are frequently encountered by transactional databases such as SQL Server, MySQL, and so on. Unless the database deadlock problem occurs frequently causing the user to be unable to operate, the database deadlock problem is generally not serious. You can do try-catch in the application. So how is data deadlock generated?

The InnoDB implements a row lock (row level lock), divided into a shared lock (S) and a mutex (X).

• A shared lock is used for a transaction read line.
• The mutex is used for the transaction update or delete row.

When customer a holds the shared lock s and requests the mutex x, and customer B holds the mutex x, and requests the shared lock S. In these cases, a database deadlock occurs. If it's not clear enough, take a look at the example below.

Dual Open two MySQL client

Client A:

Turn on the transaction and lock the shared lock s at id=12:

Mysql>STARTTRANSACTION; Query OK,0Rows Affected (0.00sec) MySQL> SELECT *  fromBlogWHEREId=  ALOCKinchSHARE MODE;+----+-------+-----------+|Id|Name|author_id|+----+-------+-----------+|  A |TestA|         - |+----+-------+-----------+1Rowinch Set(0.00Sec

Client B:

To open a transaction and try to delete id=12:

MySQL>TRANSACTION0 rows affected (0.00  sec) MySQL>  DELETEfromWHERE=n;

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

At this time client A also wants to remove 12:

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

And the reference article is different, actually deleted the success, but the client B Error:

1213 (40001when totransaction

So, I try to delete 13, which is blocked:

In my MyBatis test code, because the previous test did not commit a deadlock, the commit is OK. Here, I would like to say that the database of all the things back to the teacher, about the lock and the transaction needs to be re-revision.

Number of datasource database connections in 2.Mybatis

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

 .- -- +  at: +: -,356DEBUG[org.apache.ibatis.transaction.jdbc.JdbcTransaction] -Opening JDBC Connection .- -- +  at: +: -,356DEBUG[Org.apache.ibatis.datasource.pooled.PooledDataSource] -Waiting as Long  as 20000Milliseconds forConnection.

So, sure enough to wait for a period of time before the implementation of success. Follow the source code, find this log to understand. First of all, the database connection configuration I used here is mybatis default:

<EnvironmentID= "Development">            <TransactionManagertype= "JDBC"/>            <DataSourcetype= "Pooled">                < Propertyname= "Driver"value= "${jdbc.driver}"/>                < Propertyname= "url"value= "${jdbc.url}"/>                < Propertyname= "username"value= "${jdbc.username}"/>                < Propertyname= "Password"value= "${jdbc.password}"/>            </DataSource></Environment>

When the number of connections to the database connection pool is exhausted, wait for 2s to get 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 has available connection          if(State.activeConnections.size () <poolmaximumactiveconnections) {            //Can Create New Connectionconn =NewPooledconnection (Datasource.getconnection (), This); if(log.isdebugenabled ()) {Log.debug ("Created Connection" + conn.getrealhashcode () + "."); }          } Else {            //cannot create new connectionPooledconnection oldestactiveconnection = state.activeConnections.get (0); LongLongestcheckouttime =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 "); }} Conn=NewPooledconnection (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" + pooltimetowait + "milliseconds for connection."); }                Longwt =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 () + ") is 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."); }              Throw NewSQLException ("Pooleddatasource:could not get a good connection to the database.")); }          }        }      }    }

View Code

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

Mybatis-update-Database deadlock-Get database connection pool wait