“This article has participated in the good article call order activity, click to see: back end, big front end double track submission, 20,000 yuan prize pool for you to challenge!”
A CSDN blog post that caught my attention was widely reposted about the reasons for using ThreadLocal for database connection pooling, and the conclusion is shown in the figure below.
This is an article from CSDN, which has been reprinted by many numbers
Leaving aside what threadLocal does and how it works, the idea of database connection pooling suddenly makes sense. Take a closer look, how does not feel quite right, classmate, this is what tiger word.
Practice is the sole criterion for testing truth
It is understood that the ability to obtain connections provided by the connection pool needs to be unique to the “task”, that is, only after a certain thread has completed the data operation and put the connection back into the connection pool, other threads can obtain and use the connection again. We’ll explain why later, but let’s test it for ourselves.
Select a DruID and set only one connection in the connection pool to facilitate the verification of multiple threads for the same connection scenario.
First, pass the shared datasource resource to the thread and use datasource.getConnection() to get the connection:
Note: Runnable intentionally does not execute connection.close
The result is as shown in the figure above: only one thread can execute properly, and because it is not closed, the other threads fail to obtain the connection. Note: The database connection pool function is “exclusive” to a thread task.
Take a step back
Given that multiple threads have access to the same connection using an incomplete connection pool, as described in the beginning of the article, can threadLocal really do the same thing?
// The validation process is as follows: HTTP: / / https://blog.csdn.net/sunbo94/article/details/79409298 / / Connection setting the autoCommit = false private static final ThreadLocal<Connection> connectionThreadLocal=new ThreadLocal<>(); Private static class InnerRunner implements Runnable{@override public void run() {private static class InnerRunner implements Runnable{@override public void run() { String insertSql="insert into user(id,name) value("+RunnerIndex+","+RunnerIndex+")"; statement=connectionThreadLocal.get().createStatement(); statement.executeUpdate(insertSql); System.out.println(RunnerIndex+" is running"); If (RunnerIndex==3){// let Thread. Sleep (100); / / take connection object from the threadlocal connectionThreadLocal. The get (). The rollback (); System.out.println("3 rollback"); } else {/ / take connection object from the threadlocal connectionThreadLocal. The get (), commit (); System.out.println(RunnerIndex +" commit"); }}}Copy the codeHere are the results:
As long as the statement.executeUpdate statement runs before thread 3 and the transaction rollback statement executes after a commit, the problem is that the data to be rolled back is committed.
As shown in the figure below, the insert result from 3 is indeed not rolled back, but appears in the table instead:
Therefore, for the knowledge, we can not blindly accept, the suggestion holds some skeptical attitude, is still necessary.
So why doesn’t threadLocal work for the same database connection?
What is Connection?
A connection can be thought of as a session between the server and the database, and Statemant is used to execute SQL and return results in the context of the session. A connection can contain multiple statements; In between, however, is the concept of a transaction (Translation), which ensures that all or none of its internal statements are executed. If autoCommit is enabled, the default is one statement, one transaction.
At a simpler level, a connection is a shared resource. At a simpler level, it is a shared variable whose address is the only one in memory after the connection pool has created it.
Can ThreadLocal hold shared variables?
This is certainly possible, but it is not recommended because setting a Connection to a ThreadLocalMap is actually storing an address reference to an in-memory object, and when used, only that object will work.
The most common feature of ThreadLocal is that it provides methods to save and retrieve objects in order to avoid passing through layers.
There’s a technique I used in high school math called proving hard is the other way around, and it applies here as well. On the other hand, if the isolation of a connection can be achieved by using a copy of a ThreadLocal, why not just one connection? In real time, database connections are often insufficient, and the conclusion is obvious
Then again, what does a threadLocal need to do to achieve database connection isolation?
If you had to use a ThreadLocal to implement connection isolation, you would have to create a new connection for each thread and save it in a ThreadLocal, so that each thread would only use that connection for the duration of its lifetime.
$Druid, zadl and other database connection pool is how to manage the connection work?
Druid connection pooling with a maximum number of connections:
-
Druid maintains an array to hold connections
-
Multiple variables are maintained to check the state of the connection pool, where poolingCount is used to indicate the number of connections in the pool
-
When there is a thread to acquire the connection, need to first lock, to subtract one operation.
-
Null is returned when the number of connections is found to be 0 when the connection is obtained
-
When the connection closes, the connection resource is put back into the array and the number is incremented.
* The above is only a minimalist description of the process of druid connection pool. In fact, there are complex judgment of connection pool empty wait, full notification, active number, exception number, etc. * Interested students can see the source code.
Zdal connection pool management source list:
Public class InternalManagedConnectionPool {/ / maximum number of connections private final int maxSize; Private final ArrayList connectionListeners; Private final InternalSemaphore permits; Private volatile int maxUsedConnections = 0; protected InternalManagedConnectionPool(...) ConnectionListeners = New ArrayList(this.maxSize); // Listeners = new ArrayList(this.maxSize); permits = new InternalSemaphore(this.maxSize); }Copy the codeGetConnection () method:
/ / to get connected public ConnectionListener getConnection () {/ / semaphore attempts to acquire permission if (permits. TryAcquire (poolParams blockingTimeout, TimeUnit.MILLISECONDS)) { ConnectionListener cl = null; Do {// Synchronized (connectionListeners) {if (connectionListeners. Size () > 0) {// Fetch the last cl = (ConnectionListener) connectionListeners.remove(connectionListeners.size() - 1); Int size = (maxSize - permit.availablepermits ()); if (size > maxUsedConnections){ maxUsedConnections = size; } } } if (cl ! = null) { return cl; } }while(connectionListeners.size() > 0); //OK, no working connection is found in the connection pool. Create a new createNewConnection(){... }}else{if (this.maxsize == this.maxusedConnections) {throw new ResourceException(" The data source has the maximum number of connections, And no new connections within the range timeout release, poolName = "+ +" poolName blocking timeout = "+ poolParams. BlockingTimeout +" (ms) "); }}Copy the codeHere we simplify the key attributes of the internal connection pool management class and the connection acquisition method traffic. The connection return will not be made. It is much the same
-
MaxUsedConnections, identified as volatile, is used to complete inter-thread visibility
-
Semaphone, part of the AQS series, controls concurrent access to shared resources.
These are some common eight-part essays, but together they can be great ~
Where does threadLocal function in Druid and Zdal?
As we know, such as Druid, ZDAL and other excellent middleware, is not only a database connection pool this role, Ali database middleware zDAL source code parsing is also mentioned in the article.
So what role can ThreadLocal play here?
Take ZDAL as an example. Ali’s databases are basically very large, but there is a complete set of database table splitting specifications. Therefore, the design and storage of database keys, sub-table keys, primary keys, and virtual table names need to follow the specifications, and the parsing operations in ZDAL also need to match them.
The analytical work is relatively complicated and onerous, however, according to the operation of the same user, usually library table routing is relatively fixed, therefore, when we parsed SQL, through the fields and configuration rules, to calculate the routing table, so, can be directly put into the thread context, for the subsequent database operations of this request.
public Object parse(...) { SimpleCondition simpleCondition = new SimpleCondition(); simpleCondition.setVirtualTableName("user"); simpleCondition.put("age", 10); ThreadLocalMap.put(ThreadLocalString.ROUTE_CONDITION, simpleCondition); } public void (){RouteCondition rc = (RouteCondition) threadLocalMap.get (threadLocalString.route_condition); if (rc ! = null) {/ / not parse SQL, by the specified object (RouteCondition) of the incoming ThreadLocal decided to library table metaData = sqlDispatcher destination. GetDBAndTables (rc); } else {/ / depots by parse SQL table try {metaData. = sqlDispatcher getDBAndTables (originalSql, parameters); } catch (ZdalCheckedExcption e) { throw new SQLException(e.getMessage()); }}}Copy the codeThis also just complements the previous correct use of ThreadLocal.
The reason is to have a question about an article narrative, through a simple verification, confirmed their idea, and then from several aspects of the database connection and threadLocal extension, if you find any problems, welcome to comment to help correct and supplement.
Recommended Reading:
1.1. Architecture optimization: Cluster deployment and load balancing 1.2. Architecture optimization: Storage degradation with message queues 1.5. Storage optimization: mysql index principles and optimization 1.6. Storage: Explain index optimization 1.7. Storage optimization: detailed sub-table 1.8. Supplement: Ali database middleware source code analysis 1.9. Storage optimization: Of all strategies, cache is king