| Summary: | initSQL should be committed if defaultAutoCommit == false | ||
|---|---|---|---|
| Product: | Tomcat Modules | Reporter: | Craig Servin <cservin> |
| Component: | jdbc-pool | Assignee: | Tomcat Developers Mailing List <dev> |
| Status: | NEW --- | ||
| Severity: | normal | ||
| Priority: | P2 | ||
| Version: | unspecified | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | Linux | ||
|
Description
Craig Servin
2014-06-02 20:25:50 UTC
Are you executing DML statements in your initSQL? Are there drivers who allow rollbacks on session state changes? Weird. Note that you could always use a stored procedure that contains a COMMIT. I am using postgresql and the problem originally showed up when the initSQL was executing a set search_path and a rollback was triggered by the first transaction. To test/recreate the issue I did trigger the same problem with DML( an insert into a temp table, which then is rolled back). I was able to "fix" the issue in my case by adding a ";commit" to the end of my actual initSQL, although this makes me nervous since you are supposed to use the Connections commit() method when using JDBC. That solution seamed a little bit hackish to me so I posted this ticket. Honestly, I'm surprised that "SET search_path [..]" will be affected by a rollback. I know it's no basis for comparison, but MySQL does not appear to behave this way: mysql> begin; Query OK, 0 rows affected (0.01 sec) mysql> set @foo := 'bar'; Query OK, 0 rows affected (0.00 sec) mysql> select @foo; +------+ | @foo | +------+ | bar | +------+ 1 row in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select @foo; +------+ | @foo | +------+ | bar | +------+ 1 row in set (0.00 sec) I think this should be an /option/ that defaults to false because it will execute another query (COMMIT) on the server side. If most clients do not need it, there's no need to execute a COMMIT unless the application absolutely needs it. Any objections to an option rather than strict behavior? It just seems like the connection initialization should persist for the life of the connection. If I have some DML that initializes a connection, and it is returned to the pool, when I use that connection again it should still be initialized. In this case it wasn't, and it became apparent when the wrong data was returned. Adding the ";commit" worked as a solution for me it was just interesting to track down because I expected the initSQL to be permanent for the life of the connection. Since different people will have different initSQL commiting the initSQL seems correct to me, but I have know idea what most people would expect. No objection to option, default, or leave it as is. I just wanted to let people know what I found. I agree with the filer. Alternately, turn on autocommit when running the initialization SQL, then if configured to be off, turn it back off. Thanks for the report gentlemen. Glad there is a workaround, the added ";commit". If it did do if ( !connection.getAutoCommit() ) connection.commit(); what are the implications for other users? I can't think of any issues with that unless someone was intentionally trying to lose their initSQL later on. |