1

I have a question using transactions in a JDBC connection.
I know how to use setAutoCommit(), roolback() and commit().
But now I read that it is also possible to use these commands in a sql script:

begin transaction;
create table table1 ();
create table table2 ();
commit;

What is the difference? And what happens if I set the auto-commit mode of the JDBC connection to false and then run the sql script? Does the transaction commands then only have temporary effects or are the commands ignored?

altralaser
  • 2,035
  • 5
  • 36
  • 55

2 Answers2

0

What is the difference?

Running JDBC code such as commit() or rollback() is simply a Java wrapper around raw sql (i.e. sql script). If you call commit() from a JDBC connection, ultimately the commit command gets called in SQL as a result.

And what happens if I set the auto-commit mode of the JDBC connection to false and then run the sql script?

If you set auto-commit mode of a JDBC connection to false, you are only setting auto-commit false for the scope of that particular connection. If you have a different connection and invoke an sql script, or even open another JDBC connection, the connections would have their own auto-commit modes.

Does the transaction commands then only have temporary effects or are the commands ignored?

If you are operating with auto-commit=false, then nothing permanent happens to the database until you commit the transaction. If you are operating with auto-commit=true, then a commit is essentially happening after every command.

Andy Guibert
  • 41,446
  • 8
  • 38
  • 61
0

If you want to use transactions in your application, you typically set autoCommitto FALSE and you explicitly issues COMMIT or ROLLBACK.

So the script in your question will create the two tables in one transaction, i.e. either both or none.

Two notes to your script:

Some database systems (e.g. Oracle) do implicitly open the transaction (there is no begin transaction)

Some database systems (e.g. Oracle, MySql) do implicitly COMMIT the create table statement, so the last COMMIT will have no effect and in those RDBMS it is possible to end with only one table created.

Community
  • 1
  • 1
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • First of all - thanks for your answers. And sorry - it was my misstake. I meant what happens if I set the auto-commit mode to TRUE (I have only one single JDBC connection) and then run the sql script with the transaction commands in it like above? Let's say the first create-statement is executed successfully and the second statement fails - does the transaction is now rolled back or is the first table already present because of auto-commit=true? As database type I'm using SQLite. – altralaser Mar 06 '16 at 22:10