Tuesday, June 8, 2010

ADS 10 Tip #7 – Excluding Tables from a Transaction

Along with nested transactions version 10 also allows tables to be excluded from a transaction. This can be very useful when all users need to see changes to a table immediately. Advantage uses read-committed isolation while within a transaction. This means that only the user within the transaction can see the changes. Additionally all records that are modified within a transaction are locked until the transaction is committed or rolled back.

Changes to Transaction-Free tables within a transaction are immediately visible and will not be restored to their original values if the transaction is rolled back. This also means that records within a transaction-free table are not locked until the transaction is committed or rolled back either. This can be very useful if you are using a table to store key values. If the table was not transaction-free a bottleneck could occur if multiple users needed to update the key table.

TxnFreeTableYou can set tables to be transaction free when creating a table. This setting can be modified on an existing table using the  AdsSetTableTransactionFree API. There are also two system procedures  sp_IgnoreTableTransactions (for free tables or dictionary tables) and sp_ModifyTableProperty (for dictionary bound tables) that can modify this property. This property can also be set in Advantage Data Architect with the Table Designer on the Table Properties tab, pictured right. All of these functions require exclusive access to the tables and the setting is persisted.

A new column TABLE_TRANS_FREE has been added to system.tables which indicates if the table is transaction free or not. An example of the various SQL statements pertaining to transaction free tables is below.

// Set the table to be excluded from a transaction
EXECUTE PROCEDURE sp_ModifyTableProperty
  ( 'tblname', 'TABLE_TRANS_FREE', 'true' , 'NO_VALIDATE', NULL );

// Ignore transactions on a table requires exclusive access
EXECUTE PROCEDURE sp_IgnoreTableTransactions( 'tblname', true );

// Get a list of tables that are transaction free in the database
SELECT * FROM system.tables WHERE Table_Trans_Free = true;

You can view a screencast on the new Transaction Processing features on the DevZone.

No comments: