Distributed Database Concepts and Transactions

Vocabulary flashcards covering the fundamentals of database transactions, concurrency issues, ACID properties, CAP Theorem, and indexing.

Transaction

A logical unit of work that completes successfully in its entirety, or not at all.

COMMIT

A command that commits all changes made during a transaction and ends it.

ROLLBACK

An action taken when an error occurs, causing all changes from that transaction to be undone.

Atomicity

The ACID property ensuring a transaction completes in its entirety or not at all.

Consistency (ACID)

The ACID property ensuring the database is in a consistent state, which is handled by developers.

Isolation

The ACID property where each transaction executes independently from others.

Durability

The ACID property ensuring changes made by a transaction persist, which is managed by the recovery system.

Lost update problem

Occurs when two transactions (T1, T2) read the same data and both update it, then T1 overwrites T2 without incorporating T2’s changes, causing data inconsistency.

Dirty read problem

Occurs when T1 reads data modified by T2 that hasn't been committed; if T2 rolls back due to an error, T1 will be reading invalid data, leading to data inconsistency

Inconsistent analysis problem

Occurs when T1 reads data while T2 updates the database, leading to incorrect summaries, calculations, and analysis. Can be fixed by making T1 and T2 run in series.

Shared lock

A type of lock used for read-only operations.

Exclusive lock

A type of lock used for both read and write operations.

Two phase locking (2PL)

A protocol that only unlocks operations after all locks have been acquired.

Deadlock

A situation where two transactions are each waiting for the other to release a lock.

Timeouts

A deadlock resolution method where a transaction rolls back after a set period of time.

Deadlock detection

A deadlock resolution method that rolls back whichever transaction would cost the least to stop.

Deadlock prevention

A method of looking for potential deadlock problems in advance; it is not very common because it is tricky.

CAP Theorem / Brewer’s Theorem

States that a distributed database can only have two of the following at one time: Consistency, Availability, and Partition tolerance.

Consistency (CAP)

The database remains in a consistent state, which is ensured via rolling back if errors occur whenever the database is in an inconsistent state

Availability (CAP)

Every query request is guaranteed to get a response

Partition tolerance (CAP)

The ability of distributed databases to cope with network failures; this is considered non-negotiable in the CAP Theorem.

Checkpoints

Notes in a log file that specify which transactions are running or committed while putting the transaction in suspension.

B-Tree

The specific data structure used by MYSQL to store indexes.

Cluster index

An index relating to how data is stored on disk; there can only be one per database table.

Non-clustered index

An index that contains a pointer to the data row called the row locator.

Query optimisation

The process where the DBMS makes decisions that will impact the overall performance of query processing.