Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

INF261 > Transaction Management & Concurrency Control (a) Week 1 1 (MO1, Chapter 10-1 to 10-2) > Flashcards

Transaction Management & Concurrency Control (a) Week 1 1 (MO1, Chapter 10-1 to 10-2) Flashcards

1
Q

What is a transaction?

A

Is a logical unit of work that must be completed or entirely aborted; no intermediate states are acceptable.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What does a transaction consist of?

A

Consists of:
SELECT statement
Series of related UPDATE statements
Series of INSERT statements
Combination of SELECT, UPDATE, and INSERT statements

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is a consistent database state?

A

A state in which all data integrity constraints are satisfied, Must begin with the database in a known consistent state to ensure
consistency. Most are formed by two or more database requests

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is a database request?

A

a single SQL statement in an application program or transaction.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What does the DBMS do if the database is in an inconsistent state

A

It rolls back the database to the previous consistent state.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What effects can Improper or incomplete transactions have on
database integrity:

A

Users can define enforceable constraints based on business rules.
Other integrity rules are automatically enforced by the DBMS.
Can you identify more examples?

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What are the individual properties of transactions?

A

Atomicity
Consistency
Isolation
Durability
Serializability

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Explain each of the individual properties of transactions. Consistency

A

A transaction takes place from one consistent state to another

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Explain each of the individual properties of transactions. Atomicity

A

Atomicity: Requres ALL operations of a transaction to be completed if not the transaction is aborted, a transaction is treated as a single, indivisable unit of work.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Explain each of the individual properties of transactions. Isolation

A

Means that data used during the execution of a transaction cant be used by a second transaction until the first one is completed.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Explain each of the individual properties of transactions. Durability

A

Once transactions changes are done they cannot be undone or lost even if the system fails.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Explain each of the individual properties of transactions. Serializability

A

The schedule for the concurrent execution transaction yields consistent results

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How do we manage transactions in SQL

A

The ANSI (American national standards institute has defined standards that govern SQL database transactions. Transaction support is provided by 2 sql statements
1. COMMIT
2 ROLLBACK

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Describe the two SQL statements COMMIT and ROLLBACK

A

Commit: is reached when all statements are permanently recorded within the database, automatically ends the SQL statement.
ROLLBACK : is reached in which case all changes are aborted and the database is rolled back to a consistent state.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q
  • Transaction sequence must continue until one of four events occur:
A

COMMIT statement is reached
* ROLLBACK statement is reached
* End of program is reached
* Program is abnormally terminated

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

The transaction log

A

Keeps track of all transactions that update the database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

DBMS uses the information stored in a log for

A

Recovery requirement triggered by a ROLLBACK statement
* Program’s abnormal termination
* System failure

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Transaction log stores the following ?

A

Record for the begginijng of a transaction
A SQL statement for each transaction component
The names of objects affected by the component
The before and after values for tge fields being updated
Pointers to the previous and next transaction log entries for the same transaction
The ending COMMIT statement.
RSNBAPC

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What are some dangers of the transaction log?

A

diskcrashes, disk full conditions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

CONCURRENCY CONTROL and objectives

A

Coordination of the simultaneous transactions execution in a multiuser
database system:
* Objective: ensures serializability of transactions in a multiuser database
environment.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What are most concurrency controls coordinated to

A

Isloation property.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Why is concurrency control important

A

Important because the simultaneous execution of transactions over a
shared database can create several data integrity and consistency
problems:

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What are the three main problems in several simultaneous executions of transactions

A

Lost Updates
Uncommitted Data
Inconsistent Retrievals

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Lost Updates Learn Tables

A

Lost Updates:
Occurs in two concurrent transactions when:
* Same data element is updated
* One of the updates is lost

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Uncommitted Data (Learn Tables)

A

Occurs when:
* Two transactions are executed concurrently
* First transaction is rolled back after the second transaction has already accessed
uncommitted data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Inconsistent Retrievals (Learn Tables)

A

Occurs when:
* A transaction accesses data before and after one or more other transactions finish working
with such data

27
Q

The scheduler

A

Establishes the order in which concurrent transaction operation are executed. It interleaves the execution of database operations in a specific sequence in order to ensure serializability.

28
Q

Serializable Schedule

A

Interleaved execution of transactions yields the same results as if the transactions were executed in serial order.

29
Q

Concurrency control with locking methods, what is locking

A

Locking is one of the most common techniques used in concurrency control as they facilitate in isolating the data items used in concurrently executing transactions.

30
Q

What is a lock?

A

Guarantees exclusive use of a data item used in concurrently executing transaction. T2 does not have access to the data item that is currently being used by T1.

31
Q

pessimistic locking

A

The use of locks based on the assumption that conflict transactions are likely to occur.

32
Q

Lock manager

A

Responsible for assigning and policing the locks used by the transaction .

33
Q

Lock granularity

A

Indicates the level of lock use.

34
Q

What are the levels that locking can take place at?

A

database, table, page, row and field (attribute)

35
Q

Database level

A

In a database level lock : The entire database is locked, thus preventing the use of any table in the database by transaction T2 while transaction T1 is being executed.

36
Q

Table level

A

The entire table is locked, preventing access to any row by transaction T2 while transaction T1 is using the tables. If a transaction requires access to several tables, each table may be locked. However, two transaction can access the same database as long as they are using different tables.

37
Q

Page level:

A

DBMS locks an entire disk page: a disk page is a directly addressable section of a disk.

38
Q

Field level lock

A

Allows concurrent transaction to access the same row as long as they require the use of different field within that row.

39
Q

What are the types of locks?

A

Binary and shared exclusive

40
Q

Binary lock

A

Has only two states locked (1) or unlocked (0). If an object such as the database, table, page or row is locked by a transaction. No other transactions can use that object.
If an object is unlocked any transaction can lock the object.
As a rule the transaction must unlock the object after its transaction .
LOCK TABLE overrides the default settings.

41
Q

Exclusive lock:

A

Access is reserved specially for the transaction that locked the object.
Used when potential for conflict exists.

42
Q

Shared lock:

A

exists when concurrent transactions are granted read access on the basis of a common lock.
All transactions must be read only.

43
Q

Problems

A

Problems:
transaction
schedule might
not be
serializable
Schedule might
create
deadlocks

44
Q

Mutual exclusive rule

A

A condition in which only one transaction at a time can own an exclusive lock on the same object.

45
Q

Deadlock

A

Occurs when two transactions wait indefinitely for each other.

46
Q

How do you manage deadlocking

A

Serializability which is obtained through a protocol called two-phase locking.

47
Q

Two phase locking

A

Defines how transactions acquire and relinquish locks. It guarantees serializability, but does not PREVENT deadlock.
1. Growing Phase and Shrinking Phase

48
Q

Growing and Shrinking phase

A

Growing: transaction acquires all required locks without unlocking any data.
Shrinking: transaction release all locks and cannot obtain any new locks

49
Q

What rules govern the 2 phase locking protocol

A

Two transactions cannot have conflicting locks
NO unlock operation cannot precede a lock operation in the same transaction
No data is affected until all locks are obtained - that is, until the transaction is in a locked point.

50
Q

What are the three basic techniques to control deadlocks?

A

Deadlock prevention: A transaction requesting a new lock is aborted when there is the possibility that a deadlock can occur. : Prob of deadlocks low
Deadlock detection: The DBMS periodically tests the database for deadlocks. If a deadlock is found the “Victim” transaction is aborted : Prob of deadlocks high
Deadlock avoidance: The transaction must obtain all the locks it needs before it can be executed: Response time not high

51
Q

Concurrency control with timestamping methods, what is time stamping

A

Assigns a global, unique time stamp to each transaction.
* As a result, an explicit order in which transactions are submitted to the DBMS is
produced.

52
Q

What are the two properties of timestamps?

A

Properties:
* Unique: no equal time stamp exist.
* Monotonicity: always increase.

53
Q

Disadvantage of timestamps

A

Each value stored in the database requires two additional stamp fields;
* Increases memory needs;
* Increases the database’s processing overhead;
* Demands a lot of system resources.

54
Q

What are the two schemes to decide which transaction is rolled back and which continues executing?

A

Wait/Die
Wound/Wait

55
Q

Wait/Die scheme

A

If the transaction requesting the lock is older it will wait until the younger transaction is completed
If the transaction requesting the the lock is younger it will die and is rescheduled using the same time stamp.

56
Q

Wound/Wait scheme.

A

The older transaction rolls back the younger transaction.

57
Q

Concurrency control with optimistic methods, optimistic approach?

A

: based on the assumption that the majority of
database operations do not conflict:
Does not require locking or time stamping techniques;
* Transactions are executed without restrictions until it is committed

58
Q

What are the three phases the transaction moves through using the optimistic approach

A

Read
Validation
Write

59
Q

ANSI Levels of Transaction isolation.

A

The ANSI SQL standard (1992) defines transaction management based on transaction isolation levels.

60
Q

What are transaction isolation levels

A

Transaction isolation levels refer to the degree to which transaction data is
“protected or isolated” from other concurrent transactions.
* Transaction isolation levels are described by the type of “reads” that a
transaction allows or not allow.

61
Q

What are the three types of read operations?

A

Dirty read: A transaction can read data that is not yet commited
Non repeatable read A transaction reads a given row at t1 and then reads the same row at t2 yielding different results.
Phantom read: A transaction executes a query at t1then runs the same query at t2 yielding additional rows that satisfy the query

62
Q

What are the four levels of transaction isolation:

A

Read uncommitted: will read uncommitted data from other transactions
Read committed Forces transactions to read only committed data.
Repeatable read: Isolation level that ensures queries return consistent results.
Serializable: Does not allow dirty, non repeatable or phantom reads.

63
Q

Database recovery management.

A
64
Q
A

INF261 (3 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions