TRANSACTION DESIGN FOR DATABASES WITH HIGH PERFORMANCE AND AVAILABILITY

Lars Frank

Abstract

When many concurrent transactions like ERP and E-commerce orders want to update the same stock records, long duration locking may reduce the availability of the locked data. Therefore, transactions are often designed without analyzing the consequences of loosing the traditional ACID (Atomicity, Consistency, Isolation and Durability) properties. In this paper, we will analyze how low isolation levels, optimistic concurrency control, short duration locks, and countermeasures against isolation anomalies can be used to design transactions for databases with high performance and availability. Long duration locks are defined as locks that are held until a transaction has been committed, i.e. the data of a record is locked from the first read to the last update of any data used by the transaction. This will decrease the availability of locked data for concurrent transactions, and, therefore, optimistic concurrency control and low isolation levels are often used. However, in systems with relatively many updates like ERP-systems and E-commerce systems, low isolation levels cannot solve the availability problem as all update locks must be exclusive. In such situations, we will recommend the use of short duration locks. Short duration locks are local locks that are released as soon as possible, i.e. data will for example not be locked across a dialog with the user. Normally, databases where only short duration locks are used do not have the traditional ACID properties as at least the isolation property is missing when locks are not hold across a dialog with the user. The problems caused by the missing ACID properties may be managed by using approximated ACID properties, i.e. from an application point of view the system should function as if all the traditional ACID properties had been implemented.

References

  1. Berenson, H., Bernstein, P., Gray, J., Melton, J., O'Neil, E. and O'Neil, P., 1995, “A Critique of ANSI SQL Isolation Levels”, Proc ACM SIGMOD Conf., pp. 1- 10.
  2. Breibart, Y., Garcia-Molina, H. and Silberschatz, A., 1992, “Overview of Multidatabase Transaction Management”, VLDB Journal, 2, pp 181-239.
  3. Frank, L., 1999, “Evaluation of the Basic Remote Backup and Replication Methods for High Availability Databases”, Software - Practice & Experience, Vol. 29, issue 15, pp 1339-1353.
  4. L. Frank and Uffe Kofod, 2002, 'Atomicity Implementation in E-Commerce Systems', Proc of the Second International Conference on Electronic Commerce, ICEB 2002, Taipei.
  5. Frank, L. and Zahle, T, 1998, “Semantic ACID Properties in Multidatabases Using Remote Procedure Calls and Update Propagations”, Software - Practice & Experience, Vol.28, pp77-98.
  6. Gallersdörfer, R. and Nicola, M., 1995, “Improving Performance in Replicated Databases through Relaxed Coherency”, Proc 21st VLDB Conf, 1995, pp 445-455.
  7. Garcia-Molina, H. and Salem, K., 1987, “Sagas”, ACM SIGMOD Conf, pp 249-259.
  8. Garcia-Molina, H. and Polyzois, C., 1990, “Issues in disaster recovery”, IEEE Compcon., IEEE, New York, pp 573-577.
  9. Gray, J. and Reuter, A., 1993, “Transaction Processing”, Morgan Kaufman, 1993.
  10. Kung, H and Robinson, J, 1981, 'On Optimistic Methods for Concurrency Control', ACM TODS 6, No. 2.
  11. Humborstad, R., Sabaratnam, M., Hvasshovd, S. and Torbjornsen, O., 1997, “1-Safe algorithms for symmetric site configurations”, Proc 23th VLDB Conf, 1997, pp 316-325.
  12. Mehrotra, S., Rastogi, R., Korth, H., and Silberschatz, A., 1992, “A transaction model for multi-database systems”, Proc International Conference on Distributed Computing Systems, pp 56-63.
  13. O'Neil, P., “The Escrow Transaction Mode”, ACM TODS 11, No. 4, 1986.
  14. Polyzois, C. and Garcia-Molina, H., 1994, “Evaluation of Remote Backup Algorithms for TransactionProcessing Systems”, ACM TODS, 19(3), pp 423-449.
  15. Weikum, G. and Schek, H., 1992, “Concepts and Applications of Multilevel Transactions and Open Nested Transactions”, A. Elmagarmid (ed.): Database Transaction Models for Advanced Applications, Morgan Kaufmann, pp 515-553.
  16. Zhang, A., Nodine, M., Bhargava, B. and Bukhres, O., 1994, “Ensuring Relaxed Atomicity for Flexible Transactions in Multidatabase Systems”, Proc ACM SIGMOD Conf, pp 67-78.
Download


Paper Citation


in Harvard Style

Frank L. (2004). TRANSACTION DESIGN FOR DATABASES WITH HIGH PERFORMANCE AND AVAILABILITY . In Proceedings of the Sixth International Conference on Enterprise Information Systems - Volume 1: ICEIS, ISBN 972-8865-00-7, pages 222-226. DOI: 10.5220/0002598702220226


in Bibtex Style

@conference{iceis04,
author={Lars Frank},
title={TRANSACTION DESIGN FOR DATABASES WITH HIGH PERFORMANCE AND AVAILABILITY},
booktitle={Proceedings of the Sixth International Conference on Enterprise Information Systems - Volume 1: ICEIS,},
year={2004},
pages={222-226},
publisher={SciTePress},
organization={INSTICC},
doi={10.5220/0002598702220226},
isbn={972-8865-00-7},
}


in EndNote Style

TY - CONF
JO - Proceedings of the Sixth International Conference on Enterprise Information Systems - Volume 1: ICEIS,
TI - TRANSACTION DESIGN FOR DATABASES WITH HIGH PERFORMANCE AND AVAILABILITY
SN - 972-8865-00-7
AU - Frank L.
PY - 2004
SP - 222
EP - 226
DO - 10.5220/0002598702220226