Transactions - Multi-User Considerations (Magic xpa 3.x)
Much thought should be given to the subject of transactions. On the one hand transactions increase data integrity due to the use of the locking mechanism, but also reduces concurrency. The longer the transaction, the more concurrency is reduced. You can select the Transaction mode from the Data tab in the Task Properties dialog box.
In the Transaction Begin property, selecting the transaction to begin Before Task Prefix, causes all updates to be logged as a single transaction. This has various implications for multiple users as it reduces concurrency but it does increase performance. The following suggestions should be considered:
-
For improved concurrency, it is better to begin transactions at the Task Prefix logic unit for Batch tasks with a data view that includes only a few records. Of course, this depends on your project, as there may be instances in which you are required to lock many records.
Use transactions at the Task level in Batch tasks when performance is important (SQL databases). ISAM type databases may require a lot of disk storage for extended transactions in Batch.
Note that if the user is working in Deferred mode, it is not recommended to begin the transaction before the Task Prefix logic unit since these operations are stored in Cache memory, and memory is limited.
-
Using a transaction at Task level for interactive tasks may cause problems for other users accessing the same tables, in multi-user projects, because the data sources may be locked during the transaction for long periods of time. Once again, there may be instances in which this is required.
-
Be aware that if a task is called within this transaction, those locked rows will also be part of the transaction and thus unavailable to other users.
-
Task level transactions that cover large quantities of records will result in the expansion of the Transaction Log file, thus reducing available space on the disk drive. Be careful not to run out of disk space, because this will abort the transaction.
-
When entering a task in which either a transaction is already open or else there is a task level transaction, Oracle locks the entire data view of that task (that is, all records in the range). In some cases, this is the intended behavior. You should be aware that if you only want to lock some of the records, you should adjust the range accordingly. This behavior is beneficial for data integrity and performance, as all records are locked while they are being fetched to create the data view. In other databases, the data is only locked when parked on the record. This affects performance, because for each record the data is locked. But on the other hand, concurrency is maximized.
