WinSQL » Knowledge base

Document information

Document ID:1032
Subject:Transactions in a relational database
Creation date:9/23/09 3:02 PM
Last modified on:12/13/18 12:22 PM

Transactions in relational databases

Almost every relational database support transactions. A transaction comprises a unit of work performed within a database management system and treated in a coherent and reliable way independent of other transactions.

You can use transactions in WinSQL if the back-end database supports it. Therefore two modes of operations in WinSQL related to transactions:

  1. Auto-Commit mode (default)
  2. Manual Commit mode

Auto-Commit mode

This is the default mode in WinSQL. When you connect to any database in this mode, every DML (insert, update, delete) query gets committed automatically. One exception to this rule is when you manually start a transaction using SQL script. For example, MS SQL Server and Sybase allows writing a query similar to:

-- Starts a transaction
begin tran
-- Inserts a record in a table
insert into, lName, email, company, dateAdded)
values ('John', 'Baker', '', 'N/A', GetDate())
-- Rolls back the insert.
rollback tran

In the above example, you can rollback an update even if you are running in auto-commit mode. Please refer to the documentation for you back-end database to see if it support such queries.

If you mistakenly run an UPDATE/DELETE statement in auto-commit mode, the only way to bring your database to a previous state is to restore it from a backup.

Manual Commit mode

In manual commit mode, you must either Rollback or Commit every query manually. This is done by clicking the right mouse button in the query window and selecting the appropriate option.

Additionally, a red indicator appears in the status bar of the window reminding the user to commit or rollback.

Switching Transaction Modes

There are two ways to select a transaction mode:
  1. When you first establish connection to the database. Click here to see a screen shot.
  2. Click Toggle Commit Mode under the Query menu

Add a comment to this document

Do you have a helpful tip related to this document that you'd like to share with other users?

Important: This area is reserved for useful tips. Therefore, do not post any questions here. Instead, use our public forums to post questions.


Social Media

Powered by