What happens if you don't commit a transaction to a database (say, SQL Server)?

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

As long as you don't COMMIT or ROLLBACK a transaction, it's still "running" and potentially holding locks.

If your client (application or user) closes the connection to the database before committing, any still running transactions will be rolled back and terminated.

Answer 2

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.