Experiments

SQL_TCL

1 min read

SQL TCL

  • Seems like each DBMS has its own syntax for transactions
  • It is little complex to understand, SQL server has BEGIN TRANSACTION TRY/CATCH which is weird
  • Is it in any way related to procedural SQL?
  • BEGIN or START TRANSACTION
  • COMMIT
  • ROLLBACK

General flow

  1. Begin the transaction.
  2. Execute a set of data manipulations and/or queries.
  3. If no error occurs, then commit the transaction.
  4. If an error occurs, then roll back the transaction.

Here is rough syntax:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
 
-- Do your stuff here
 
COMMIT TRANSACTION

Isolation Levels

  • Serializable (highest level)
    • read and write locks (acquired on selected data) to be released at the end of the transaction
  • Repeatable reads
  • Read committed
    • keeps write locks (acquired on selected data) until the end of the transaction, but read locks are released as soon as the SELECT operation is performed
  • Read uncommitted (lowest level)
    • one transaction may see not-yet-committed changes made by other transactions
  • Syntax (maybe different b/w databases):
SET ISOLATION LEVEL <isolation-level>

Understanding isolation level, and transactions