This chapter discusses the way transactions and locking are handled by DataFlex and the DataFlex Connectivity Kit for ODBC. The chapter starts with a small introduction to concurrency issues, discuss the DataFlex
programming language aspect then it will go into details of the supported database formats.
Concurrency
Concurrency is the ability of multiple users to access and modify data simultaneously and is of vital importance in any database environment. The database should handle multiple users accessing the database
simultaneously in a correct way. The means to achieve this are transactions and locking.
Transactions
A transaction is a unit of work that is done as a single atomic operation. Transactions either succeed or fail as a whole. For example consider a transaction that transfers money from one bank account to another. This involves withdrawing money from one account and depositing the money in the other account. It is important that both actions occur, it is unacceptable for one action to succeed and the other to fail. A database that supports
transactions is able to guarantee that either both steps succeed or both steps fail.
The transaction support of a database system must have the ACID (Atomicity, Consistency, Isolation and Durability) properties.
• Atomicity. A transaction must be an atomic unit of work.
• Consistency. When completed a transaction should leave the database
in a consistent state. Internal data structures such as indexes must be correct at the end of the transaction.
• Isolation. Modifications made by the current transaction must be isolated
from modifications made by other concurrent transactions. A transaction should not be able to “see” intermediate results of another transaction. • Durability. After a transaction has completed its effects are permanent.
Transactions are started by a program. The program then manipulates data in the database. Eventually the transaction is committed, or rolled back. A
Tr
ansacti
ons
commit will make all changes made by the transaction permanent. A rollback will remove all changes made by the transaction just like the transaction was never started.
Most databases support all of the transaction properties. The Isolation property usually can be set up to be less restrictive. So called Isolation Levels have been defined. The lowest level will make intermediate results available to other transactions, the highest level will not. There is an inverse relation between Isolation Level and concurrency. The higher the Isolation Level, the lower the concurrency.
Locking
A lock guarantees exclusive access to the object on which it is applied. All databases that support multi user access use some form of locking. Locking is used to enforce the Isolation transaction property. Although there are a number of variants on implementation level basically databases support the following lock granularities: row locking, page locking and table locking. Usually a database supports one or more of the lock granularities or uses a mechanism that will automatically select the best locking granularity for the task at hand. There is an inverse relation between lock granularity and concurrency. The smaller the granularity, the bigger the concurrency. If for example we have a table with 10 rows made up of 5 pages, each page having 2 rows, 10 users can simultaneously lock a row, 5 users can simultaneously lock a page and only one user can lock the table.
The use of locks can introduce a side effect known as a deadlock. A deadlock is a situation where two, or more, transactions are waiting on each other’s locks to be released. Transaction A waits for B and B waits for A. In a deadlock situation transactions wait indefinitely. This is an unacceptable situation. The database system must either prevent deadlocks from happening or detect deadlocks when they happened and resolve that situation.
The scenario below gives a sample of a deadlock situation with two
transactions. The sample uses record locking. In this case, the lock must be part of the find operation. One can only lock a record after it has been found. To indicate this special type of find we use XFind.
TRANSACTION A
TIME
TRANSACTION B
Tr
ansacti
ons
TRANSACTION A
TIME
TRANSACTION B
- - XFind R1 T1 - - - - T2 XFind R2 - - XFind R2 T3 - Wait - Wait T4 XFind R1 Wait Wait Wait Wait
If the above situation occurs, the two transactions are waiting for each other indefinitely. This is an unacceptable situation so the DBMS either needs to prevent deadlocks from happening or detect when they happen and resolve that situation.
There are deadlock avoidance strategies. The native DataFlex database uses one of these strategies. Most database systems however use a deadlock detection and resolving strategy. Deadlock can be resolved by choosing a victim transaction in the list of deadlocked transactions. The victim is then stopped by issuing a rollback. This will free the locks claimed by the victim transaction thus allowing other transactions to obtain those locks and continue.
It is important to understand that deadlocks are not a programming error. It is a situation that can occur in certain environments. It is the application
programmer’s responsibility to handle deadlocks in a proper way. Application programmers typically handle deadlock errors in two ways, one is to report an error to the user and let the user re-enter the information. The other is to automatically retry the transaction a designated number of times.
Again, deadlock is not a programming error. It is a condition that can occur in a certain environment. It depends on the application and database backend what the chance is for deadlock to occur. Usually this chance is very small. Nevertheless, application programmers should handle deadlock no matter how small the chances are for it ever occurring.
Tr
ansacti
ons
Transactions and locking in the DataFlex language
A DataFlex program will alter data in tables using one of two methods. Either they will:
• use data-dictionaries (DDOs) or their predecessor, data-sets (DSOs) • or, they will manually code their applications using commands. This
method is usually referred to “procedural” or 2.3 style.
All table changes in DDOs are handled through two messages, Request_save and Request_delete. Using DDOs greatly simplifies transaction handling. When you use DDOs, transaction support is built in (in fact it is required). There are no migration or conversion issues with DDOs or DSOs. An
application written using data-sets prior to DataFlex 3.1, will work without any required change.
Manual methods include using the traditional commands Save, Saverecord, Delete, Lock, Reread, Unlock. Three additional commands were added to DataFlex 3.1 and VDF4, Begin_Transaction, End_Transaction, and
Abort_Transaction. Depending on the applications coding logic, changes may be required to make an application work properly with transactions. Even if a legacy application works properly with transactions you may want to make changes in your code to take full advantage of the transaction handling. If you have an application that uses DDOs or DSO, you will want to refer the section titled Handling Transaction and Data-Dictionaries. If, you have applications that do not use DDOs or DSO, you should review the following section.
Transactions and locking using the command language
The DataFlex programming language supports a number of commands to define transactions and use locking. These commands are:
• Begin_Transaction to define the beginning of a transaction
• End_Transaction to commit a transaction
• Abort_Transaction to rollback a transaction
• Lock to lock the database
• Reread to lock the database and refresh all active buffers
• Unlock to unlock the database
The Lock, Reread and Unlock commands have been part of the DataFlex language for a long time. Up until DataFlex revision 3.1 there was no proper
Tr
ansacti
ons
support for transactions. In DataFlex 3.1 support for transactions was added in the form of the three transaction commands. VDF and WebApp have always supported transactions.
Command Description
Begin_Transaction Marks the beginning of an explicit transaction. Every database action between this command and its accompanying End_transaction command is “the transaction”. Defining a transaction within a
transaction is not considered an error. The inner transaction will be embraced by the outer one making both
transactions act as if it were one. Begin_Transaction and
End_Transaction must be on the same scope. It is not allowed to place the End_Transaction command in a different scope.
Begin_Transaction will lock the
database; see Lock for a description of locking the database.
End_transaction Marks the end of an explicit transaction. Every database action between this command and its accompanying Begin_Transaction command is “the transaction”.
End_Transaction will unlock the database. See Unlock for a description of unlocking the database.
Abort_Transaction Will rollback the transaction.
The abort transaction command will not jump to the End_transaction command automatically. The flow of the program will continue on the next line. It is the
Tr
ansacti
ons
programmer’s responsibility to ensure control jumps out of the transaction after issuing this command. If the
programmer does not take care of the control flow errors like
DFERR_EDIT_WITHOUT_REREAD (4155) can occur.
Abort_Transaction will unlock the database. See Unlock for a description of unlocking the database.
Usually there will be little need to use the Abort_Transaction command. Transactions are automatically aborted if errors occur (see below) and the flow jumps to the End_Transaction command in that case.
Lock Locks the database. Every table open in
the application that is not read only or an alias will be locked. The database API will call the driver of each individual table to lock that table. Tables are locked in the order of the number that they are opened in. Normally this is the same order as defined in the filelist. If the lock attempt generates error DFERR_LOCK_TIMEOUT (4106), the API will automatically retry the lock. The number of retries can be set by using the Set_Transaction_Retry command. A database driver may or may not
generate the DFERR_LOCK_TIMEOUT error.
Some database systems use row locking. Row locking differs from table locking not only in granularity but also in timing. You can lock a table any time. You can only lock a row after it has been found. Drivers that connect to row locking databases usually do not
Tr
ansacti
ons
implement the lock function but will lock as finding occurs in a locked state. Lock and Unlock are legacy commands. If you want your application to take full advantage of transaction processing we recommend that you replace these commands with Begin_Transaction and End_Transaction.
Reread A reread is actually two actions. Reread will lock the database followed by a re- find. If the reread command gets no arguments, every active record is re- found. If the reread command has arguments only the active record of the passed buffers are re-found. There is no difference in how the lock is done between the two commands reread and lock.
See Lock for a description of locking the database.
Unlock Unlocks the database. Every table, page
or row that has been locked will be unlocked. The database API will call the driver of each individual table to unlock that table. Tables are unlocked in the order of the number that they are opened in. Normally this is the same order as defined in the filelist.
Lock and Unlock are legacy commands. If you want your application to take full advantage of transaction processing we recommend that you replace these commands with Begin_Transaction and End_Transaction.
Transactions support the ACID properties; this makes programming a system that supports transactions more simple. For example, suppose we have a restriction in our application that we can only order items that are in stock. In a
Tr
ansacti
ons
non-transaction system this condition would have to be checked before trying to save the order. In a transaction system however one can start the
transaction, then start saving the rows that make up the order. If somewhere along the way the condition is not met, issue an error, which will return the database in the state it had before starting the transaction. If the condition is met for every row, issue an End_transaction thus committing the transaction. Using the transaction commands to define transactions is referred to as doing explicit transactions.
Since the transaction commands were not supported in DataFlex prior to revision 3.1 programmers would be forced to adjust all their programs in order to use the new revision’s transaction logic. To avoid this and offer a smooth transition into version 3.1 DataFlex supports so-called implicit transactions. An implicit transaction is started by the Lock or Reread command and ends with the Unlock command.
Errors in a transaction
Because transactions must support Atomicity, errors that occur during a transaction will force a rollback of the transaction. When the error occurred during an explicit transaction, control will jump to the next line of the End_Transaction command. This behaviour of transactions results in a minimal use of the Abort_Transaction command. Transactions are usually rolled back because some error occurred, since the occurrence of an error results in a rollback, there is no need to explicitly use the Abort_Transaction command in such a case.
The only error that is an exception to this rule is the
DFERR_LOCK_TIMEOUT (4106) error. If this error occurs, the program will check the retry setting and if a retry must be done jump to the beginning of the (implicit or explicit) transaction. The number of retries can be set by using the Set_Transaction_Retry command. If the object oriented programming style is used the message Verify_Retry will be send to the object where the transaction is defined. This function can return zero in which case the transaction will be retried, a non-zero return value will stop the retry. If the logic loops through all its retries and it still gets a time out error, the timeout error will be treated as any other error would.
Implicit transactions will not be aborted when an error occurs. Instead the flow of the program will continue on the next line. Since implicit transactions where designed for compatibility reasons, the behavior is the same as it was before transactions where introduced to DataFlex. Some database drivers may not be able to support implicit transactions.
Tr
ansacti
ons
There is a difference in how some DataFlex revisions handle errors in implicit transactions. DataFlex 3.1d, DataFlex 3.2, Visual DataFlex 6.0, 6.2 and 7.0 will abort the implicit transaction if an error occurs. The flow of the program however will not jump out of the implicit transaction but continue on the next line. The behavior change was made to accommodate non-DataFlex databases. It does affect the way existing applications using DataFlex data work when errors occur in a locked state. This behavior will usually not be desired. If for example an implicit transaction tries to save 3 records and an error occurs while trying to save the second record, the transaction will be rolled back but the control will not jump to the end of the implicit transaction. In this case it will still try to save the third record. The rollback has removed the lock so the DFERR_EDIT_WITHOUT_REREAD (4155) error occurs. In DataFlex 3.2 and Visual DataFlex 7.0 this can be avoided by setting the DF_TRANABORT_ONERROR global attribute. In the other revisions mentioned above there is no way to change this behavior. Setting this attribute to false will not cause the implicit transaction to be rolled back if an error occurs. To adjust the setting the programmer should add the line: “Set_attribute DF_TRANABORT_ONERROR To (False)”. In upcoming revisions of DataFlex the DF_TRANABORT_ONERROR attribute will be supported. Its default value will change from true to false. This will ensure that new revisions will be compatible with pre-transaction code.
In Visual DataFlex 7.0 the attribute DF_TRANABORT_ONERROR is not defined in fmac. If you want to set it in a VDF7 application you will have to define it. The internal attribute number for the attribute is 22. To define it add “ #REPLACE DF_TRANABORT_ONERROR |CI22” to your program code. In DF3.2 the attribute has been defined in fmac, you can use it without having to define it first.
Please note that explicit transactions will always be aborted when an error occurs no matter how the DF_TRANABORT_ONERROR attribute is set. Data sets and Data dictionaries automatically use explicit transactions. Setting the
DF_TRANABORT_ONERROR attribute has no effect on Data Set / Data Dictionary based applications. They always use explicit transactions.
Due to the difference between runtimes the behavior of existing applications may change when converted to a newer version. Lets summarize how different runtime versions behave compared to pre-DataFlex 3.1 runtimes. The runtimes that behave different are marked red; those that behave different but can be made to behave the same are marked blue
Important: These differences only apply to applications that are not using
Tr
ansacti
ons
exhibit the same, correct behavior.
Runtime Behavior
DF3.1, DF3.1b, DF3.1c
Exactly the same as in pre-3.1 runtimes. If an error occurs during an implicit transaction the
transaction will not be aborted. The program continues in a locked state.
DF3.1d Different from pre-3.1 runtimes. If an error occurs during an implicit transaction the transaction is aborted. The program continues in an unlocked state.
DF3.2 Different from pre-3.1 runtimes. If an error occurs during an implicit transaction the transaction is aborted. The program continues in an unlocked state.
In DF3.2 the programmer can set the runtime to work compatible to pre-3.1 runtimes in the following way:
Set_attribute DF_TRANABORT_ONERROR To (False)
VDF4,
VDF5 Exactly the same as pre-3.1 runtimes. If an erroroccurs during an implicit transaction the transaction will not be aborted. The program continues in a locked state.
VDF6 Different from pre-3.1 runtimes. If an error occurs during an implicit transaction the transaction is aborted. The program continues in an unlocked state.
VDF7 service pack 3
Exactly the same as in pre-3.1 runtimes. If an error occurs during an implicit transaction the
transaction will not be aborted. The program continues in a locked state.
In VDF7 the programmer can set the runtime to work incompatible to pre-3.1 runtimes in the following way:
Tr
ansacti
ons
#REPLACE DF_TRANABORT_ONERROR |CI22 Set_attribute DF_TRANABORT_ONERROR To (True)
Lock granularity
DataFlex only recently started supporting different database systems from the native DataFlex database. The native DataFlex database uses a DataFlex specific way of locking. This way of locking is not supported by all other database systems. Issuing a lock can therefore result in different behavior depending on the database system in use.
The native DataFlex database supports table level locking only. This means that one and only one transaction can have a table locked at any given time.