What is Database Concurrency?

Database concurrency denotes the concurrent transaction processing capabilities of a database.

What is Database Concurrency?

  • Database concurrency is a unique characteristic enabling two or more users to retrieve information from the database at the same time without affecting data integrity.

  • Most database management systems support concurrent operations, with the universal principle of keeping unsaved datain temporary log files to maintain data integrity and consistency. Businesses can apply two different concurrencytechniques when dealing with databases:

    1. Simultaneous access to data: Allows multiple users to retrieve information from the database simultaneously
    2. Coexistent query workload: An indicator of system performance, enabling businesses to determine the total units of work executed concurrently in a system
    Check out this blog ondatabase concurrency for a simplified explanation with examples from a database, open-source, anddistributed systems performance and scalability expert.
  • While concurrent database operations can enhance system performance, maximize resource utilization, and reduce query response time, parallel execution of database transactions can also create data consistency problems, especially when multiple transactions simultaneously operate on the same data object.

    For instance, imagine a scenario where a particular user deposits money into a bank account, and at the same time, another user withdraws money from the same account. Without proper concurrency control, the order of operations could cause wrong account balance updates in the bank database.

    Common issues during concurrent database transactions include dirty reads, lost data updates, and phantom reads. Outlined below are some of the key problems related to concurrent execution of database transactions:

    Dirty read: This issue arises when a particular transaction accesses a data object written or updated by another uncommitted transaction in the database. For example, imagine two different transactions - T1 and T2 - are updating and retrieving data from a stock brokerage account, respectively. The brokerage account has five shares, and this information is stored in the database in an integer variable called stocks_tally. The T1 transaction updates the stocks_tally to 8, and at the same time, the T2 transaction reads this value and gets committed successfully. However, the T1 transaction fails due to the database server issue, resulting in the rollback of the database to the previous state. As a result, the T2 transaction will have an incorrect stocks_tally value, which is a dirty read and can lead to an inconsistent database.

    Lost data updates: This issue emerges when two transactions simultaneously operate on the same data variable, resulting in a loss of data updates made by one of the transactions. The second transaction generally nullifies the data updates of the first transaction.

    Time

    Transaction (T1)

    Transaction (T2)

    Tw

    Read (B)
    B = B - 100

    Tx

    Read (B)
    B = B + 300

    Ty

    Write (B)

    Tz

    Write (B)

    The example includes two different transactions, T1 and T2, looking to update the value of a particular variable, B. The value of B is 300 at the start of the operation.

    • At Tw time, the T1 transaction modifies the value of B to 200. However, this value isn’t updated in the database at this stage (commit pending).
    • At Tx time, the T2 transaction reads the value of B, which will still reflect as 300 in the database as the T1 transaction didn’t update the value yet. The T2 transaction will then alter the value of B to 600 but didn’t save it.
    • At Ty time, the T1 transaction writes the value of B in the database, which will be 200 as the T2 transaction is uncommitted.
    • At Tz time, the T2 transaction will write the value of B in the database, which will be 600. It will also overwrite the value of B updated by the T1 transaction in the database previously. Therefore, the final value available in the database will be inaccurate.

    Unrepeatable read: This issue happens when a transaction accesses a particular database variable two or more times, but it reads a unique value of the variable on every iteration.

    Time

    Transaction (T1)

    Transaction (T2)

    Tx

    Read (B)

    Ty

    Read (B)
    B = B + 50
    Write (B)

    Tz

    Read (B)

    The example includes two transactions, T1 and T2, operating on the same data object B in the database. Initially, the value of B is 50. At Tx time, the T1 transaction reads the value of B. However, at Ty time, the T2 transaction modifies the value of B to 100 and updates this value in the database with a commit operation. As a result, the T1 transaction reads a different value of B at Tz time, resulting in a non-repeatable read problem.

    Phantom read: This issue emerges when two identical queries get executed during a transaction, but the number of records returned by the second query is dissimilar to the first query. It happens because another transaction inserts new records in the database table before the second query runs. For example, imagine two transactions, T1 and T2, operating concurrently on an employee table in the database. The T1 transaction runs a query to fetch the employee records belonging to the IT department. The total number of records returned by this query is 10. Before the T1 transaction can rerun the same query, the T2 transaction inserts a new employee record. As a result, the T1 transaction will get a different set of records upon rerunning the same query.

  • Database management systems are designed to apply different concurrency control techniques to overcome concurrency challenges, such as:

    • Lock-based protocols: In multi-user databases, require every transaction to request an appropriate lock before data read or write operations to prevent data integrity issues. A lock merely denotes the type of operations (read and write) permitted on a particular data object. Shared and exclusive locks are typically active to prevent data integrity problems during concurrent database transactions.
    • Timestamp-based protocols: In multi-user databases, ensure the concurrent transactions execute in an ordered manner based on the timestamps assigned to them. Older transactions are given priority while deciding the execution order of concurrent transactions in this method.
    • Multiversion concurrency control (MVCC): Databases like Oracle and Postgres utilize multi-version concurrency control to eliminate data consistency issues evident in simultaneous transactions. It helps eliminate the read and write operation conflicts arising during concurrent transactions. MVCC is typically used with other concurrency control mechanisms for better results, such as multiversion timestamp ordering and multiversion two-phase locking.
  • Database performance monitoring tools can help determine the concurrency capabilities of your database system. You can easily view and analyze the queries processed simultaneously by your database application. Database performance monitoring tools also help analyze and correlate different performance metrics, such as query response times and disk usage, on a unified interface to determine the underlying reasons for poor database concurrency and throughput.

    Besides concurrency, these tools can assist you in consistently observing your database performance over a period to uncover and fix recurring issues and enhance overall system performance. These tools can also send timely alerts regarding slow-running queries, inefficient workloads, and other issues, so you can act upon such problems quickly to optimize database performance.

Featured in this Resource
Like what you see? Try out the products.
Database Performance Analyzer

Monitor and optimize multiple database management system (DBMS) platforms for cloud and on-premises environments.

Email Link To TrialFully functional for 14 days
SolarWinds SQL Sentry

SolarWinds SQL Sentry provides database performance monitoring for only the Microsoft SQL Server and platform.

Email Link To TrialFully functional for 14 days

View More Resources

What is Database Management System (DBMS)?

Database performance management system is designed to help admins more easily troubleshoot and resolve DBMS performance issues by monitoring performance and providing root-cause analysis of your database using multi-dimensional views to answer the who, what, when, where, and why of performance issues.

View IT Glossary

What is SSAS (SQL Server Analysis Services)?

SQL Server Analysis Services (SSAS) is a multidimensional online analytical processing (OLAP) server and an analytics engine used for data mining. It allows IT professionals to break up large volumes of data into more easily analyzed parts. A component of Microsoft SQL Server, it helps enable analysis by organizing data into easily searchable cubes.

View IT Glossary

What is MIB?

MIB is an organized, up-to-date repository of managed objects for identifying and monitoring SNMP network devices.

View IT Glossary

What is CPU usage?

CPU utilization indicates the amount of load handled by individual processor cores to run various programs on a computer.

View IT Glossary

What is a Relational Database?

A relational database allows you to easily find, scan, and sort specific information based on the relationship among the different fields defined within a table.

View IT Glossary

What is MariaDB?

MariaDB is a secure enterprise database system using pluggable storage engines to store and manage different types of data.

View IT Glossary