Transactions

Web page by Kevin Harris of Homer IL

Please contact Kevin Harris of Homer IL concerning this web site

Transactions

ADO.NET supports simple and distributed transactions. However support also depends upon the database provider and the storage engine. In MySQL the most popular storage engine which supports transactions is InnoDB.


Database transactions provide data integrity when modifying data. They ensure an entire unit of work (UoW, i.e. updates to multiple tables) occurs correctly or not a all. This prevents one table making up a portion of the UoW from being updated while a second table in the UoW fails to update. Transactions should only be used when needed as they cause a performance penalty by add overhead to processing and increase the number and extent of locks (depending upon the isolation level).

Successful transaction willt pass the "ACID" test:

  • Atomic – All statements in the transaction either completed successfully or they were all rolled back.

  • Consistent – All data touched by the transaction is left in a logically consistent state.

  • Isolated – The transaction must affect data without interfering with other concurrent transactions.

  • Durable – The data changes enacted by the transaction are permanent, and will persist through a system failure.

Isolation Levels

Isolation levels determine the degree of restriction of a transaction. The more restrictive the isolation level the more the data involved in the transaction is isolated from other transaction, at the price of performance and concurrency. ADO.NET supports these isolation levels (in order from least restrictive to most restrictive:

  • Unspecified - A different isolation level than the one specified is being used, but the level cannot be determined.

  • Chaos - The pending changes from more highly isolated transactions cannot be overwritten.

  • ReadUncommitted - A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored. ReadUncomitted doesn't use any locking and dirty reads are possible.

  • ReadCommitted - Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data. ReadCommitted is often a good compromise with shared locks held while the data is being read, thereby avoiding dirty reads (reads that retrieve information from a transaction that has not yet been committed). However, the data can be changed by another user before the end of the transaction, resulting in nonrepeatable reads or phantom data. This is SQL Server’s default

  • RepeatableRead - Locks are placed on all data that is used in a query, preventing other users from updating the data. Prevents non-repeatable reads but phantom rows are still possible. RepeatableRead uses locking to prevent another user from updating or deleting the rows that are being used in the transaction, but it doesn’t guarantee that new rows won’t be inserted.

  • Serializable - A range lock is placed on the data set, preventing other users from updating or inserting rows into the dataset until the transaction is complete. Serializable places a lock on all the tables a transaction accesses, which prevents other users from updating or inserting rows while the transaction is in process. Their requests are still processed when the transaction ends, provided their commands don’t time out.


Isolation Levels
Isolation Level Dirty Read Nonrepeatable Read Phantoms
Read Uncommitted Yes Yes Yes
Read Committed No Yes Yes
Repeatable Read No No Yes
Serializable No No No


  • Dirty Read - occurs when a transaction reads data that has not yet been committed.

  • Nonrepeatable Read - occurs when a transaction reads the same row twice but gets different data each time.

  • Phantoms - is a row that matches the search criteria but is not initially seen. A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first. This is caused by Inserts and Deletes which match the selection criteria and occur between the first and second executions of the select.

ADO.NET Transaction Models

Microsoft Windows developers traditionally (ADO .NET 1.0) chose between two transactional programming models:

  1. Explicit Transaction Management - Programmer writes code to explicitly start and manage the transaction. Good for single objects interactions with a single database.

  2. Declarative Transaction Flow and Management - available through Enterprise Services attributes are used to declare classes and methods making up a transaction. Any classes derived from Serviced Component can use the transaction attribute to define a transactional context (i.e. inner most execution scope). Good for distributed transactions where more than one object and/or more than one database is involved.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
string connectionString = "...";
IDbConnection connection = new SqlConnection(connectionString);
connection.Open();
IDbCommand command = new SqlCommand();
command.Connection = connection;

IDbTransaction transaction;
transaction = connection.BeginTransaction(); //Enlisting database
command.Transaction = transaction;
try
{
   /* Interact with database here, then commit the transaction */
   transaction.Commit();
}
catch
{
   transaction.Rollback(); //Abort transaction
}
finally
{
   connection.Close();
}


Explicit Transaction

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
using System.EnterpriseServices;

[Transaction]
public class MyComponent : ServicedComponent
{
   [AutoComplete]
   public void MyMethod()
   {
      /*Interact with other serviced components
      and resource managers */
   }
}


Declarative Transaction

In .NET 2.0 a new explicit transaction model was introduced which was complemented by a new optimized transaction manager called the Lightweight Transaction Manager (LTM). The new model allowed for explicit coding of distributed transactions as well as single-source transactions. The model allows the run time promotion of transactions from single-source to distributed, if the DBMS supports transaction promotions. If a transaction involves a single database, then the LTM only monitors the transaction in case it needs to be promoted to a distributed transaction. LTM will take over the transaction, and coordinate the distributed resource update using a two-phase commit (2PC).

The new transaction model uses the TransactionScope class to start a transaction. TransactionScope handles the scope of transactions as they become more complicated, ie. nested transactions. TransactionScope involve the concept of an ambient transaction which is the transaction which is present in the thread of the current executing application. The top-most transaction scope is called the root scope. TransactionScope contains options (TransactionScopeOption) for how it should handle the relationship between the top-most transaction scope and the ambient transactions. TransactionScopeOption lets you control whether a transaction joins the ambient transaction or becomes the root scope of a new transaction.

TransactionScope Options
TransactionScopeOption Ambient Transaction Exists Transaction Scope Will Take Part In
Required No New Transaction (will be the root)
Requires New No New Transaction (will be the root)
Suppress No No Transaction
Required Yes Ambient Transaction
Requires New Yes New Transaction (will be the root)
Suppress Yes No Transaction


Important properties of TransactionScope and their default values are:

  • IsolationLevel - defaults to Serializable
  • Timeout - defaults to 1 minute
  • TransactionScopeOption - defaults to Required

When you are satisfied that all operations within the scope are completed successfully, you should call the Complete() method (only once) to inform that transaction manager that the state across all resources is consistent, and the transaction can be committed. Failing to call Complete() aborts the transaction, because the transaction manager interprets this as a system failure.


1
2
3
4
5
6
7
8
9
using (IDbTransaction tran = conn.BeginTransaction()) {
    try {
        // your code
        tran.Commit();
    }  catch {
        tran.Rollback();
        throw;
    }
}


Connection Transaction with BeginTransaction()

1
2
3
4
5
using(TransactionScope tran = new TransactionScope()) {
    MethodThatDoesSomeWork();
    MethodThatDoesSomeAdditionalWork();
    tran.Complete();
}


Ambient Transactions with TransactionScope()

Transaction Examples

Below is VB.NET code I recently used for transactions in a web application:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
        Dim objConn As New MySqlConnection(strConn)
        ' Begin Transaction - Update Unit of Work (table1, table2)
        Dim transaction As MySqlTransaction
        objConn.Open()
        transaction = objConn.BeginTransaction()
        Try
            ' (1) Update Table1
            Dim cmdTable1SQL As New MySqlCommand
            Dim strTable1SQL As String = "UPDATE table1 SET " & _
            "FieldName=?ParmName " & _
            "WHERE table1.PKey=?ParmKey;"
            cmdTable1SQL.CommandText = strTable1SQL
            cmdTable1SQL.Connection = objConn
            cmdTable1SQL.Parameters.Add(New MySqlParameter("?ParmName", Session("mySessionVar")))
            cmdTable1SQL.Parameters.AddWithValue("ParmKey", Session("mySessionPKey"))
            cmdTable1SQL.ExecuteNonQuery()

            ' (2) Update Table2
            Dim cmdTable2SQL As New MySqlCommand
            Dim strTable2SQL As String = "UPDATE table2 SET " & _
            "FieldName=?ParmName" & _
            "WHERE table2.PKey=?ParmKey;"
            cmdTable2SQL.CommandText = strTable2SQL
            cmdTable2SQL.Connection = objConn
            cmdTable2SQL.Parameters.Add(New MySqlParameter("?ParmName", Session("mySessionVar")))
            cmdTable1SQL.Parameters.AddWithValue("ParmKey", Session("mySessionPKey"))
            cmdTable2SQL.ExecuteNonQuery()

            transaction.Commit()
        Catch exception As MySqlException
            transaction.Rollback()
            Throw
        End Try
        objConn.Close()
        ' End Transaction


Explicit Transaction Example

Error | ASP.NET Developer

Error

Error message

  • Warning: Cannot modify header information - headers already sent by (output started at /srv/disk9/1218369/www/kcshadow.net/aspnet/includes/common.inc:2748) in drupal_send_headers() (line 1232 of /srv/disk9/1218369/www/kcshadow.net/aspnet/includes/bootstrap.inc).
  • PDOException: SQLSTATE[42000]: Syntax error or access violation: 1142 INSERT command denied to user '1218369_b2cf'@'185.176.40.58' for table 'watchdog': INSERT INTO {watchdog} (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9); Array ( [:db_insert_placeholder_0] => 0 [:db_insert_placeholder_1] => cron [:db_insert_placeholder_2] => Attempting to re-run cron while it is already running. [:db_insert_placeholder_3] => a:0:{} [:db_insert_placeholder_4] => 4 [:db_insert_placeholder_5] => [:db_insert_placeholder_6] => http://www.kcshadow.net/aspnet/?q=transactions [:db_insert_placeholder_7] => [:db_insert_placeholder_8] => 54.162.171.242 [:db_insert_placeholder_9] => 1534728116 ) in dblog_watchdog() (line 160 of /srv/disk9/1218369/www/kcshadow.net/aspnet/modules/dblog/dblog.module).
The website encountered an unexpected error. Please try again later.