What is Database Concurrency?

• Database Concurrency Definition

  Database Concurrency Definition

  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.
• Concurrent access to a database

  Concurrent access to a database

  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.
• Common database concurrency problems

  Common database concurrency problems

  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.
• Types of concurrency database control techniques

  Types of concurrency database control techniques

  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.
• Concurrency in database performance monitoring

  Concurrency in database performance monitoring

  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.