Today I want to talk with you about a mybatis dynamic SQL caused by the production accident.

The thing is, we have an order related database service, which is responsible for order related add, delete, change and check. This service has been running for a long time without any problems.

Until one day at noon, just want to lie down for a rest, suddenly received a system alarm, a large number of orders to create failure. The order service can be said to be the core service, this service is not available, the whole process will be blocked, the transaction will fail.

Immediately lost sleep, immediately get up and board the production operation and maintenance machine, check the order service system log.

Caused by: java.util.concurrent.RejectedExecutionException: Thread pool is EXHAUSTED! Thread Name: DubboServerHandler-xxip, Pool Size: 200 (active: 200, core: 200, max: 200, largest: 200), Task: 165633 (completed: 165433), Executor status:(isShutdown:false, isTerminated:false, isTerminating:false), in 1! at com.alibaba.dubbo.common.threadpool.support.AbortPolicyWithReport.rejectedExecution(AbortPolicyWithReport.java:53) at  java.util.concurrent.ThreadPoolExecutor.reject(ThreadPoolExecutor.java:768) at java.util.concurrent.ThreadPoolExecutor.execute(ThreadPoolExecutor.java:656) at com.alibaba.dubbo.remoting.transport.dispatcher.all.AllChannelHandler.caught(AllChannelHandler.java:65)Copy the code

As shown above, the log prints a large number of Dubbo thread pool threads running out, directly denying service calls to the log. Board another machine, boy, in addition to the above log, look carefully also happened “OOM”!!

Dump the log, then analyze the generated log, find the largest category of memory usage, and then analyze and locate specific code blocks.

With the combination of system logs and dump logs, we can quickly locate the code where the problem occurred. The sample code is as follows:

Order order=new Order(); Log.info (" Order query parameters :{}",order); List<Order> orderList = ordermapper.query (Order); / /.. Other query logicCopy the code

Query the underlying use of Mybatis dynamic SQL function, such as the following:

ParameterType ="order" resultMap="orderResultMap"> Select orderId, AMT,orderInfo from order <where> <if test="orderId ! = null"> orderId = #{orderId} </if> <if test="amt ! = null"> AND amt = #{amt} </if> ..... </where> </select>Copy the code

The above code is very simple, because the incoming mybatis query statement parameters are not set, resulting in the generated SQL missing query conditions, query the full table.

Due to the large amount of data in the order table, the data returned by the Full table query will be continuously loaded into the application memory, resulting in “Full GC”, resulting in a long “stop the world” of the application.

Because the Dubbo thread is also suspended, a normal call received cannot return a result in time, causing a service consumer timeout.

On the other hand, as the application continues to receive requests and a large number of Dubbo threads are unable to process the calls in a timely manner, thread resources in the Dubbo thread pool are exhausted and subsequent requests are rejected outright.

Finally, the system app memory can’t load any more data, so it throws the “OOM” exception above.



This picture really reflects the change of mentality of little black brother at that time

The root cause of the problem is found, so why before the query is ok, but this time suddenly no value?

The original is because the front-end page changes, resulting in the incoming query parameters are empty!!

The front page can not display the query order, the user will generally choose to retry, and then did not pass in the query parameters, again aggravating the application situation, worse.

Thinking more broadly about the above problem, we can still solve the problem temporarily by restarting the application. Imagine if dynamic SQL were used in other scenarios.

If the condition is lost, the balance of all users will change. If it’s a bigger balance, it’s probably fine. But if the balance is getting smaller, it really could turn into a social accident

Let’s also assume that if some configuration tables use dynamic SQL physical delete data, then if the condition is lost, the entire table data will be deleted. If the data is gone, there’s nothing to talk about. Run away

As you can see, updating/deleting dynamic SQL, if conditions are lost, can cause a lot of damage, and the business can be shut down.

Solutions Are there any solutions to these problems?

“Very simple, don’t use dynamic SQL, write it by hand.”

Emm! Put the knife down. I’m just kidding

While it is true that the above problems are caused by dynamic SQL, the root cause I think is caused by improper use.

We certainly can’t give up eating for choking, abandon martial arts, and return to the “slash-and-burn” era, handwritten SQL.

So, without further ado, there are two ways to address potential problems with dynamic SQL:

First, change the ideology and educate all developers about the problems that dynamic SQL can cause.

It is only when we are aware of the problems that dynamic SQL can cause that we are likely to think during the development process about whether writing this will cause problems.

“That, I think, is really important.”

Second, controllable query conditions should be provided for actual business scenarios, and necessary parameter verification must be done for external interfaces.

We need to analyze the external conditions from the actual business scenarios. In principle, the master database table must query a single key according to the primary key or unique key, or use the relevant foreign key to query multiple. For example, primary key queries such as order table queries for payment order numbers.

In addition, for these query conditions, the interface layer must do the necessary parameter verification. If the parameters are not passed, call back directly to nip them in the bud.

If there is really a need to query multiple data background requirements, this kind of query does not need high real-time, then we can actually be separated from the above application query, and query using slave library.

Third, add some tool-like prevention plug-ins.

For example, we can add a plugin in Mybatis to check whether the SQL is executed with the WHERE keyword, if there is no direct interception.

Mybatis interceptors are as follows:

@Intercepts({ @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}), @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}), @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})}) @Slf4j public class CheckWhereInterceptor implements Interceptor { private static final String WHERE = "WHERE"; @override public Object Intercept (Invocation) throws Throwable {// Get the 0th argument of the method, that is, MappedStatement. MappedStatement MappedStatement = (MappedStatement) Invocation. GetArgs ()[0]; / / get the SQL command operation type SqlCommandType SqlCommandType = mappedStatement. GetSqlCommandType (); final Object[] queryArgs = invocation.getArgs(); final Object parameter = queryArgs[1]; BoundSql boundSql = mappedStatement.getBoundSql(parameter); String sql = boundSql.getSql(); if (Objects.equals(SqlCommandType.DELETE, sqlCommandType) || Objects.equals(SqlCommandType.UPDATE, SqlCommandType) | | Objects. Equals (sqlCommandType. SELECT sqlCommandType)) {/ / formatting SQL SQL. SQL = replace (" \ n ", ""); if (! StringUtils.containsIgnoreCase(sql, WHERE)) { sql = sql.replace(" ", ""); Log.warn ("SQL statement without WHERE condition, forbid execution, SQL is :{}", SQL); log.warn("SQL statement without WHERE condition, forbid execution, SQL is :{}", SQL); Throw new Exception("SQL statement with no WHERE condition "); } } Object result = invocation.proceed(); return result; } @Override public Object plugin(Object target) { return Plugin.wrap(target, this); } @Override public void setProperties(Properties properties) { } }Copy the code

The above code is actually rather rough, you can add corresponding precautions according to their own business.

Summary Today’s article, starting with a real example, leads to the potential problems of dynamic SQL, mainly to make you aware of the problems in this area. To be more careful with dynamic SQL in the future.