Goals

Understand the difference between concurrency and parallelism. Learn how database transactions can be used to ensure ACID properties and how different isolation levels can help handle lost updates, dirty reads, non-repeatable reads, and phantom reads. Understand the difference between optimistic and pessimistic locking and how different locking modes can be used to access data concurrently in a safe manner. Analyze a bank transaction for its atomicity and isolation properties, and try to obtain an exclusive lock to update data.

Examples

• Updating two bank accounts as a single logical unit
• Handling failures during the update process of multiple bank accounts

Exercises

• Test atomic updates by crashing a PostgreSQL transaction
• Inspect data which is modified by another PostgreSQL transaction
• Update and lock data which is modified by another PostgreSQL transaction

Introduction

Context

Concurrency

Databases are often used by thousands of users at the same time. Users are accessing the database in a concurrent fashion when they want to read or write the same records. To illustrate concurrent behaviour, we can ask the question: How many people can walk through a single door at exactly the same time?

Concurrency (computer science)

It is physically impossible for two users to read or write exactly the same bit on a disk at precisely the same time (harrington16 ).

Parallelism

When users are accessing totally independent records, they access the database in a parallel fashion. To illustrate parallel behaviour, we can ask the question: How many people can walk through five doors at the same time?

Data parallelism

Consistency and Integrity

Concurrency control in databases is concerned with logical consistency and data integrity.

Both properties need to be ensured in the presence of failures of both hardware and software components and with highly concurrent access (connolly15 ). For example, even if a database crashes during a write, a bank transaction is not allowed to be only updated on one of the two accounts, only.

Millions of websites offline after fire at French cloud services firm

Transactions