Oracle Locks, blocks, and deadlocks are very frequent on OLTP databases, and every time they happen, you can see frustrated people. A few seconds later you will hear a guy saying “the database is not working properly” or something worse “the database is blocking us” as if the database were conspiring against them. Hey guys! You have chosen a database which prioritizes data concurrency, consistency, and integrity, so now it is helping your data remain consistent.
Many people use the words lock, block and deadlock interchangeably, which is wrong. One of the more important things when resolving an issue is to be on the same page, so let’s discuss what these concepts mean. What is the meaning of each word? When should we use each one? And the most crucial question: how to avoid each scenario?
Keep reading to learn more about Oracle: a simple guide to Manual Partitioning
Oracle Locks
Oracle describes this action as: “A lock is a mechanism that prevents negative interaction between transactions accessing a shared resource. Locks help ensure data integrity while allowing maximum concurrent access to data.”
In other words, every time you access some piece of data, some kind of magic inside Oracle is acquiring a lock in your stead and preventing another process to modify the same piece of data (or a part of them) and guaranteeing your access. You have two basic types of locks: shared and exclusive locks.
Oracle Shared locks
These kinds of locks are more often associated to read operations, and their main purpose is mainly to keep the same data structure through the entire process. Imagine you are performing a full table scan over a big table of hundreds of gigabytes. Even if you had the fastest disks available on your storage, the scan would take several minutes to retrieve all the information, reading one block at a time. Now, imagine another user is performing a DDL at the same time and drops a column before your process had a chance to read these blocks. Something will go wrong. Shared locks have been designed to prevent these scenarios.
Actually, you may find some situations in which shared locks operate over DML transactions, but they depend on the isolation level of each type of transaction. This is a complex subject that exceeds the purpose of this paper.
Oracle Exclusive locks
Every time you try to modify a row or a set of rows through write operations, such as an insert, update, delete or merge statement, you will be involved in two types of lock: a row-level exclusive lock over all rows involved in your statement, and a (shared) table lock to prevent your table structure to be modified while your DML remains running. This way, Oracle prevents other processes from modifying the same rows you are working on or modifying your table structure until you release the lock through a commit or rollback statement.
So, only one exclusive lock can be acquired over a single resource at the same time.Each of these single locks could hold a lot of processes until each lock is released and the processes that were held can resume their operation. On the other hand, several processes can acquire shared locks at the same time over the same resource.
Oracle Deadlock
What is a deadlock? It is an odd situation in which two or more processes could be fighting each other in an endless struggle. Each process is at a stage that requires the row-level exclusive lock acquired for the other process so that no process can finish their job.
Imagine a point in time named “t1”, at this point the process “p1” acquires a row-level exclusive lock for the first row in the table. At the same time, another process named “p2” acquires a row-level exclusive lock for the second row on the same table. A few cycles later, p1 needs to modify the second row in the table which is currently locked by p2. At the same time, p2 needs to modify the first row in the same table which is currently locked by p1. This constitutes a deadlock.
Given this situation, Oracle will detect the deadlock and resolve the issue by rolling back one of the processes. Of course, not all deadlocks are as simple as the mentioned, there could be several processes and many tables involved in a single deadlock.
Oracle Blocking Locks
What about blocking locks? As we said, in OLTP databases, it is highly likely that two or more processes will compete for the same resources. The database will do its magic in pursuit of keeping your data consistent and intact acquiring the right locks according to the particulars of each situation. But, what happens with the other processes that need to modify the same resource while a lock persists? They will remain in the wait state of course, and this wait is what some people call “blocks” inaccurately.
So, in this scenario, some processes will halt others. These so-called “blocking locks” are abstract concepts resulting from locks. When they appear, there’s not much you can do to fix them. You can wait until the process that placed the lock releases it or you can identify the process that placed the lock, kill it and wait until the whole transaction is rolled back and the lock is released.
The most important question is how to avoid these blocking locks. Technically, a process that is blocking another one, does not constitute a database error, even when there are many of them, and you are notified with an “ORA-00060: deadlock detected while waiting for resource” message. These come from common mistakes in application design.
My best advice is simple and should be obvious: if you are working in an OLTP you cannot just add new flows and associated code without analyzing the whole number of processes that will access the same resources concurrently (even when the possibility of occurrence is low). You will need to plan and serialize your code; you will need to split your transactions to make them as small as they can be and release the locks as fast as possible. You will need to use indexes, access the rows you want to modify by unique identifier if possible, and make small chunks for massive modifications. You will need to be smart and creative.
Your main reference to understand how your Oracle database works should always be its documentation. Here’s the specific link to the concurrency topics on Oracle 12cR2:
http://docs.oracle.com/apps/search/search.jsp?category=database&product=e66230-01&q=concurrency
Follow instructions step by step to reach a successful Oracle configuration by reading DataGuard Broker in Oracle Step-By-Step
