JDBC - c3p0 - hibernate - setAutocommit, Can someone please help me clarify the story

The fundamental source of my questionning came from this observation. When I use Hibernate and make any query, I get the following in the MySQL logs:

SET autocommit=0
insert into SimpleNamedEntity (name, version) values (null, 0)
commit
SET autocommit=1

Now, I did some research (refs below, would have added more, but I'm not 'good' enough it seems :-)) and this seems to be a fairly classic problem. I did a number of tests on various levels of architecture (mysql config, jdbc, connection pool, hibernate) to get a better idea of how things work, and I ended up more confused as a result,开发者_如何学C so here are a few questions:

• Can someone confirm if autocommit does or doesn't have a negative impact on performance? The main reason I see for this is defensive behavior to prevent unwanted uncommitted transactions that would happen if people forgot to commit after statement execution. Is there another reason?

• The way I see it, it seems to me that you will want autocommit to off when doing large dependant inserts-updates-deletes as this helps ensure data integrity. If all I want to do are selects, then it's useless and I'd probably want to set it to true to avoid some long-transaction lock-type side-effects. Does that make sense?

• I noticed that whatever the mysql autocommit setting, the jdbc connection is always to true and the only way for me to control that (have it false by default) is to add init-connect="SET AUTOCOMMIT=0" on the mysql server'. But, the strange part is once I start using c3p0 with that setting, c3p0 decides to manually override that and forces the set autocommit to 1. There's even a rollback in this case I find really suspect. Why does c3p0 force-set the flag? Why does it rollback when it doesn't normally? Is there a way to tell c3p0 not to do this?

  19 Query    SHOW COLLATION
  19 Query    SELECT @@session.autocommit
  19 Query    SET NAMES utf8mb4
  19 Query    SET character_set_results = NULL
  19 Query    SET sql_mode='STRICT_TRANS_TABLES'
  19 Query    SELECT @@session.tx_isolation
  19 Query    rollback
  19 Query    SET autocommit=1
  

• I was trying to 'clean up' the autocommit statements I was having in the example above. The closest I got was by setting autocommit to off on the mysql server. But even then, hibernate feels compelled to at a minimum do one set autocommit = 0 statement at the beginning of a transaction. Why would it need to do that? To 'reset' transaction state? Is there a configuration path I can use somewhere in the stack to prevent hibernate from doing this?

References: Java JDBC ref Article on the problem

(edit - clarification)

I am re-reading my question and can understand how complicated it all sounds. I'm sorry I wasn't clear, I'll try to clarify. I want transactions. I get the InnoDB part, READ_COMMITED. I also understand autocommit is essentially a transaction per sql statement. Here's what I don't get:

1. hibernate does 2 calls to the DB it doesn't need to do in my first example. I don't need it to toggle between autocommit = false and autocommit = true. I expect it to stay at false all the time but however I configure it, it still wants to make set autocommit calls automatically. I don't get why it does that.

2. c3p0 exhibits somewhat similar behavior. It does set autocommit automagically for me when I don't want it to, and I also don't understand why it does that.

You need to decide if you are doing transactions or not.

It is important to understand how SQL servers work, there is no such thing as no transactions. What autoCommit=true does it make each statement a transaction in its own right, you don't need to use an explicit BEGIN TRANSACTION and COMMIT around each statement (the SQL server implies this behavior).

Yes... it is important that you do SELECTs inside transaction. That fact you suggest it is not needed indicates you do not yet fully appreciate and understand the point of relational integrity.

Hibernate expects relational integrity to exist, if you read some data now, then runs some Java code, then use the data you previously read to access more data from SQL (for example using Hibernates lazy loading feature) then it is important that data still exists isn't it. Otherwise what should your Java code do now? throw the user an error?

This is what using transactions and say the default READ_COMMITTED Transaction Isolation level would provide and this is the minimum level you should start your quest to grok (learn) SQL.

So are you doing transactions or not. The question is do you want consistent, reliable, repeatible behavior or do you fast but sometimes crazy ? (this is not to say transactions slow down performance, that is a common misconception, only transactions that have conflicts with each other might lower performance, working on large data sets in an open transaction increase the possibility to lower performance).

Re autoCommit=true|false affecting performance. I think you are asking the wrong question.

Re databases locking up due to long transactions, this is usually a code/code-design bug, sometimes it is a design issue about how to do about doing things in SQL. Many small data-set transactions is good, one big large data-set is bad.

.

In order to get transactional support in MySQL you should ensure all your tables are InnoDB type using "CREATE TABLE tableName ( ... ) ENGINE=InnoDB;".

You should keep the SQL driver, pooled connection handle (via c3p0) with autoCommit=false (this should be the default).

You should ensure the default Transaction Isolation Level is (at least) READ_COMMITTED.

Don't forget to use the correct Hibernate dialect for InnoDB (check the hibernate startup logging output).

Now learn how to use that. If you hit a problem it is because you are doing something wrong and don't fully understand it all. If you feel you need to lower the isolation level, stop using transactions or need autoCommit=true then you should seriously hire a professional consulant to look at the project and should not consider turning it off until you understand why and when you can turn it off.

.

Update after your edit

The default autoCommit setting is a property of the driver and/or the connection pooler. So the suggestion to mess with hibernate setting isn't where I'd start.

By default hibernate checks what it gets and modifies it if necessary. Then puts is back to how it was before returning the Connection to the connection pooling (some broken connection poolers don't check/reset this so hibernate does this to improve its inter-operation within a larger system).

So if it is assistance with setting up the connection pooler (c3p0) then maybe you should start by posting the details of how you configured it ? The version you are using, also an example of the JDBC connection string in use and also the MySQL driver jar you are using.

Also what is your deployment scenario ? standalone J2SE ? web-app WAR ? app-server ?

Can you write some test code to get a Connection and there is API to ask it what the autoCommit mode it. http://download.oracle.com/javase/6/docs/api/java/sql/Connection.html#getAutoCommit%28%29 Your goal is to then adjust your configuration so that you always get 'false' back from this API.

The MySQL driver documentation http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html there is some reference to autoCommit in there.

You also want to try and run "mysqladmin variables" on your server to get back the server configuration, it maybe possible to set it globally (which seems like a silly thing to do IMHO).

In short you need to find the source of where it is being set on, since that is an unusual default. Then you can remove it.