In Hibernate 3, there's a new property:

hibernate.connection.autocommit

In my mind, it makes sense to have all connections start as auto commit = true and then change as Transactions are started. However, this is not the default behavior. In addition, the documentation states that setting this property to true is "not recommended".

Why is the case? Why does it make sense to have all connections automatically be in a transaction?

Scott

I think it's a question of "best practices". Commits should be in a transaction in most cases, especially in enterprise applications.

The choice of false for the default may be also be related to backwards-compatibility.

I agree with Scotts thinking. In fact I agree with both comments but with different interpretation.

The default idle state of an SQL connection makes more sense to me in auto-commit=on state, UNTIL you explicitly issue a "BEGIN TRANSACTION" type of command, at this point the connection temporarly drops auto-commit until the ROLLBACK or COMMIT. At which point it resumes auto-commit behaviour again. This has always made sense to me. But not how I see people implement their stuff.

The above relates specificly to what state is the underlying db connection is to be left in when its idle. I don't think any of us dispute that when its in a transaction then auto-commit=off.

I agree also with sgoldstein comments about best practices for enterprise applications, its true to maintain best data integrity make everything into a transaction and therefore it becomes atomic, it either happened or it didn't, everything knows where everything stands at all times.

But you must understand the enterprise transactions are happening at a different level, the enterprise transaction should not start until you open your transactional code path. You should be allowed to read enterprise data outside of this transactional code path which is not subject to the transaction. For that you need auto-commit=on at idle.


This is exactly the same programming design concepts as used for multi-threaded or operating system level data access paterns, the principals are the same, just the mechanisms have different names.

data column = memory location
SQL Transactions = CPU Machine Code atomic Instructions
Enterprise Transaction = Spinlocks, Mutex, Semaphone, yadda, yadda

Its true to say that SQL transactions are an atomic primitive building block upon which the more complex enterprise transactions can be built. Without atomic primitives the bigger things can't be realised.


So having every access to the database always inside a transaction is a bit like writing 'C' code that puts one big mutex around main() that program will never thread/scale. If your transaction isolation level is REPEATABLE-READ then the database server has to do a lot of works staving off all the other updates until you decide you want to commit. It just make everything heavy work, when most things don't need it.

Enterprise Best Practice is not very different from regular computer best practice:

* Receive data and canonicalize it
* Validate data, operation
* Check access rights for operation/data
* Dry run the operation

Okay now we think we can run the transaction, because we did not pickup any error above:

* Start our transaction
* Revalidate findings in step 2 & 3
* Run the operation for real
* Commit transaction


The point being that the "revalidate" is meant to be a lighter weight operation than what actually happened in step 2 & 3. In those steps some very serious validation and checking might have taken place, those checking procedures themselves have run several transactions to get the all clear, or at least setup objects/handles on things.

Its a bit like the US President, he doesn't photocopy and colate his paperwork, he has multiple departments each contributing their piece with someone handing him every piece of paper he needs just outside the meeting room door. So all he has to do to complete is walk into the room give it the once over and sign the form.


The actual transaction part of our operation should be kept open no longer than necessary, just like you would in a mutex.

This is just nonsense.

Of course you do not want to be forcing explicit "begin tran" calls (ie. an extra request to the database) in the overwhelmingly common case where you want to access data without autocommit enabled.

(I guess the problem is that people simply don't understand connection pools.)

This is of course utterly wrong. I'm not even going to start on explaining how wrong it is.

Transactions are demarcated by client and all reads/writes are transactional in both cases, "auto-commit=on" sends "COMMIT;BEGIN TRANSACTION" per SQL statement and it doe's not solve any problems (It is just a wrong way to demarcate transactions). Rollback "idle" transaction to start "enterprise transaction" in both cases.

I think I understand. The connection pool maybe shared by non-hibnernate users, therefore some agree default state needs to be decided. The auto-commit state is a point of contention, some servers default this state to auto-commit=on, but hibnernates natural way of using any connection is everything is inside an SQL transaction so to save having another server round-trip to turn it off each time before use you can use this option.

The main point of my thoughts are with this policy of everything is inside a transaction, makes no sense to me. Upon inspecting my applications I find only key areas actually need transaction support and they are clearly marked in the application. Maybe my applications are just not big enough to see benifits from everything inside a transaction?

So this makes me disagree then that the "overhemlmingly common case" is that transactions are needed. This is not what I find in my applications.

O/R persistance is your expertise and maybe because much is machine generated in order to safeguard it a slightly bigger sledgehammer is needed I grant you that. But my background is in hand crafted schemas and SQL calls with a complete overview of how all data access is going to happen on the dataset.




My understanding of auto-commit=on differs from yours. No "COMMIT;BEGIN TRANSACITON" verbs are sent as AUTO-COMMIT is a server side mode that relates to the default state of the SQL connection at idle. Its true to say each statement is executed on the server as-if they were wrapped in those verbs when auto-commit=on.


I must add my ramblings in my previous post were made late a night but the topic has always been straightforward to me I am however interested in your flames :)

You keep saying "I don't need". But you don't explain what you think the *cost* of doing stuff in a transaction is. THINK! In read committed isolation, which is the default for all databases, and the correct setting for almost all applications, there is no extra cost to doing read operations in a transaction.

Yes, but the intent is that the connection pool you use with Hibernate has autocommit *disabled*.

This default is designed for new ODBC users (JDBC does it for historical reasons too), it helps to avoid deadlocks at learning time (this is single argument for "auto-commit"). Probably it was not a good idea, I see it more confuses than help.

Thanks for the input.

I didn't realize that having auto-commit on lead to a higher performance cost. Without this knowledge, it made sense logically to have isolated reads within their own transaction. But, given the extra cost, using the proper transaction isolation level makes more sense.

Try "SET TRANSACTION READONLY", it can help to optimize read only transactions on some implementations, but auto-commit can reduce performance only (flushes buffers and caches on transaction log per statement)

With this behavior, we're seeing a problem on Postgres related to failed select statements. Essentially, a select fails and no rollback is performed, since a Transaction object is not being used (not suggested for a select). However, when the error occurs, the Connection is merely returned to the connection pool (currently using the default Connection Provider) with the auto commit still set to false. Following this, all queries fail because Postgres will not allow any further queries within the same transaction.

What is the expected behavior here? Is it the responsibiliy of the application to retrieve the underlying connection and toggle the auto commit flag?

Scott

Responsibiliy of the application is to demarcate transactions, it means you must commit or rollback *all* started transactions. You can demarcate transactions in servlet filter. Pool must rollback transaction before to return idle connection too.