Synametrics Technologies
Revolving around the core of technology
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:
- Auto-Commit mode (default)
- 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
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:- When you first establish connection to the database. Click here to see a screen shot.
- 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.
