Get documents about "Relational Algebra - Download Now PowerPoint
Document Sample
UNIT – 4
DBMS
Prepared By: Rohit Miri
Asst Prof.(C.S.E Dept)
Dr. C. V. Raman University.
Query Processing
Query Processing:
The Size of the database is no longer a problem because we can store the huge amount of
data in a small amount of space and being available in pretty cheap. Ex pen drive.
The problem today is that how to retrieve the data, it is not just retrieve any data, retrieving
any data whatever is required . The crucial technology for retrieving a data in quick period
of time from the database.
The query processing means for a given user query we process a query is such a way that
result will come out extremely fast.
The query processing means for a given user query we process a query is such a way that
result will come out extremely fast.
If you give query in google for web search and if result will come after two days the no
body will use this google search. But google responses your request of megabyte size
within very quick period of time. This is only possible with query processing.
Query Processing
Query Processing:
Efficient Query processing is crucial for good for good or even effective operations of a
database.
Suppose the table has 2 megabytes and we select a query that is based on two tables and
each of one contains one gigabytes of data. There is bad execution plan if we compute the
Cartesian product of these two tables.
If we compute the Cartesian product of one gigabyte table then we will get the result of
1GB * 1GB= 1016 bytes.
If we use equi -join or natural join the we get the response with quick period of time.
Factors Effects The Query Processing:
Query processing depends on variety of factor, not every thing is under the control of
DBMS.
Whether the storage media is fragmented or defragmented.
Fragmented Storage: Contiguous block on the storage media belongs to different files.
Query Processing
Factors Effects The Query Processing:
Query processing depends on variety of factor, not every thing is under the control of
DBMS.
Whether the storage media is fragmented or defragmented.
Fragmented Storage: Contiguous block on the storage media belongs to different files.
If we scan through relation like select statement these block are distributed all across the
storage devices. The response time will increase for query.
Incorrect or insufficient information can result in vastly ineffective plan. If a query
estimation plan estimate that the size of the table is few kilobytes but the size of the table is
actually gigabytes then the entire strategy will be change when the scale of problem is
changed.i.e few kilobytes can stored in main memory but few gigabytes will not.
Catalogue: It is help in estimating several kind of information
Query Processing
Factors Effects The Query Processing:
Catalogue: It is help in estimating several kind of information like ;
The size of tuple in bytes.
The number of tuples in database.
The number of distinct values in database.
It decides the query execution plan.
Database design: Normalization and schema
DBMS design: Query Processing and Query optimization.
Query Processing
Basic Steps(Stages) in Query Processing
1) The scanning, parsing, and validating module produces an internal representation
of the query. EX
The first action that the system must perform is to translate the query into its
internal form , which (for relational database system) is usually based on the
relational algebra. In the process of generating the internal form of the query, the
parser checks the syntax of the users query , verifies that the relations names
appearing in the query are names of relational database, and so on. If the query is
expressed in terms of view, the parser replaces all references to view name with
the relational algebra expression to compute the view.
Query Processing
2) The query optimizer module devises an execution plan which is the execution
strategy to retrieve the result of the query from the database files. A query
typically has many possible execution strategies differing in performance, and the
process of choosing a reasonably efficient one is known as query optimization.
Query optimization is beyond this course.
3) The code generator generates the code to execute the plan.
4) The runtime database processor runs the generated code to produce
the query result.
Phases of Query Processing
Marina G. Erechtchoukova 8
Query Processing
Query Execution Plan:
A sequence of primitive operations that can be used to evaluate a query in a query
execution plan or query evaluation plan.
The query execution engines takes a query evaluation plans and executes that plan and
returns the answer to the query.
The different evaluation plans for a given query can have different costs.
Query execution plan(Physical algebra)
Query execution plan is functional program with evaluation scan operator
primitives.
Tuple scan operator
Tuple selection operator
Various index scan operator.
Sort operator
Query Optimization
Query optimization :
The query optimizer is the component of a database management system that attempts to
determine the most efficient way to execute a query. The optimizer considers the
possible query plans for a given input query, and attempts to determine which of those
plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to
each possible query plan, and choose the plan with the smallest cost. Costs are used to
estimate the runtime cost of evaluating the query, in terms of the number of I/O
operations required, the CPU requirements, and other factors determined from the data
dictionary. The set of query plans examined is formed by examining the possible access
paths.
Optimization criteria:
– Reduce total execution time of the query:
• Minimize the sum of the execution times of all individual operations
• Reduce the number of disk access
– Reduce response time of the query:
• Maximize parallel operations
Query Optimization
Structure of the Query Optimizer
• These are only some of the many query-processing strategies used in database systems.
• Most systems only implement a few strategies.
• Some systems make a heuristic guess of a good strategy, in order to minimize the number of
strategies to be considered.
• Then the optimizer considers every possible strategy, but quits as soon as it determines that the cost
is greater than the best previously considered strategy.
• To simplify the strategy selection task, a query may be split into several sub-queries.
• This simplifies strategy selection and permits recognition of common sub-queries (no need to
compute them twice).
• Examination of a query for common subqueries and the estimation of the cost of a large number of
strategies impose a substantial overhead on query processing.
• However, this is usually more than offset by savings at query execution time.
• Therefore, most commercial systems include relatively sophisticated optimizers.
Query Interpretation
Query Interpretation:
Why do we need to interpret a query?
• A high-level relational query is generally non-procedural in nature.
• It says ``what'', rather than ``how'' to find it.
• When a query is presented to the system, it is useful to find an efficient method of finding
the answer, using the existing database structure.
• Usually worthwhile for the system to spend some time on strategy selection.
• Typically can be done using information in main memory, with little or no disk access.
• Execution of the query will require disk accesses.
• Transfer of data from disk is slow, relative to the speed of main memory and the CPU.
Table Scan
Table scan:
this is a term used to describe an event that occurs when you search for data in a table,
and your query either doesn't take advantage of an existing index, or there is no index on
the table. Generally, a table scan is not a good thing to have happening on a frequent basis
in your database. There are some instances where it's not a bad thing, and I will try now to
explain what it is, why it's happening, and when its not that bad of an occurence.
Fill Factor
Fill Factor:
Specifies a percentage that indicates how full the Database Engine should make the leaf
level of each index page during index creation or alteration. fillfactormust be an integer
value from 1 to 100. The default is 0.
Whenever you create a new index in SQL Server or rebuild an existing index, you can
specify several optional parameters – one of such parameters is the fill factor. This
parameter controls the percentage of free space in the leaf level of the index pages are
filled when they are created. So, a fill factor value of 100% or 0% means that each index
page is 100% full, a fill factor of 90% means that each index page is 90% full and 10% of
each leaf level page will be empty thus providing space for future index expansion as DML
statements occur. The default is 0% which is the same as 100%.
Indexes
• A data structure that allows the DBMS to locate particular records
• Index files are not required but very helpful
• Index files can be ordered by the values of indexing fields
Multiple index access:
Typical SQL database will allow you to create index using one or more
attributes and you can create multiple indexes for same table.
Marina G. Erechtchoukova 15
Retrieval Algorithms
• Files without indexes:
– Records are selected by scanning data files
• Indexed files:
– Matching selection condition
– Records are selected by scanning index files and finding corresponding
blocks in data files
Marina G. Erechtchoukova 16
Search Space
• Collection of possible execution strategies for a query
• Strategies can use:
– Different join ordering
– Different selection methods
– Different join methods
• Enumeration algorithm – an algorithm to determine an optimal strategy
from the search space
Marina G. Erechtchoukova 17
TRANSACTION
Transaction Control:
A transaction is a logical unit of work that contains one or more SQL statements.
A transaction is an atomic unit; the effects of all the SQL statements in a transaction can be either all committed
(applied to the database) or all rolled back (undone from the database).
A transaction is called atomic as the data base modifications brought about by the SQL statements that constitute a
transaction can be
Either made permanent to the db.
Or undone from the database.
The changes made to a table using insert, update or delete statement are not permanent till a transaction is nor
marked as complete
During a session, a transaction begins when the first SQL command (DDL or DML)
encountered and ends one of the following occurs.
DDL command encountered.
Commit/rollback statement encountered
Logging off from the session
System failure
TRANSACTION
Transaction Control:
Committing a transaction makes permanent the changes resulting from all successful
SQL statements in a transaction.
The auto commit environment variable can be set to execute automatically whenever an
insert , update, or delete statement is executed.
When a transaction is committed, the following events take place.
The changes made by a current transaction are made permanent.
The locks acquired by the transaction are released.
The transaction is marked as complete.
Syntax Commit
The auto commit environment variable can be set to execute automatically whenever an
insert , update, or delete statement is executed.
TRANSACTION
Rolling Back a Transaction:
Changes made to the database without COMMIT may be abandoned using the
ROLLBACK statement.
When a transaction is rolled back, it is as if the transaction never occurred.
When a transaction is rolled back, the following events take place.
• Any changes made to the database is undone.
• The locks acquired by the transaction are released.
• The transaction is ended.
Syntax:
ROLLBACK [TO SAVEPOINT <savepoint name>];
Savepoints:
Savepoints name and mark the current point in the processing of a transaction.
Savepoint allow to undo part of a transaction instead of the whole transaction.
The no of active savepoint per user session is 255.
If an error is made in between , it is not necessary to restart the transaction from the beginning.
TRANSACTION
Savepoint:
Syntax:
Savepoint <Savepoint name>;
Example:
Insert into emp (empno, ename,sal) values (1005,’amit’,’sal’)
Savepoint spt1;
Update emp set sal=sal + 1000 where dettno=10;
Rollback to spt1
:
TRANSACTION
Savepoint:
Syntax:
Savepoint <Savepoint name>;
Example:
Insert into emp (empno, ename,sal) values (1005,’amit’,’sal’)
Savepoint spt1;
Update emp set sal=sal + 1000 where dettno=10;
Rollback to spt1
:
TRANSACTION
A transaction is a logical unit of work that contains one or more SQL statements.
A transaction is an atomic unit; the effects of all the SQL statements in a transaction can
be either all committed (applied to the database) or all rolled back (undone from the
database).
Transactions access data using two operations:
1.Read(X): Which transfers the data item(X) from the database to a local buffer belonging to
the transaction that executed the read operation.
2.Write(X): Which transfers the data item(X) from the local buffer of the transaction that
executed the write back to the database.
Properties of Transaction:
Transactions have four basic properties, which are called ACID properties. These
properties are closely related to each other.
TRANSACTION
Properties of Transaction:
A-> Atomicity
C->Consistency
I-> Isolation
D-> Durability
Atomicity:
A transaction is an atomic unit of processing. It is either performed completely or not
performed at all. At the end of a transaction the updates made by the transaction will
be accessible to other transaction.
Consistency:
The consistency property of transaction implies that if database was in a consistent
state before the initiation of a transaction, then at the end of transactions the database
will also be in consistent state.
TRANSACTION
Properties of Transaction:
Isolation:
The isolation property of transaction indicates that the steps or operations performed
by a transaction should be isolated from the other transaction, i.e the execution of a
transaction should not be interfered with by any transaction being executed
concurrently.
Durability:
The changes applied to the database by a committed transaction must persist in the
database. These changes must not be lost because of any failure.
TRANSACTION
Acid properties example:
In a banking system there are many accounts are there and a set of transactions that
access and updates those accounts. For the time we assume that the database
permanently resides on disk, but that some portion of it is temporarily residing in main
memory.
The following two operations are accomplished to access the database.
1.Read(X): Which transfers the data item(X) from the database to a local buffer
belonging to
the transaction that executed the read operation.
2.Write(X): Which transfers the data item(X) from the local buffer of the transaction that
executed the write back to the database.
In real database system, the write operation does not necessarily result in the
immediate update of the data on disk, the write operation may be temporarily stored in
memory and executed on the disk later. However we shall assume that the write
operation updates the database immediately.
TRANSACTION
Acid properties example:
Ti be a transaction that transfer $50 from account A to account B. This is defined as:
Ti : read(A);
A: = A – 50;
Write (A);
Read (B);
B: = B + 50;
Write (B);
Applying the ACID properties:
1.Atomicity:
Let A= $1000;
B=$2000;
If failure occurs after write (A) operation was executed but buffer write (B) operation
was executed. In this case , the value of account A and B reflected in the database are
$950 and $2000. Here we destroy $50 as the result of failure and sum A+B not
preserved.
TRANSACTION
Acid properties example:
1.Atomicity:
Let A= $1000;
B=$2000;
If failure occurs after write (A) operation was executed but buffer write (B)
operation was executed. In this case , the value of account A and B reflected in the
database are $950 and $2000. Here we destroy $50 as the result of failure and sum
A+B not preserved.
To ensure atomicity, the database system keeps back(on disk) of the old value of any
data on which a transaction performs a write, and, if the transaction does not complete
its execution, the old values are restored to make it appear as through the transactions
never executed. Atomicity ensures by transaction management component.
TRANSACTION
Acid properties example:
2.Consistency:
Here consistency required is that the sum of A and B be unchanged by the execution
of transaction.
3.Durability:
The durability property guarantees not, once a transaction completes successfully, all
updates that carried out on the database persist, even if there is a system failure after
the transaction completes execution. To ensures durability:
1. The updates carried out by the transaction have been written to disk before the
transaction completes.
2. Information about the updates carried out by the transaction and written to disk is
sufficient to enable the database to reconstruct the updates when database system is
restarted after failure. Durability ensures by recovery management component,
.
TRANSACTION
Acid properties example:
4.Isolation:
If consistency and atomicity properties are ensured for each transaction, if several
transactions are executed concurrently them operations may interleave some
undesirable way resulting in an inconsistent state.Ex
Suppose Ramesh has a account of A and has Rs 3000 in his account. He has given a
check Rs 2000 to his friend ramu.
Ramesh went to ATM machine and checked his balance and got Rs 3000 balance after
checking. Now he wants to withdraw Rs 2000 due to some urgent work. Now he is
withdrawing Rs 2000 from his account and at the same time ramu is also withdrawing
money Rs 2000 from ramesh account(Check).
So one of the transaction has to failed to maintain the consistency.
Because Ramesh withdrawal Rs 2000 and Ramu withdrawal Rs 2000 from same
account A at same time. Total money withdrawal from account A is Rs 4000 but in
account A the total money is only Rs 3000.
TRANSACTION
Transaction State:
Partially Committed
committed
Active
Aborted
Failed
State Diagram of a Transaction
TRANSACTION
Transaction State:
Active:
It is the initial state; the transaction stays in this state while it is executing.
Partially Committed:
After the final statement has been executed.
Aborted:
After the transaction has been rolled back and the database has been restored to its state
prior to the start of the transaction.
Committed:
A transaction that completes its execution successfully is said to be committed.
Failed:
After the discovery that normal execution can no longer proceed.
TRANSACTION
Types of failures:
System crashes, resulting in loss of main memory.
Media failures, resulting in loss of parts of secondary storage.(hard disk/cpu failed)
Application software errors.()
Natural physical disasters.()
Carelessness or unintentional destruction of data or facilities.
Wrong Input.: ( Drop a table my mistake)
Other causes like fire, flood.
Statement Failure:(Giving the wrong SQL syntax/ insufficient balance in account/ ATM)
Transaction
Serilaizabilty of Schedules
When several transactions are executing concurrently then the order of
execution of various instructions is known as a schedule.
If the actions of different transactions are not interleaved--that is,
transactions are executed from start to finish, one by one -- we call the
schedule a serial schedule.
The concept of serializability of schedules is used to identify which
schedules are connect when transaction executions have interleaving of
their operations in the schedules.
L22 34
Transaction
Serilaizabilty of Schedules:
Types of schedules:
1. Serial schedules.
2. Non-serial Schedules.
3. Conflict schedules.
4. View Schedules.
Serial Schedule:
Two schedules A and B are called serial schedule if the operations of each transaction
are executed consecutively, without any interleaved operations from the other
transaction. In a serial schedule, entire transaction are performed in serial order T1 and
then T2 or T2 and then T1.
Non-Serial Schedule:
The two schedules C and D are called non serial schedule if the operations of each
transaction are executed non-consecutively with interleaved operations from the other
transaction.
L22 35
Transaction
Serial schedule
T1 T2 T1 T2
Read-item (X) Read-item (X)
X : X- N; X : X + M;
Write-item (X) Write-item (X)
Read-item (Y);
Y : Y + N;
Read-item (X)
Write-item (Y);
X : X- N;
Write-item (X)
Read-item (X) Read-item (Y);
X : X+ M; Y : Y - N;
Write-item (X) Write-item (Y);
Schedule A Schedule B
L22 36
Transaction
Non-Serial Schedule
T1 T2 T1 T2
Read-item (X)
X : X- N;
Read-item (X)
X : X+ M;
Write-item (X)
Read-item (Y);
Write-item (X)
Y : Y + N;
Write-item (Y);
L22 37
Transaction
Example Serilaizabilty of
Schedules
T1 T2 T1
Read (A)
T=A * .1; We can also swapped a read operation
A=A-T;
Write(A)
Read (B) Continue between two transaction
B=B+T; swapping After swapping there is conflict
Write (B) between the transaction.
Read (A)
A=A-50;
Write (A)
Read (B)
B=B+50;
Swapped,
Write (B)
interleave
L22 38
Transaction
Conflict schedules:
Let the instruction I and J belonging to the transaction T1 and T2 and to be executed
consecutive by the DBMS.
a) I and J can be swapped in their execution order if I and J refer to different data
elements.
b) I and J can be swapped in their execution order if I and J refer to different data
elements and both perform a read operation only.
I and J are said to be conflict ,if I and J belong to different transactions on same data
element and at least one of then is a write operations.
If a schedule S can be transformed to another S” swapping non conflicting instruction
S, Then S and S” are said to be conflict equivalent.
L22 39
TRANSACTION
A schedule S is said to be conflict serializable if it is conflict equivalent to some
serial schedule. Example
T1 T2 T1
Read (A)
T=A * .1; We can also swapped a read operation
A=A-T;
Write(A)
Read (B) Continue between two transaction
B=B+T; swapping After swapping there is conflict
Write (B) between the transaction.
Read (A)
A=A-50;
Write (A)
Read (B)
B=B+50;
Swapped,
Write (B)
interleave
L22 40
TRANSACTION
Serilaizabilty of Schedules
Conflict schedules:
The graph which contains no cycle is called conflict serializable schedules.
If the precedence graph for S has a cycle, then schedule S is not conflict serializable.
If graph contains no cycle, then the schedule s is conflict serializable.
Testing of serializability:
For testing of serializabilty the simple and efficient method is to construct a directed
graph, called a precedence graph from S.
G=(V,E)
The set of vertices consists of all the transactions participating in the schedule. The set
of edges consists of all edges Ti->Tj, for which one of three conditions holds:
Ti executes write (Q) before Tj executes read(Q).
Ti executes read (Q) before Tj executes write(Q).
Ti executes write (Q) before Tj executes write(Q).
For example
L22 41
TRANSACTION
Interleaved Execution
Two actions on the same data object conflict if at least one of them is a write.
Three anomalous situations can occur when the actions of two transactions T1 and
T2 conflict with each other.
Reading uncommitted data (WR Conflicts):
A transaction T2 could read a database object A that has been modified by another
transaction T1, which has not yet committed.
Unrepeatable reads (RW Conflicts):
A transaction T2 could change the value of an object A that has been read by a
transaction T1, while T1 is still is progress.
Overwriting uncommitted data (WW Conflict):
A transaction T2 could overwrite the value of an object A, which has already been
modified by a transaction T1, while T1 is still in progress.
L22 42
TRANSACTION
Example-1 Testing of serializability:
T1 Read(A)
T2
A : A- 50;
Rule
Read(A)
WR
Temp :=A * .1;
RW
A=A-Temp;
WW
Write (A);
Read(B)
Write (A);
Read(B)
B : B+ 50;
Write (B);
B:=B + Temp
T2
Write (B) T1
OUTPUT
Since the graph contains the cycle
hence it is not conflict serializable L22 43
TRANSACTION
Example-2
Testing of serializability
T1 T2 T3 Rule
WR
Read(X) RW
Write(X) WW
Write(X)
Write(X)
T2
T1
OUTPUT
Since the graph contains the cycle
T3
hence it is not conflict serializable L22 44
TRANSACTION
Example-3
Testing of serializability
T1 T2 T3 Rule
WR
R1(X) RW
R3(X) WW
W1(X)
R2(X)
W3(X)
(X) T2
T1
OUTPUT (X) (X)
(X)
Since the graph contains the cycle
T3
hence it is not conflict serializable L22 45
TRANSACTION
Example-4
Testing of serializability
T1 T2 T3 Rule
WR
R1(X) RW
R3(X) WW
W3(X)
W1(X)
R2(X)
(X) T2
T1
OUTPUT (X)
(X) (X)
Since the graph contains the cycle
T3
hence it is not conflict serializable L22 46
TRANSACTION
Testing of serializability
Example-5
T1 T2 T3 Rule
R3(X) WR
RW
R2(X) WW
W3(X)
R1(X)
W1(X)
(X) T2
T1
OUTPUT (X) (X)
Since the graph contains the cycle
T3
hence it is not conflict serializable L22 47
TRANSACTION
Example-6
Testing of serializability
T1 T2 T3
R1(X) Rule
R2(Z) WR
RW
R1(Z) WW
R3(X)
R3(Y)
W3(X)
R2(Y)
W2(Z)
W2(Y)
(Z)
T2
T1 (Y)
OUTPUT T3
(X)
Since the graph contains no cycle hence
it is conflict serializable L22 48
Transaction
View Serializability
If S a schedule, then S” is a view equivalent schedule if it satisfies the following
condition:-
for each data item Q, if a transaction T1 reads the initial value of Q in S (serial
schedule (S)) then it should read the initial value in S” also.
(first read operation=it also start with read operation in S”)
It schedule S, for each data item Q, if write (Q) of Tj precedes Read (Q) of Ti,it
should be the same in S”(write operation first then read operation will be done).
The same transaction that perform the final write (Q) in S, should perform also in
S”.
for example :- Consider the three transactions T1, T2 and T3
T1 T2 T3
Read(Q) Write(Q) Write(Q)
Write(Q)
Transaction
A viewed equivalent schedule
Hear all activities of T1 is not performed
T1 : Read(Q) before T2 then it is not a serial schedule.
this schedule is not a conflict
T2 :Write(Q) You can not serializable.
T1 :Write(Q) swap this to
T3 :Write(Q) make a serial
schedule
Every conflict serializable schedule is also a view serializability ; however some view
serializable are not conflict serializable.
Example (Prefer above example)
A schedule that is view serializable but not conflict serializable is characterized by blind
writes
Transaction
Recoverable Schedule
• Consider the following schedule
T 8 T9
Read(A) reads the data of it display but
Write(A) T8 is not committed
Read (A)
Read(B)
I can swap this
• If T8 rollback then T9 also needs rollback.
• Suppose T9 commits before T8 .
• If T8 fails before it commits then T9 also has to be aborted.
• However T9 is already committed.
• A situation where it is impossible to recover from the failure of T8 .
• This is an example of non recoverable schedule.
• Data base systems require recoverable schedule.
Transaction
the rolling of T8 also require the rolling back of T9 but T9 has already
committed. Rolling back of T9 is not possible so this is not a recoverable
schedule.
this is on example of non recoverable schedule
Conflict serializability and serial schedules are alone not enough. We need
to also recoverability of a particular schedule of transaction event if we able
to recover a database crash.
It is a recoverable schedule if t8 commit first then t9.
Even if it is recoverable schedule there is a chance of cascading rollback.
Transaction
Cascading rollback
Even if a schedule is recoverable, to recover from the failure of a transaction there
is a need to rollback several transaction.
Consider the three transaction T ,T1, T2,
T T1 T2
Read(A)
Read(B)
Read(A)
Read(A)
Write(A)
Read(A)
if T fails, then it will lead to rolling back T1 and T2. this is an example of
cascading rollback.
Transaction
There are three transaction T ,T1,and T2 transaction T has read a value of A
and written it. Whatever the value is written in A is read by the transaction
T1 and T2 transactions cannot commit or cannot display the new value of
A until T has committed otherwise it will become non recoverable.
Now even they do not commit and suppose the T has roll back. It has cause
a cascading effect in T1 and T2 . Because T2 is dependent on T1 and T1 is
dependent on T. so rollback of T. So roll back of T 1 which causes the
rollback of T2.
Cascading rollback needs several transaction to rollback.
Transaction
Cascading –less schedule:
Cascading rollback is undesirable leads to undoing a lot of work. Restriction of
such schedules where cascading rollback do not occur such a schedule are called
Cascading –less schedule.
Non Serializable Schedule :-
Consider the three transaction T1 ,T2 and T3 that performing the operation on x,
y, and z data item respectively consider the transaction graph.
T1 T2
•T1 <T2
•T2<T3 T1 and T3 by transitively
dependency
•T3<T1
T1<T2</T3
T3
If there is cycle it shows that there is non serializable schedule because this is
not produce a serial schedule one after the other .
Transaction
Transaction graph:
How to avoid cycle in transaction graph.
Protocols Put a correct sequence to prevent a cycle in graph.
Means the graph will flow on forward direction only.
T4 T1 T2T3
Suppose T4 is
next transaction to
come in
transaction graph T4 T4 T4 Go only forward direction
I can put T4 in such a
way.
TRANSACTION
Condition where two operation in transaction becomes conflict.
Conflict operation are as followed
Read(X) T1
Write(X) X
T2
They are operating on the same data item X and one of the operation is write than
we can say that these two operation are conflicting to each other.
TRANSACTION
Probability for conflict operation:
Write 1 (X) T1
(X)
Write 2 (X)
T2 Write
If one of the operation is write and operating on same data item.
TRANSACTION
Nested Transaction Model:
A nested transaction is used to provide a transactional guarantee for a subset of
operations performed within the scope of a larger transaction.
Doing this allows you to commit and abort the subset of operations independently of the
larger transaction.
A nested transaction occurs when a new transaction is started by an instruction that is
already inside on existing transaction. The new transaction is said to be nested
transaction within the existing transaction, hence the term nested transaction are
implemented differently in database. However, they have a common that the changes are
not made visible to any unrelated transaction until the outermost transaction has
committed. This mean that a commit in an inner transaction does not necessary persist
updates to the database.
TRANSACTION
Nested Transaction Model:
Nested Transactions offer several features, they are:
Nested transactions enables an application to isolate errors in certain operations.
Nested transaction allow an application to treat several related operations as a single
atomic operation.
Nested transaction can operate concurrently.
Nested transaction, like any other transactions do incur a performance cost. Therefore,
they should be used only when necessary.
TRANSACTION
Nested Transaction Model:
Some rules to the usage of a nested transaction are as follows:
While the nested(child) transactions is active , the parent transaction may not perform
any operations other than to commit or abort or to create more child transactions.
The depth of the nesting that you can achieve with nested transaction is limited only by
memory.
The lock held by a nested transaction are not released when the transaction commits.
Rather , they are now held by the parent transaction until such as time as that parent
commits.
TRANSACTION
Nested Transaction Model
Transaction viewed as hierarchy of sub transactions.
Top-level transaction can have number of child transactions.
Each child can also have nested transactions.
In Moss’s proposal, only leaf-level sub transactions allowed to perform database
operations.
Transactions have to commit from bottom upwards.
However, transaction abort at one level does not have to affect transaction in progress at
higher level.
TRANSACTION
Nested Transaction Model
Parent allowed to perform its own recovery:
Retry sub transaction.
Ignore failure, in which case sub transaction non-vital.
Updates of committed sub transactions at intermediate levels are visible only within
scope of their immediate parents.
Transaction
Deadlock:
Deadlock occurs when each transaction in a set of two or more transactions wait for some
resource that is locked by some other transaction in the same set.
For example transaction T1 acquires Resource R1 and transaction T2 acquires resource
R2. After this if T1 waits for R2 and T2 waits for R1. Both will never get the lock, and this
situation is termed as deadlock.
TRANSACTION
Lock Starvation:
Lock starvation occurs when a transaction cannot proceed for an indefinite period of time while
other transactions in the system continue to run normally. This can occur due to unfair lock
scheduling algorithms which implements priority based locking. One solution for starvation is to
have fair lock waiting scheme, such as first in first out (FIFO) queue.
Starvation can also occur when the deadlock algorithm, selects the same transaction repeatedly
for abort, thereby never allowing it to finish. The algorithms shall be modified to use higher
priorities for transactions that have been aborted multiple times to avoid this problem.
TRANSACTION
Dead Lock Detection:
Deadlock detection is more practical approach than the deadlock prevention techniques. This first
checks whether deadlock state actually exist in the system before taking any actions.
A simple way to detect a state of deadlock is for the system to construct and maintain a “wait-for”
graph.
If the system is in a state of deadlock, some of the transactions causing deadlocks must be aborted.
Either application or DBMS should select one of the transactions involved in deadlock for rollback to
get the system out of deadlock situation. This selection algorithm should consider avoiding
transactions that are running for long time and transactions that have performed many updates. The
best transactions to be aborted are the SELECT or read only transactions.
TRANSACTION
Dead Lock Recovery:
When detection algorithm determines that a deadlock exists, the system must
recover from the deadlock. The most common solution is to rollback one or more
transactions to break the deadlock. Three actions need to be taken:
Selection of Victim:
In this we determine which transaction (or transactions) to roll back to break the
deadlock. We should rollback those transactions that will incur the minimum cost.
Starvation:
In a system where selection of transactions, for rolled back, is based on the cost
factor, it may happen that the some transaction are always picked up.
TRANSACTION
Dead Lock Recovery:
Rollback:
Once we have decided that a particular transaction must be rolled back, we must
determine how far this transaction should be rolled back. The simplest solution is a
“total rollback”. Abort the transaction and then return restart it. However, it is more
effective to roll back the transaction only as far as necessary to break the deadlock.
Such partial rollback requires the system to maintain additional information about
the state of all the running transaction.
Concurrency control algorithm
Timeouts:
The simplest solution for handling deadlocks is timeouts. In this method,
transactions that wait for longer than the system defined timeout period, are assumed
to be in deadlock situation and are aborted.
TRANSACTION
Multi-level transaction
• Closed nested transaction - atomicity enforced at the top level.
• Open nested transactions - allow partial results of sub transactions to be seen outside
transaction.
So is multi-level transaction model where tree of sub transactions is balanced.
Nodes at same depth of tree correspond to operations of same level of abstraction in
DBMS.
Transaction
Multi-level Transaction
Transaction
Multi-level Transaction
• Example
• T7: T71, which increases balx by 5
T72, which subtracts 5 from baly
• T8: T81, which increases baly by 10
T82, which subtracts 2 from balx
As addition and subtraction commute, can execute these sub transactions in
any order, and correct result will always be generated.
Transaction
Transaction Benchmarking:
The goal of transaction benchmarking is to compare several database
management systems on the same class of applications in order to show
which one is more efficient for this particular class.
TPC(Transaction Processing Performance Council) :
Benchmarks compares the efficiency of implementation of the same
solution for different relational DBMS rather than comparing how
efficiently DBMS are able to solve a given problem.
The TPC-C benchmark measures performance and the prices.
The TPC is a non profit corporation founded to define transaction
processing and database benchmarks.
Related docs
Other docs by HC120718185831
