SQL: Transactions in Sybase

Transaction is one or more operations on data that need to be saved in the database all or none of them. Transactions can be introduced in Sybase in two ways:

• chained - non default, compliant with ANSI standard. The specific of that mode is, that transaction is started automatically when the first statement modifying the data is started. To finish the transaction COMMIT or ROLLBACK must be issued. This model is default in Oracle database.
• unchained - default, non ANSI standard. Each command modifying the data is a complete transaction. That transaction is started automatically and also automatically committed when the data modification finishes. To have more commands in one transaction an explicit BEGIN TRANSACTION must be issued. To finish the transaction COMMIT or ROLLBACK must be executed. This model is default in TSQL languages (Sybase or Microsoft SQL).

As you see, there are some commands to service transactions:

• BEGIN TRANSACTION - starts the transaction
• COMMIT TRANSACTION - saves the transaction on disk in the database
• ROLLBACK TRANSACTION - invalidates the transaction, so after it is executed all data remains like before the transaction
• SAVE TRANSACTION - creates a savepoint internally in the transaction. Under some conditions in the transaction, the state of database can be reverted to that point.

Transactions can be nested, however only the COMMIT of the most outer transaction is commiting the changes to the database. If the most outer transaction would be rolled back, then the internal commits also will be rolled back. When a programmer in the code needs to verify if a transaction has already been opened, than he can check the value of a variable @@TRANCOUNT. For example the value equal 3 means that we are on level 3 of nested transactions.

Let's take look  at this example:

USE tempdb
GO

CREATE TABLE test_a
(id INT);
GO

SELECT @@Trancount
BEGIN TRANSACTION
     SELECT @@Trancount
     INSERT test_a values(1)
     SELECT * FROM test_a
     SAVE TRANSACTION a1
     SELECT @@Trancount
     INSERT test_a values(2)
     SELECT * FROM test_a
     ROLLBACK TRANSACTION a1
     SELECT * FROM test_a
COMMIT TRANSACTION
SELECT * FROM test_a
GO

• Lines 1-6: In the tempdb we create a table test_a:
• Line 8: At the first moment @@Trancount returns 0
• Line 10: After BEGIN TRANSACTION @@Trancount returns 1. As any new transaction is opened here the value @Trancount remains the same until the COMMIT
• Line 12: SELECT * FROM test_a returns one record
• Line 13: State of the transaction is saved under the name "a1"
• Line 16: SELECT * FROM test_a returns two records
• Line 17: Internal rollback comes back to state with only one row in the test_a table
• Line 18: SELECT * FROM test_a returns again only one record
• Line 19: COMMIT is issued
• Line 20: When the transaction finishes only one record is present in the table

Example from mobilo24.eu