jdbc request always set autocommit=1

[liwei225]

Expected behavior

fix bug

Actual behavior

jdbc request always set autocommit=1

Steps to reproduce the problem

i wanna test transaction on mysql 8.0.20 . however, after opening a transaction, the next jdbc request always sets autocommit=1

mysql general_log:

2023-10-20T08:02:11.701987Z 23 Query SET autocommit=0

after 5 sec ,i found next jdbc request[select_1] send [ set autocommit=1] before select statement

2023-10-20T08:02:16.721626Z 23 Query SET autocommit=1 2023-10-20T08:02:16.741564Z 23 Query SELECT @@session.transaction_isolation 2023-10-20T08:02:16.744943Z 23 Query SELECT @@session.transaction_isolation 2023-10-20T08:02:16.750466Z 23 Query select from t1 limit 10 2023-10-20T08:02:21.770572Z 23 Query SELECT @@session.transaction_isolation 2023-10-20T08:02:21.779301Z 23 Query SELECT @@session.transaction_isolation 2023-10-20T08:02:21.782025Z 23 Query select from t1 limit 10 2023-10-20T08:02:26.800593Z 23 Query SELECT @@session.transaction_isolation 2023-10-20T08:02:26.811092Z 23 Query SELECT @@session.transaction_isolation 2023-10-20T08:02:26.829268Z 23 Quit

JMeter Version

5.6.2

Java Version

openjdk 17.0.2 2022-01-18

OS Version

macos

[FSchumacher]

Have you tried to set autocommit to false in the JDBC Connection Configuration settings dialog above?

[liwei225]

@FSchumacher yes, this is the only way to achieve transaction testing. but it seems that jdbc request is not working as expected.

[FSchumacher]

Can you be more verbose here? What is not working and what did you do? (The first example you gave, had not set autocommit to off)

[liwei225]

@FSchumacher For example, I would like to use two groups of transaction controllers within the same test plan, with one group using manual control for commit and the other using automatic commit.

tran 1 should control by manual. tran 2 should inherit jdbc connection configuretion[autocommit = true]

jdbc request supports set autocommit, but in the test plan, it cannot affect the next jdbc request autocommit state. In other words, it seems that the query type AutoCommit(false) has no practical use anymore.

[FSchumacher]

Well, I think I know, what happens. We set the default autocommit value on the database pool. The pool initializes every connection we borrow from it with that value. When we set (default) autocommit to true and use the sampler autocommit (false), we set the autocommit flag for the borrowed connection, which immediately gets returned (with autocommit false). The next borrow operation re-sets the autocommit status to the default one. To work around this behaviour, you can use a JSR 223 Sampler which sets the default value for autocommit to null. That will keep the autocommit status on the connections through their entire lifetime. Note, that you will still have to set it for every connection the pool contains. The script for a database pool named db would look like

def dataSource = vars.getObject("db").configuredDataSource

dataSource.defaultAutoCommit = null
dataSource.autoCommitOnReturn = false

This script should be the called before any other JDBC Sampler. I believe the JDBC Connection Config element should allow for null(or empty) value for the default autocommit value.