Transaction Pitfalls (Magic xpa 3.x)
Working with transactions is the only way to develop data bound applications. But, in order to work with them, the developer has to know the pitfalls of using transactions. Let us go back to the problem with Fred and Wilma. Wilma received the wrong information. In the case of the transaction, Wilma received information that the transaction had not been carried out at all. Actually with some RDBMSs, she will actually have to wait until the transaction has been finished. This is what is known as a deadlock. In a multi-user environment, deadlocks are a common cause of transaction failure.
The various Database Management Systems (DBMS) behave differently when a deadlock occurs. Some DBMS systems are able to detect the deadlock and issue a warning. In this case, Magic xpa rolls back the transaction and continues execution according to the Error behavior strategy setting for the transaction.
Because some ISAM databases may hang in deadlock situations, Magic xpa provides a deadlock prevention mechanism for ISAM and SQL databases. To activate the deadlock prevention mechanism, you would set the Deadlock prevention setting in the Environment dialog box to Yes. The deadlock prevention mechanism locks exclusively all tables opened with Write access, in the same order as defined, for the period of the transaction. Temporary tables are not locked.
In our case, it was a simple situation. We can make this situation slightly different. We discussed that one of the by-products of the transaction is that the modified data is locked. Now let us take a scenario when Fred wants to make the transaction. The bank clerk has the Savings account details on the screen and wants to start the transaction now. All of a sudden, the phone rings and the clerk answers it. The data is locked and waiting for the completion of the transaction. Now, dear Wilma has found that Fred has not made the transaction and she wants to execute it herself. (She will take up the matter with her husband later.) But the data is locked; remember the clerk is on the telephone. She will have to wait until Fred’s transaction has finished before continuing. We will discuss locking in more detail in the section about isolation levels.
This simplistic situation may not be as simplistic as it seems. At the same moment that this couple are making their transactions, there may be the greengrocer who is currently cashing one of their checks; or the bank manager who is running a report on various accounts, one of them being Fred and Wilma’s account.
What is evident here is that despite the fact that database integrity is preserved, the application is time-dependent. That means that while a single user or process is currently updating the data, all others who need to use the same data either have to wait in line or make do with old data. This is perfectly correct behavior in a multi-user situation. But things start getting complicated when one user is holding up the execution, such as the clerk speaking on the phone.
The scenario that we described here is concurrency.
|
Definition:
|
Concurrency is when at least two users are accessing the same data at the same time.
|
An application should be developed in such a way that data integrity is preserved and concurrency is maximized. In other words, as many users as possible can access the same data simultaneously. What often happens is that one has an adverse effect on the other. When we try and improve data integrity, often by having a longer transaction, other users will be affected. We previously discussed before that locks are accumulative within the transaction. If the transaction is long, then all the modified data is locked until the release of the transaction. Other users will have problems when accessing that data. More about this in the next section.
