Distributed data concepts

Question 1

what is a transaction

Answer 1

a logical unit of work which either completes in its entirety or not at all, its aim is to keep the database consistant

Question 2

what do transactions end with

Answer 2

COMMIT – commit the changes and successfully end
ROLLBACK – an error has occurred so undo everything from the transaction
Programmatic SQL – normal termination of the program is a COMMIT
Programmatic SQL – abnormal/error termination is a ROLLBACK

Question 3

ACID properties of a transaction

Answer 3

Atomicity, Consistency, Isolation, Durability

Question 4

What does Atomicity mean

Answer 4

completes in its entirety or not at all

Question 5

what does consistency mean

Answer 5

the database must be in a consistent state - a DBMS or application developers need to ensure this

Question 6

what does Isolation mean

Answer 6

each transaction executes independently of others

Question 7

what does durability mean

Answer 7

changes made by a transaction must persist - recovery systems must ensure this

Question 8

OLTP

Answer 8

Online transaction processing

Quick real-time access to data to read or modify it

Question 9

OLAP

Answer 9

Online Analytical Processing

involves fewer, more intensive transactions than OLTP

Question 10

Problems with transactions

Answer 10

The lost update problem
The dirty read or uncommitted dependency problem
the inconsistent analysis problem

Question 11

The lost update problem

Answer 11

an update done to a data item by a transaction is lost as it is overwritten by the update done by another transaction

Question 12

The dirty read problem (uncommitted dependancy problem)

Answer 12

A dirty read occurs when a transaction reads data that has not yet been committed

Question 13

The inconsistent analysis problem

Answer 13

when one user is reading the several pieces of data when another is updating them data the user reading the data may end up with a mix of old and new values

Question 14

How to fix the problems with transactions

Answer 14

making all transactions run serially, this causes performance issues if only one transaction is run at once
read operations can be run in parallel
if a transaction is writing to one part of the database, it can still access other parts which arent affected by the update

Question 15

what is locking

Answer 15

transactions lock part of the database before updating

Question 16

what is a shared lock

Answer 16

the data item can only be read by the transaction

Question 17

what is an exclusive lock

Answer 17

read or write

Question 18

what is a deadlock

Answer 18

it is possible that 2 transactions are each waiting for the other to release a lock

Question 19

how can a deadlock be resolved

Answer 19

timeouts - transaction rolls back after a certain amount of time
deadlock detection - rollback the transaction which would cost the least to stop
deadlock prevention - try look for the problem in advance (not common as is tricky to do)

Question 20

what is a distributed database

Answer 20

the data is stored in different physical locations but the DBMS makes it invisible to the end-user
this causes more performance issues but has more storage

Question 21

CAP theorem

Answer 21

Consistency
Availability
Partition tolerance

Question 22

Consistency(CAP)

Answer 22

the database must be in a consistent state, transactions ensure this by rolling back if an error occurs when in an inconsistent state

Question 23

availability

Answer 23

every query request gets a response

Question 24

partition tolerance

Answer 24

distributed databases can cope with network failures / network delays

Question 25

Brewers theorem

Answer 25

Can only expect to have 2 of these CAP aspects at any given time, partition tolerance is a necessity, therefore designers have to trade-off between consistency and availability

Question 26

logging

Answer 26

the database keeps a log of all transactions made, including before and after values,

Question 27

checkpoints

Answer 27

these are made periodically noting which transactions are running, which are committed, etc. transactions are suspended as the checkpoint is made.
All shown as committed are fully complete and written to the disk

Question 28

archiving

Answer 28

databases are regularly archived by its administrator into offline storage

Question 29

what issues can a database be suddenly hit by

Answer 29

Sudden crash or loss of power to servers
Hardware problems (e.g. corrupt/broken disks)
Flood/fire, etc. (in the server room)
Accidental issues (e.g. bug in a program accessing the DB, the user doing something silly)
Malicious damage

Question 30

how can database be recovered

Answer 30

using the log file, as it shows what transactions have been made.

any incomplete transactions need to be undone

completed transactions will need to be redone in case the DBMS had not finished writing the changes to the disk

transactions marked as complete at the last checkpoint don’t need to be redone

Depending on the problem, the database may need to be reloaded from the previous archive before the log file entries are redone

Question 31

where should the log file be stored/archived

Answer 31

on a different storage medium to the database, archive has to be stored somewhere other than the server location