Bug 61259

Summary: JDBC Request : since JMeter 3.0, when JDBC auto-commit is false, a rollback statement happens each time a Request is executed
Product: JMeter - Now in Github Reporter: Liu XP <liu_xp2003>
Component: MainAssignee: JMeter issues mailing list <issues>
Status: RESOLVED FIXED    
Severity: regression CC: p.mouawad
Priority: P2    
Version: 3.0   
Target Milestone: ---   
Hardware: All   
OS: All   

Description Liu XP 2017-07-07 06:58:11 UTC
When I want to test JDBC with transaction and disable auto commit in JDBC Connection Configuration panel, Each JDBC sampler will be rollback.
This behavior is different with JMeter 2.13+excalibur package and the multiple JDBC sampler transaction couldn't be tested on latest JMeter version.

JDBC Test Sampler
1. select * from testplan where CreatorId = 10001;
2. select count(1) from testplan;
3. commit

JMeter 2.13+excalibur
Wireshark.info from JDBC client side:
Statement [truncated]: /* mysql-connector-java-5.1.40 ( Revision: 402933ef52cad9aa82624e80acbea46e3a701ce6 ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@charac
Statement: SET NAMES utf8
Statement: SET character_set_results = NULL
Statement: SET autocommit=0
Statement: select * from testplan where CreatorId = 10001;
Statement: select count(1) from testplan;
Statement: commit
Statement: rollback
Command: Quit (1)


JMeter 3.2+commons-dbcp2
Wireshark.info from JDBC client side:
Statement [truncated]: /* mysql-connector-java-5.1.40 ( Revision: 402933ef52cad9aa82624e80acbea46e3a701ce6 ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@charac
Statement: SET NAMES utf8
Statement: SET character_set_results = NULL
Statement: SET autocommit=0
Statement: select * from testplan where CreatorId = 10001;
Statement: rollback
Statement: SET autocommit=1
Statement: SET autocommit=0
Statement: select count(1) from testplan;
Statement: rollback
Statement: SET autocommit=1
Statement: SET autocommit=0
Statement: commit
Statement: rollback
Statement: SET autocommit=1
Command: Quit (1)
Comment 1 Philippe Mouawad 2017-07-16 14:49:13 UTC
Author: pmouawad
Date: Sun Jul 16 14:48:48 2017
New Revision: 1802065

URL: http://svn.apache.org/viewvc?rev=1802065&view=rev
Log:
Bug 61259 - JDBC Request : since JMeter 3.0, when JDBC auto-commit is false, a rollback statement happens each time a Request is executed
Bugzilla Id: 61259

Modified:
    jmeter/trunk/src/protocol/jdbc/org/apache/jmeter/protocol/jdbc/config/DataSourceElement.java
    jmeter/trunk/xdocs/changes.xml
Comment 2 Liu XP 2017-07-21 07:27:59 UTC
To make different JDBC Sampler committed in one transaction, we also need the following code after fixed line position.
dataSource.setEnableAutoCommitOnReturn(false);
Comment 3 Philippe Mouawad 2017-07-21 07:41:25 UTC
(In reply to Liu XP from comment #2)
> To make different JDBC Sampler committed in one transaction, we also need
> the following code after fixed line position.
> dataSource.setEnableAutoCommitOnReturn(false);

I don't think this is not needed , did you try setting Auto Commit to false in JDBC Connection Configuration ?

Thanks
Comment 4 Liu XP 2017-07-21 09:22:50 UTC
Yes. I have verified it and it need to be fixed with the additional setting.
dataSource.setEnableAutoCommitOnReturn(false);

You can try to test with following three JDBC Sampler with current fix soluction.

1. Prepare some records into AAAA table and truncate table BBBB.
2. Do following SQL with 3 JDBC Sampler in one thread.
insert into BBBB (select *  from AAAA)
select Name from BBBB
Query Type with Rollback

3. And then check the BBBB table at last.
If transaction worked normally, the BBBB table should be empty.But,you will find the table BBBB is not empty.
Comment 5 Philippe Mouawad 2017-07-22 12:56:27 UTC
Author: pmouawad
Date: Sat Jul 22 12:55:58 2017
New Revision: 1802688

URL: http://svn.apache.org/viewvc?rev=1802688&view=rev
Log:
Bug 61259 - JDBC Request : since JMeter 3.0, when JDBC auto-commit is false, a rollback statement happens each time a Request is executed
Bugzilla Id: 61259

Modified:
    jmeter/trunk/src/protocol/jdbc/org/apache/jmeter/protocol/jdbc/config/DataSourceElement.java
    jmeter/trunk/xdocs/changes.xml
Comment 6 The ASF infrastructure team 2022-09-24 20:38:09 UTC
This issue has been migrated to GitHub: https://github.com/apache/jmeter/issues/4420