How-to: Concurrency (Multi User access) and Locking

One of the key functions of a database is to ensure that multiple users can read and write to the database without overwriting each other's changes inadvertently or reading inconsistent data due to in-progress changes. Designing application code to take account of concurrency issues is essential.

Multiversion Concurrency Control

To handle simultaneous access of the same data by many users, Oracle utilises various types of locks and a multiversion consistency model to maximise performance. A simple way to think of Oracle read consistency is to imagine each user operating a private copy of the database.

Oracle guarantees that the set of data seen by a statement is consistent with respect to a single point in time and does not change during statement execution.

This means that applications which attempt to perform or enforce database consistency at the application level cannot assume the Oracle data they read will remain unchanged during the execution of a transaction (just because such changes are not visible to the transaction does not mean they don't happen).


Taxi table: Taxi_id, drivers_name
jobs table: Job_id, Taxi_id, start_time, end_time

Application inserts row into jobs for a Taxi_id
Application looks in jobs table for overlaps with that row - "is the taxi already booked during that time?"
Two people - at the same time - will not see each others insert = double booking!

For this case you need to SELECT for UPDATE on the Taxi table (by primary key:Taxi_id) as part of a transaction.

For most operations the default row-level locking will automatically lock the right rows, only a minority of cases (maybe 10%) need the above technique.


Oracle can provides read consistency for an individual query or to all of the queries in entire transaction.

To manage the multiversion consistency model, Oracle must create a read-consistent set of data when a table is being queried (read) and simultaneously updated (written). When an UPDATE occurs, the original data values changed by the update are recorded in the database's undo records. As long as this update remains part of an uncommitted transaction, any user that later queries the data will view the original uncommitted data.

Only when a transaction is committed are the changes of that transaction made visible to other users.

Statement-Level Read Consistency
Oracle always enforces statement-level read consistency. This guarantees that all the data returned by a single query comes from a single point in time—the time that the query began. Therefore, a query never sees 'dirty' data nor any of the changes made by transactions that commit during query execution.
For most applications, read committed is the most appropriate isolation level.
Read committed is the default.

Transaction-Level Read Consistency
Oracle also offers the option of enforcing transaction-level read consistency. When a transaction executes in serializable mode, all all queries within the same transaction reflect the state of the database as of the time the transaction began. (you do see changes made by the transaction itself.)
Serializable will produce repeatable reads within a transaction, updates are possible with the risk of getting ORA-8177 so this is appropriate only for very short /small transactions. e.g. inserting a single small row into a table.

Read-Only Transactions
For queries that don't modify any data, you might prefer a read-only transaction.
You can execute multiple queries against multiple tables, knowing that the results are consistent with respect to the same point in time. This can be useful for reports where all the numbers need to tally up.

Session Default

You can set the isolation level of a transaction by using SET TRANSACTION… or at a session level you can use ALTER SESSION… to set it for all subsequent transactions.

Automatic Locking

Oracle's automatically locks table data at the row level to minimize data contention. Oracle doesn't ever escalate row locks to block or table locks.

Oracle can maintain several different types of row lock:

Exclusive lock mode - Prevents the resource from being shared. This lock mode is obtained to modify data. Only one exclusive lock can be placed on a resource (such as a row or a table).

Share lock mode - Allows the resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding a share lock to prevent concurrent access by a writer (who needs an exclusive lock). Many share locks can be placed on a single resource.

Manual Locking

Oracle's automatic locking can be overridden at two levels:

Per Session
As described above, the ALTER SESSION statement can set the transaction isolation level.

Per Transaction
Transactions that include the following SQL statements override Oracle's default locking:
- The SELECT … FOR UPDATE statement
- The LOCK TABLE statement (which locks either a table or, when used with a view, the underlying base table)

Locks acquired by these statements are released after the transaction commits or rolls back.

Deadlock Detection

Oracle automatically detects deadlock situations and resolves them by rolling back one of the statements involved, and returning an error message to the failed transaction.

Deadlocks are very unlikely to occur if all application code follows the rule that when both a master and detail table are updated, the master table is locked first and then the detail table. (either implicit or explicit locks)

When you know you will require a sequence of locks for one transaction, consider acquiring the most exclusive (least compatible) lock first.

Managing Multiple Transactions

You can name a transaction by using the SET TRANSACTION … NAME command. This makes it easier to monitor long-running transactions and to resolve in-doubt distributed transactions.
Transaction names replace commit comments for distributed transactions, the advantage is in monitoring activity. e.g. you can find transaction names in the data dictionary (V$TRANSACTION)

Commit comments are supported for backward compatibility only.

A space allocation failure will normally cause SQL statements to fail, to prevent this a RESUMABLE mode can be enabled for the session (ALTER SESSION)

Transaction control statements (e.g. commit, rollback to savepoint) within an autonomous PL/SQL block will apply only to the currently active autonomous transaction.

Similarly, when one transaction calls another autonomous transaction, then a 'commit' or 'rollback to savepoint' in the main transaction will not apply to the second autonomous transaction.

Comparison with SQL Server

Rather than multi-versioning, SQL Server uses a locking model:

Comparing this with Oracle, it might initially appear that SQL Server will spend a lot more time waiting, but consider, if many users are waiting for the one process with a lock, then that process will finish faster (having more or less exclusive access to the disk), the next process in line will then obtain it's lock and gain access to those blocks of data (which are probably now in cache anyway), also bear in mind that any user accessing a different row (or page) will not have to wait for a lock.

In terms of performance this 'locking & waiting' approach of SQL server is not significantly faster or slower than Oracle's multiversioning. The important difference is in the way that data changes become visible to concurrent transactions.

SQL Server has a choice of 4 isolation levels, these follow the ANSI/ISO SQL92 standard (1992)

Shared locks are held while the data is being read. This option is the SQL Server 2000 default.
Data can be changed before the end of the transaction, resulting in nonrepeatable reads (phantom data).
(Writers do not wait for Readers)

Implements dirty reads, this allows Readers to be nonblocking, (as in Oracle) but also means that Readers ignore exclusive locks, so values in the data can be changed and rows can appear or disappear in the data set before the end of a transaction. Reading uncommitted data means an application will read data that has been changed by another user but has not yet been committed.

Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user.

Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. (Writers wait for Readers)

Both Oracle and SQL Server do conform to the ACID properties (atomicity, consistency, isolation, and durability) but achieve this in different ways.

"Concurrency Controls - understand them or you will corrupt your data" ~ Thomas Kyte

See also

Copyright © 1999-2024
Some rights reserved