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.
