Imagine that at your local superstore(Walmart, Target, etc.), when you got to the parking lot, you had to get a number and wait till you got called to proceed into the store to do your shopping(however long that takes) and then leave, consequently freeing up space for the next person to come in. This sounds very similar to how things have been quite recently in our human history.
Alternatively, consider a situation where you’re sitting in the living room, and your sibling is in the bedroom. You both want to watch TV: you want to watch The Wire, and he wants to watch The Rookie (someone needs better taste!) .
However, both televisions have to be in sync! So you change the program; he sees that change on his end, he changes it back, you see it on your end, and so on and so forth. You eventually thumb wrestle for it and lose!
Does either scenario sound ideal?
Well, without the concept of locking & transaction concurrency in relational database management systems, your database will behave similarly: be a one-user system (Almost like your local grocery store during COVID), or people will overwrite each other (like you and your sibling fighting over which TV show to watch). This blog talks about Oracle’s implementation of locking and how it serves to ensure transaction concurrency and enable multi-user access to the database.
What is a lock?
A lock is a mechanism in the database used to regulate access to a shared resource. In other words, they prevent one user from accessing a resource that is being modified by another user. The term shared resource is important, as locks are commonly associated with rows and tables. Oracle also uses locks at many other levels to ensure concurrent access to various resources. For example, a named PL/SQL block stored in the database while being executed cannot have the underlying code modified at the same time.
An Oracle database has many different lock types which can be held at 3 levels :
- Row level: locks only the rows accessed by a transaction
- Table level: locks the entire table accessed by a transaction
- Database level: locks the entire database when it’s accessed by a transaction
More details can be found in the docs.
Database locks could also be shared or exclusive, meaning that the resource can still be accessed while the lock is held and other shared locks can be acquired, or the resource is prevented from being shared. Exclusive locks are typically obtained when modifying data.
If an application is poorly designed and holds a lock for longer than required, then this could lead to blocking in the Oracle database.
Some of the many types of locks in Oracle include:
- TX (Transaction) locks: These locks are acquired for the duration of a data-modifying transaction.
- TM (DML Enqueue) and DDL locks: These locks ensure that the structure of an object is not altered while you are modifying its contents (TM lock) or the object itself (DDL lock).
- Latches and Mutexes: These are internal locks that Oracle employs to mediate access to its shared data structures.
This post contains a table of all the lock types in various database versions.
What is a transaction?
A transaction is a logical unit of work: a sequence of steps that must all succeed for the operation to be considered complete.
Imagine a simple barter scenario. You live in 12th‑century Africa, where goods are traded directly. You send your steward with five bags of beans to a farmer two miles away, expecting him to return with thirteen bags of corn. The transaction is only complete if he travels to the farmer, exchanges the goods, and returns home safely with the corn. If he is robbed, injured, or unable to reach the farmer, the transaction fails.
Database transactions work the same way. Every step must succeed from start to finish. If I transfer you money, it must leave my account and arrive in yours; if the process fails halfway, the entire operation is rolled back. A transaction must be all or nothing, a principle known as atomicity, one of the properties of a transaction. Others include: consistency, isolation, and durability, collectively known as the ACID properties.
Atomicity
This means that a database transaction is meant to run its entire intended course or not run at all. A database transaction must complete all of its intended steps. The database cannot run only a part of a transaction. For example, if a database transaction is meant to delete 100 rows, the entire transaction should roll back if the database fails after deleting 99 records.
Consistency
This means that a database transaction must leave the database in a valid state at the end of its processing. A database transaction moves the Oracle database from one consistent state to another. For example, a banking transaction meant to move money between two accounts must debit and credit the source and target account or roll back completely. If the transaction debits the source account and fails, then that leaves the database in an inconsistent state. The transaction should leave the database consistent or not proceed at all.
Isolation
This refers to the ability of a transaction to run on its own, as if it were the only transaction running in the entire database. The transaction is prevented from viewing the uncommitted work made by other transactions processing at the same time. The isolation property makes it appear as though statements are being executed serially(one after another). It’s upheld thanks to the database concurrency control mechanisms such as undo data and statement-level read consistency.
Durability
The work done by a transaction should be committed permanently to the database. When a transaction completes, the changes it made cannot be lost due to a power outage or any other such failure. Committed changes are saved permanently to the database. The database’s recovery mechanism, with the help of the write-ahead protocol, helps enforce this property. This protocol ensures that before the database writer writes the changed data to disk, it ensures that the log writer has already completed writing all redo records for the changed data from the log buffer to the redo logs on disk.
Transaction Concurrency Controls
To ensure data consistency across concurrent sessions, each user must see a consistent set of data that includes all changes made by that user’s transactions as well as all committed changes made by other users’ transactions. Concurrency controls are the collection of functions that the database provides to allow many people to access and modify data simultaneously.
In a single-user database, it’s a trivial matter to achieve data consistency. However, enterprise-level databases need to allow simultaneous operations by numerous users, a requirement that’s known as data concurrency. Improper interactions among transactions can cause data to become inconsistent.
Transaction Isolation Levels
There are many different transaction isolation levels. For each one, the same transaction could have different outcomes. This means the result of the work done by a transaction under each isolation level is not the same. There exist read committed, read uncommitted, serializable, and read-only isolation levels.
Read Uncommitted
This isolation level provides maximum performance but minimal data consistency. At this isolation level, a transaction can read data that has been modified by another transaction but not yet committed. This leads to issues like non-repeatable and phantom reads. This isolation level is not supported in Oracle.
Read Committed
Read committed is the default isolation level in Oracle, and it ensures that every query in a transaction executes with statement-level consistency. Under this isolation level, A statement can only see the committed data from other sessions from before that statement began. Also, locks are only held on modified rows and not on queried rows. It’s very scalable as it ensures that readers do not block writers. There is, however, the possibility of non-repeatable reads and phantom reads to occur if other sessions commit data between your session’s statements.
Serializable
This isolation level simulates a single-user system as it ensures transaction-level read consistency. Here, a transaction can only see data that was changed before it began plus the changes by its own transaction. It’s the most restrictive level of isolation and also provides the highest degree of isolation. Under this isolation level, repeatable reads are guaranteed, and changes made by other transactions are not visible. Oracle achieves this by making use of undo data. If a serializable transaction attempts to update or delete a row that was modified and committed by another transaction after your transaction started, Oracle immediately throws an
ORA-08177: can't serialize access for this transaction error.
Read Only
This level is similar to Serializable, with the only difference being that data modification is not allowed. Queries within the transaction see data committed before the transaction began. This isolation level is Ideal for generating complex, multi-query reports where data must remain perfectly consistent across all outputs, without locking out active writers.
Some Tips for Achieving Transaction Concurrency in Oracle
In order to ensure that users see a consistent set of data, avoid data manipulation anomalies, allow multiple users to access a system, and prevent long-running transaction errors, the following are practical steps that could be taken.
- Use the default READ COMMITTED isolation level to maximize throughput and prevent dirty reads.
- Close transactions by committing immediately after it’s work is complete, not multiple times within.
- Application sessions should disconnect after prolonged inactivity to prevent blocking sessions.
- Access and update database tables in the same sequence across all application modules to prevent deadlocks.
- Always index foreign key columns to prevent table-level locks when updating child tables.
- Use bind variables to stop excessive hard parsing from blocking concurrent database sessions.
- Size undo tablespaces appropriately and configure retention to match your longest query, avoiding snapshot errors (ORA-01555).
- In modern database versions, make use of features like Oracle 23ai Lock-Free Reservations.
Conclusion
Transaction concurrency and database locking are not necessarily the top things on developers’ minds when writing application code. However, they can be the difference between your system eventually becoming a one-user application and being robust enough for a large production environment. An application should be properly stress-tested by multiple users before being deployed to production to avoid concurrency issues escaping and, consequently, lots of support calls on go-live day. Different RDBMS(es) also handle concurrency differently; it’s important to understand your product. By understanding how concurrency is handled and following the best practices, developers can build scalable, highly performant database applications.
Cheers, Harris.