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:9/23/09 3:35 PM


Transactions in relational databases


Almost every relational database these days 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
go
-- Inserts a record in a table
insert into  dbo.contact(fName, lName, email, company, dateAdded)
values ('John', 'Baker', 'John@xyz.com', 'N/A', GetDate())
go
-- 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.


IMPORTANT:
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? Please add it below. Your name and tip will appear at the end of the document text.
Your name:
Your email:
Hide my email address
Verification code:
Enter the verification code you see above more submitting your tip
Tip:Please limit tips to 1000 characters

Navigation

Social Media

Powered by 10MinutesWeb.com