Concept of Select Unlocking Optimization

Ensuring data integrity stored in a database requires change encapsulation in a transaction. Transaction maintains consistency by shifting the database from one consistent state to another, protected by atomicity, isolation, and durability. This paper focuses on the isolation and parallelism allowing distributed access to the data tuple across multiple transactions. Access to the data tuple is marked by the locks applied on the row level. Locks can be shared expressing data retrieval process or exclusive intended to change the values of the columns. The general locking rule takes the lock before the operation and releases it just after the operation or at the end of the transaction. Oracle database, however, does not release the lock physically, whereas the data block access would be necessary to be reloaded during the transaction approval. Therefore, this paper aims in optimizing the process of unlocking data before the data retrieval process using multiple strategies.

database transaction; unlocking; performance; data retrieval

1. Abhinivesh, A., Mahajan, N.: The Cloud DBA-Oracle, Apress, 2017

2. Al-Sanhani, A.H., Hamdan, A., Al-Thaher, A.B., Al-Dahoud, A.: A comparative analysis of data fragmentation in distributed database. ICIT 2017 - 8th International Conference on Information Technology, Proceedings. 724–729 (2017). https://doi.org/10.1109/ICITECH.2017.8079934

3. Dudáš, A., Škrinárová, J, Vesel, E.: Optimization design for parallel coloring of a set of graphs in the high-performance computing. In: Proceedings of 2019 IEEE 15th International Scientific Conference on Informatics, pp 93–99. ISBN 978–1–7281–3178–8

4. Elbahri, F., Al-Sanjary, O., et al.: Difference Comparison of SAP, Oracle, and Microsoft Solutions Based on Cloud ERP Systems: A Review, 15th IEEE International Colloquium on Signal Processing & Its Applications (CSPA), 8-9 March 2019

5. Graefe, G., Guy, W., Sauer, C.: Instant Recovery with Write-Ahead Logging: Page Repair, System Restart, Media Restore, and System Failover, Second Edition. Synthesis Lectures on Data Management. 8, 1–113 (2016)

6. He, Q., Zhang, F., Bian, G., Zhang, W., Duan, D., Li, Z., Chen, C.: Research on Data Routing Strategy of Deduplication in Cloud Environment. IEEE Access. (2021).

7. Jin, D., Chen, G., Hao, W., Bin, L.: Whole Database Retrieval Method of General Relational Database Based on Lucene. Proceedings of 2020 IEEE International Conference on Artificial Intelligence and Computer Applications, ICAICA 2020. 1277–1279 (2020). https://doi.org/10.1109/ICAICA50127.2020.9182496

8. Kuhn, D., Kyte, T.: Oracle Database Transactions and Locking Revealed. Oracle Database Transactions and Locking Revealed. (2021). https://doi.org/10.1007/978-1-4842-6425-6

9. Lew, M.S., Huijsmans, D.P., Denteneer, D.: Optimal keys for image database indexing. In: Del Bimbo, A. (ed.) Image Analysis and Processing. Lecture Notes in Computer Science, vol. 1311, pp. 148–155. Springer, Heidelberg (1997). https://doi.org/10.1007/3-540-63508-4_117

10. Kvet, M, Matiasko, K., Kvet, M.: Complex time management in databases, Central European Journal of Computer Science vol.4, 2014, pp. 269-284, doi: 10.2479/s13537-014-0207-4

...