RazorSQL

Query, Edit, Browse, and Manage Databases

SQL Commit / Rollback

When a connection is setup with the auto-commit mode set to off or "smart commit",
the SQL Commit and Rollback options become available.

Use the commit option to commit a transaction or statement of work
to the database. Use the rollback option to rollback any work
since the last commit.

If auto-commit is set to on, a commit will be executed after each SQL statement.
If auto-commit is set to on, rollback becomes disabled as their is no way
to rollback transactions once committed.

Please note that some databases do not support transactions, such as older versions of
MySQL. In these cases, the commit and rollback options become meaningless.
Also, some databases handle transactions differently than
others. For example, PostgreSQL handles transactions differently
than the other major databases such as Oracle or SQL Server. RazorSQL
attempts to handle this by issuing SavePoints when using PostgreSQL
in auto-commit off mode so that transactions do not get into
an invalid state.

If you are not doing any work that would update the database, auto-commit should
be set to on or to the "smart commit" mode.
This is because certain databases may lock rows or tables even
on select queries. This can cause performance problems for other users of the database.
Another reason is some databases have limitations on the sizes of their transaction logs.
These logs would grow more quickly if auto-commit is set to off.

When commit is off, RazorSQL also offers some more options pertaining to the
type of Transaction Isolation. These options are Read Uncommitted, Read Committed,
Repeatable Read, and Serializable. These options are avaialble on the Connection
Profiles window prior to connecting to a database.

The smart commit mode behaves as if auto commit is turned on until a non-SELECT SQL statement
is executed, for example, update, insert, delete, etc. Once a non-SELECT statement is executed,
RazorSQL will switch over to auto commit off mode. Smart commit can be used to reduce locking
while still getting the safety of being able to rollback transactions.