What happens if you don't commit a transaction

Question

Suppose I have a query:

begin tran
-- some other sql code

And then I forget to commit or roll back.

If another client tries to execute a query, what would happen?

Answer 1

You can actually try this yourself, that should help you get a feel for how this works.

Open a two windows (tabs) in management studio, each of them will have it's own connection to sql.

Now you can begin a transaction in one window, do some stuff like insert/update/delete, but not yet commit. then in the other window you can see how the database looks from outside the transaction. Depending on the isolation level, the table may be locked until the first window is committed, or you might (not) see what the other transaction has done so far, etc.

Play around with the different isolation levels and no lock hint to see how they affect the results.

Also see what happens when you throw an error in the transaction.

It's very important to understand how all this stuff works or you will be stumped by what sql does, many a time.

Have fun! GJ.

Answer 2

depends on the isolation level of the incomming transaction.

Sql transaction isolation explained

Answer 3

In addition to the potential locking problems you might cause you will also find that your transaction logs begin to grow as they can not be truncated past the minimum LSN for an active transaction and if you are using snapshot isolation your version store in tempdb will grow for similar reasons.

You can use dbcc opentran to see details of the oldest open transaction.

Answer 4

The behaviour is not defined, so you must explicit set a commit or a rollback:

http://docs.oracle.com/cd/B10500_01/java.920/a96654/basic.htm#1003303

"If auto-commit mode is disabled and you close the connection without explicitly committing or rolling back your last changes, then an implicit COMMIT operation is executed."

Hsqldb makes a rollback

con.setAutoCommit(false);
stmt.executeUpdate("insert into USER values ('" +  insertedUserId + "','Anton','Alaf')");
con.close();

result is

2011-11-14 14:20:22,519 main INFO [SqlAutoCommitExample:55] [AutoCommit enabled = false] 2011-11-14 14:20:22,546 main INFO [SqlAutoCommitExample:65] [Found 0# users in database]

Answer 5

Example for Transaction

begin tran tt

Your sql statements

if error occurred rollback tran tt else commit tran tt

As long as you have not executed commit tran tt , data will not be changed

Answer 6

Any uncomitted transaction will leave the server locked and other queries won't execute on the server. You either need to rollback the transaction or commit it. Closing out of SSMS will also terminate the transaction which will allow other queries to execute.