Introduction, Transactions, Concurrency Control and Recovery

Question 1

What are database transactions

Answer 1

query
update
transaction

Question 2

What is a query?

Answer 2

a query is a request to the DBMS to retrieve data from the database

Question 3

What is a update?

Answer 3

an update requests the DBMS to insert, delete or modify existing data

Question 4

What is a transaction?

Answer 4

a transaction is a unit of work performed against a database, and treated in a coherent and reliable way independent of other transactions.

transactions are logical groupings of query and update requests to perform a task

Question 5

Transactions management only applies to the DML. why?

Answer 5

this encompasses the four basic functions of persistent storage, create, read, update and delete (CRUD)

Question 6

FACTS about transactions

Answer 6

each SQL command run is treated as a transaction

Question 7

How do you define a multi step transaction

Answer 7

START TRANSACTION or BEGIN starts a new transaction

COMMIT to commit (Save) the current transaction making its changes permanent

usually used to ensure;
complex data integrity

Question 8

transaction states are?

Answer 8

two outcomes;
SUCCESS
FAILURE

Question 9

Describe Failure

Answer 9

transaction aborts, and database must be resorted to consistent state before it started. such a transaction is rolled back or undone.

Question 10

Describe Success

Answer 10

transaction commits and database reaches a new consistent state

a committed transaction cannot be ABORTED!

Question 11

Transaction managers are components of a DBMS.

describe the two main purposes.

Answer 11

1. to provide reliable units of work

2. to provide isolation between programs accessing a database concurrently.

Question 12

to ensure these goals, DB transactions must be:

Answer 12

atomic
consistent
isolated
durable

Question 13

Explain atomicity

Answer 13

either all the operations associated with a transaction happen or none of them happen

Question 14

Explain consistency

Answer 14

a transaction must transform the database from one consistent state to another, ensuring all predefined rules are adhered to

e.g. foreign keys

Question 15

Explain Isolation

Answer 15

the result of the execution of concurrent

transactions is the same as if transactions were executed serially

Question 16

Explain Durability

Answer 16

the effects of completed transactions become permanent surviving any subsequent failures

Question 17

Why is transaction manager necessary/

Answer 17

Concurrency

Failure Tolerance

Question 18

what is concurrency?

Answer 18

we want to allow multiple OPERATIONS to be performed at the SAME TIME so as to increase db THROUGHPUT

support by multi-threading, multi-processors

Question 19

What is failure tolerance

Answer 19

we want the DB to be resilient from system crashes due to hardware or software failures

Question 20

What would happen if DBMS has poor transaction management…?

Answer 20

lost updates
uncommitted dependencies
inconsistent analysis

Question 21

Lost updates are?

Answer 21

an apparently successful completed

update by one user can be overridden by another user

Question 22

what are uncommitted dependencies?

Answer 22

one transaction is allowed to see intermediate results of another transaction
before it has committed

Question 23

what are inconsistent analysis?

Answer 23

a transaction reads several values from the DB but a second transaction updates
some of them during the execution of the first

Question 24

A solution to these issues transactions are SERIAL schedules. explain what this is

Answer 24

allows only one transaction to execute at at time

Question 25

what is a non-serial schedule?

Answer 25

is where the operations from a set of concurrent transactions are interleaved

Question 26

what is serialisability?

Answer 26

if a set of transactions execute concurrently, we say that the non-serial schedule is correct if it produces the same results as some serial execution (this schedule is called serialisable)

Question 27

how to recover schedules?

Answer 27

for each pair of transactions Ta, and TB, if Tb reads a data item previously written by Ta, then the commit operation of Ta, must precede the commit operation of Tb

Question 28

What are concurrency control schemes?

Answer 28

a trade-off between the amount of concurrency they allow and the amount of overhead that they incur

Question 29

what is a concurrency control

Answer 29

is the process of managing simultaneous operations on the DB without having them interfere with one another