Description:
hi,
In loadbalance mode, inappropriate use PrepareStatement close() and Connection commit() cause memory leak, like :
```
...
conn = DriverManager.getConnection(url, "test","test");
PreparedStatement statement = conn.prepareStatement(sql) ;
statement.executeQuery();
conn.commit();
statement.close();
...
```
but put statement.close() before conn.commit() ,no memory leak will hanppen.
How to repeat:
test caseļ¼
```
import java.sql.DriverManager;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class Mysqltest {
public static void main(String[] args) throws ClassNotFoundException, InterruptedException {
int close_pos = Integer.parseInt(args[0]);
String url = "jdbc:mysql:loadbalance://127.0.0.1:8901,127.0.0.1:8902/jdbctest?" + "useSSL=false&connectTimeout=3000&useLocalSessionState=true";
final long startTm = System.currentTimeMillis();
String sql = "select * from tbl limit 1";
Connection conn= null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, "test","test");
int i1 = 0;
int a=0;
int b=0;
conn.setAutoCommit(false);
while (true) {
PreparedStatement statement = conn.prepareStatement(sql) ;
i1++;
ResultSet rs = statement.executeQuery();
if (rs !=null) {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (close_pos == 1) {
statement.close();
}
conn.commit();
if (close_pos == 2) {
statement.close();
}
if (i1 % 1000 == 999) {
double tps = i1 * 1000.0 / (System.currentTimeMillis() - startTm);
System.out.println("[" + i1 + "]tps:" + tps);
}
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
conn.setAutoCommit(true);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
if (conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
```
make:
javac Mysqltest.java
run:
java Mysqltest close_pos[1|2]
such as: java Mysqltest 1
when close_pos = 1, no memory leak
when close_pos = 2, memory leak
use jmap when close_pos=2 after a while:
jmap -histo pid
result:
```
num #instances #bytes class name
----------------------------------------------
1: 37582 3725131200 [Ljava.lang.Object;
2: 40523 169419928 [I
3: 421511 104534728 com.mysql.jdbc.JDBC42PreparedStatement
4: 421515 30349080 com.mysql.jdbc.PreparedStatement$ParseInfo
5: 442330 21246544 [B
6: 424425 13581600 java.lang.ref.WeakReference
7: 427351 10234560 [[B
8: 421914 6750624 java.lang.Object
9: 421520 6744320 java.util.concurrent.atomic.AtomicBoolean
10: 15566 1790680 [C
11: 5820 977760 com.mysql.jdbc.JDBC42ResultSet
```
use :
java -server -Xms1g -Xmx2g -XX:MetaspaceSize=256m -XX:MaxMetaspaceSize=256m -XX:-OmitStackTraceInFastThrow -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=java_heapdump.hprof -Xloggc:gc-%t.log -verbose:gc -XX:+PrintGC -XX:+PrintGCDetails -XX:+PrintGCDateStamps Mysqltest 2
result:
```
2022-02-17T11:37:43.574+0800: 692.689: [GC (Allocation Failure) [PSYoungGen: 692075K->4420K(693760K)] 1221671K->534192K(1393152K), 0.0107856 secs] [Times: user=0.18 sys=0.00, real=0.01 secs]
2022-02-17T11:37:43.911+0800: 693.027: [GC (Allocation Failure) [PSYoungGen: 692247K->4164K(693760K)] 1222020K->534097K(1393152K), 0.0135492 secs] [Times: user=0.23 sys=0.00, real=0.01 secs]
2022-02-17T11:37:44.249+0800: 693.364: [GC (Allocation Failure) [PSYoungGen: 692160K->4325K(693760K)] 1222093K->534410K(1393152K), 0.0089807 secs] [Times: user=0.16 sys=0.00, real=0.00 secs]
2022-02-17T11:37:44.590+0800: 693.705: [GC (Allocation Failure) [PSYoungGen: 692502K->4231K(693760K)] 1222586K->534451K(1393152K), 0.0155388 secs] [Times: user=0.23 sys=0.00, real=0.01 secs]
2022-02-17T11:37:44.937+0800: 694.052: [GC (Allocation Failure) [PSYoungGen: 692590K->4328K(693248K)] 1222810K->534716K(1392640K), 0.0146232 secs] [Times: user=0.21 sys=0.00, real=0.02 secs]
2022-02-17T11:37:45.281+0800: 694.397: [GC (Allocation Failure) [PSYoungGen: 692865K->4201K(693248K)] 1223253K->534829K(1392640K), 0.0123261 secs] [Times: user=0.16 sys=0.06, real=0.01 secs]
Heap
PSYoungGen total 693248K, used 265044K [0x00000000d5580000, 0x0000000100000000, 0x0000000100000000)
eden space 688640K, 37% used [0x00000000d5580000,0x00000000e543abd0,0x00000000ff600000)
from space 4608K, 91% used [0x00000000ffa80000,0x00000000ffe9a470,0x00000000fff00000)
to space 4608K, 0% used [0x00000000ff600000,0x00000000ff600000,0x00000000ffa80000)
ParOldGen total 699392K, used 530628K [0x0000000080000000, 0x00000000aab00000, 0x00000000d5580000)
object space 699392K, 75% used [0x0000000080000000,0x00000000a0631160,0x00000000aab00000)
Metaspace used 8674K, capacity 8820K, committed 9088K, reserved 1056768K
class space used 806K, capacity 861K, committed 896K, reserved 1048576K
```
Description: hi, In loadbalance mode, inappropriate use PrepareStatement close() and Connection commit() cause memory leak, like : ``` ... conn = DriverManager.getConnection(url, "test","test"); PreparedStatement statement = conn.prepareStatement(sql) ; statement.executeQuery(); conn.commit(); statement.close(); ... ``` but put statement.close() before conn.commit() ,no memory leak will hanppen. How to repeat: test caseļ¼ ``` import java.sql.DriverManager; import javax.sql.DataSource; import java.sql.Connection; import java.sql.ResultSet; import java.sql.PreparedStatement; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.Date; public class Mysqltest { public static void main(String[] args) throws ClassNotFoundException, InterruptedException { int close_pos = Integer.parseInt(args[0]); String url = "jdbc:mysql:loadbalance://127.0.0.1:8901,127.0.0.1:8902/jdbctest?" + "useSSL=false&connectTimeout=3000&useLocalSessionState=true"; final long startTm = System.currentTimeMillis(); String sql = "select * from tbl limit 1"; Connection conn= null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(url, "test","test"); int i1 = 0; int a=0; int b=0; conn.setAutoCommit(false); while (true) { PreparedStatement statement = conn.prepareStatement(sql) ; i1++; ResultSet rs = statement.executeQuery(); if (rs !=null) { try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (close_pos == 1) { statement.close(); } conn.commit(); if (close_pos == 2) { statement.close(); } if (i1 % 1000 == 999) { double tps = i1 * 1000.0 / (System.currentTimeMillis() - startTm); System.out.println("[" + i1 + "]tps:" + tps); } } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { try { conn.setAutoCommit(true); } catch (SQLException throwables) { throwables.printStackTrace(); } if (conn != null) { try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } } } ``` make: javac Mysqltest.java run: java Mysqltest close_pos[1|2] such as: java Mysqltest 1 when close_pos = 1, no memory leak when close_pos = 2, memory leak use jmap when close_pos=2 after a while: jmap -histo pid result: ``` num #instances #bytes class name ---------------------------------------------- 1: 37582 3725131200 [Ljava.lang.Object; 2: 40523 169419928 [I 3: 421511 104534728 com.mysql.jdbc.JDBC42PreparedStatement 4: 421515 30349080 com.mysql.jdbc.PreparedStatement$ParseInfo 5: 442330 21246544 [B 6: 424425 13581600 java.lang.ref.WeakReference 7: 427351 10234560 [[B 8: 421914 6750624 java.lang.Object 9: 421520 6744320 java.util.concurrent.atomic.AtomicBoolean 10: 15566 1790680 [C 11: 5820 977760 com.mysql.jdbc.JDBC42ResultSet ``` use : java -server -Xms1g -Xmx2g -XX:MetaspaceSize=256m -XX:MaxMetaspaceSize=256m -XX:-OmitStackTraceInFastThrow -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=java_heapdump.hprof -Xloggc:gc-%t.log -verbose:gc -XX:+PrintGC -XX:+PrintGCDetails -XX:+PrintGCDateStamps Mysqltest 2 result: ``` 2022-02-17T11:37:43.574+0800: 692.689: [GC (Allocation Failure) [PSYoungGen: 692075K->4420K(693760K)] 1221671K->534192K(1393152K), 0.0107856 secs] [Times: user=0.18 sys=0.00, real=0.01 secs] 2022-02-17T11:37:43.911+0800: 693.027: [GC (Allocation Failure) [PSYoungGen: 692247K->4164K(693760K)] 1222020K->534097K(1393152K), 0.0135492 secs] [Times: user=0.23 sys=0.00, real=0.01 secs] 2022-02-17T11:37:44.249+0800: 693.364: [GC (Allocation Failure) [PSYoungGen: 692160K->4325K(693760K)] 1222093K->534410K(1393152K), 0.0089807 secs] [Times: user=0.16 sys=0.00, real=0.00 secs] 2022-02-17T11:37:44.590+0800: 693.705: [GC (Allocation Failure) [PSYoungGen: 692502K->4231K(693760K)] 1222586K->534451K(1393152K), 0.0155388 secs] [Times: user=0.23 sys=0.00, real=0.01 secs] 2022-02-17T11:37:44.937+0800: 694.052: [GC (Allocation Failure) [PSYoungGen: 692590K->4328K(693248K)] 1222810K->534716K(1392640K), 0.0146232 secs] [Times: user=0.21 sys=0.00, real=0.02 secs] 2022-02-17T11:37:45.281+0800: 694.397: [GC (Allocation Failure) [PSYoungGen: 692865K->4201K(693248K)] 1223253K->534829K(1392640K), 0.0123261 secs] [Times: user=0.16 sys=0.06, real=0.01 secs] Heap PSYoungGen total 693248K, used 265044K [0x00000000d5580000, 0x0000000100000000, 0x0000000100000000) eden space 688640K, 37% used [0x00000000d5580000,0x00000000e543abd0,0x00000000ff600000) from space 4608K, 91% used [0x00000000ffa80000,0x00000000ffe9a470,0x00000000fff00000) to space 4608K, 0% used [0x00000000ff600000,0x00000000ff600000,0x00000000ffa80000) ParOldGen total 699392K, used 530628K [0x0000000080000000, 0x00000000aab00000, 0x00000000d5580000) object space 699392K, 75% used [0x0000000080000000,0x00000000a0631160,0x00000000aab00000) Metaspace used 8674K, capacity 8820K, committed 9088K, reserved 1056768K class space used 806K, capacity 861K, committed 896K, reserved 1048576K ```