Enhancing Database Efficiency: A Comprehensive Approach to DB Lock Optimization

Abstract: Typically, large-scale data transfers into the database cause lots of locking which negatively affects query execution, concurrency, as well as blocking operations such as ALTER, UPDATE, and DELETE. To ensure data consistency, a set of locks are necessary, that can become a performance bottleneck as well as cause deadlocks. The contributions of this paper include three novel techniques to improve database operation and defeat large data loads through lock contention. We minimize data handling efficiency by converting lists into sets first. The second is that data can be processed outside the main database by using temporary or virtual memory, which minimizes the lock durations. Third, by using ‘placeholders’ when inserting data, the operations stay non-blocking and do not affect other transactions. We implement these methods to show how the performance of the database can be increased, how high the concurrency is, and how we can prevent locking issues in the database in the future.