Distributed Database Concepts and Transactions

A set of vocabulary flashcards covering key concepts of distributed databases, transaction properties (ACID), locking mechanisms, the CAP theorem, and indexing techniques.

Transaction

A logical unit of work which completes in its entirety or not at all, aimed at keeping the database consistent.

• Aim to to keep database consistent

• Can contain any number of database operations using SQL

COMMIT

An operation that successfully ends a transaction and commits the changes made to the database.

ROLLBACK

An operation used when an error occurs to undo all actions performed within a transaction.

Atomicity

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

Consistency (ACID)

The ACID property requiring that the database must be in a consistent state after a transaction.

Isolation

The ACID property ensuring that each transaction executes independently of others.

Durability

The ACID property ensuring that changes made by a transaction must persist, often managed by a recovery system.

OLTP

Online Transaction Processing; systems providing quick, real-time access to data for reading or modification.

OLAP

Online Analytical Processing; an alternative to OLTP that involves fewer but more intensive transactions.

Shared lock

A read-only lock that allows transactions to read parts of the database.

Exclusive lock

A lock that allows a transaction both read and write access to a part of the database.

Two phase Locking (2PL)

A locking rule where unlock operations are only performed after all locks for a transaction have been acquired.

Deadlock

A situation where two transactions are each waiting for the other to release a lock, potentially resolved by timeouts or rollback.

Deadlock detection

The process of rolling back the transaction which would cost the least amount to stop when a deadlock is identified.

Consistency (CAP)

The aspect of the CAP Theorem ensuring the database is in a consistent state, achieved by rolling back if an error occurs.

Availability (CAP)

The aspect of the CAP Theorem ensuring that every query request gets a response.

Partition Tolerance (CAP)

The aspect of the CAP Theorem stating a distributed database can cope with network failures or delays between nodes.

CAP Theorem

A theory stating that a distributed database can only expect to have two of the three aspects: Consistency, Availability, and Partition Tolerance.

Checkpoints

Periodic logs noting which transactions are running or committed; transactions are suspended while data is written to the disk.

Clustered index

An index that relates to the physical order of data on the disk, with only one allowed per database table.

Non-clustered index

An index containing a row locator or pointer to the data row rather than storing the data itself.

Problems with transactions

• Lost update problem

• Dirty Read

• Inconsistent Analysis

• Making all transactions run serially will fix this but there are performance issues if only one transactions can ever run at once

• Read operation can be run in parallel

• if a transaction is writing to one part of the database, can still access other parts not affected by the update

Locking

To fix issues with transactions DBMS needs a serialisable schedule

• Locking - transactions lock part of DB before updating

Lost update problem

Two users are operating on same expense system, U1 reads balance has 20000, but T2 changes balance and write it, then T1 still thinks balance is unchanged and changes and write there own balance so T2 changes are lost

Dirty Read

T1 reads value as 310 which was changed by T2 but then an error causes T2 to rollback to original value of 300, so T1 has an inconsistent value

Inconsistent Analysis

If T2 updates rows mid transaction, T1 can read a mix of old and new values, which would cause errors in the transactions

Indexing

Without an index a DBMS would have to search whole tables, so it can use an index to jump into middle of data without searching through all. Set up by database administrator.

Deadlock Prevention

Look out for a problem in advance - not common as its difficult

Logging and Archiving

Databases keep a log of all transactions in order they happened, includes before and after values. Databases are also regularly archived by its administrators onto offline storage