unit 5+6: query processing + Transactions Mang. Flashcards
what topics are included in this unit
- query processing problem
- objectives of query processing
- complexities of relational algebra operations
- characterization of query processing
- layers of query processing
- brief introduction of each layer of query processing
what happens when we run any query
what is first step
first query gets optimized
then query gets executed
when query processing is done, there are how many steps
there are 3 steps to query, internally
what are three steps of query processing
Query processing involves 3 basic steps
1. parsing and translation
2. optimization
3. evaluation
in first step of query processing, parsing. what does happen here
- query’s syntax is checked, that is known as parsing
for parsing internally one thing get created, what do we call it.
for parsing parsing tree is generated
after parsing what is next step
translation is next step after parsing
what happens in translation
in translation process, query is converted in relational form
does query gets translated in Relational Algebra from SQL query
yes
what is next step after parsing and translation
optimization of query is next step after parsing and translation
what is next step after optimization
evaluation of query is next step after optimization
draw flow chart for 3 steps of query processing
Example of Query processing
definition o ftransaction
action or series of actions, carried out by single user or application program, which reads or updated the contents of the database
is transaction logical unit of work
yes, transaction is logical unit of work
more points on transaction
it may be an entire or a part of program or a command (SQL for INSERT or UPDATE) and it may involve a large number of operations on a database
what happens after transaction is committed
after a transaction is committed, database reaches to a new consistent state
can a transaction is roll back or undone after its execution
yes, a transaction may be rolled back or undone after its execution
what is compensating transaction
A compensating transaction can reverse the effect of a transaction
what is “ state transition of a transaction “
Draw diagram
what is meaning of state’s of transaction
when program is in transaction state, from how many steps / processes it goes
this is known as state of transaction
Explain Active states of transaction
Database before execution starts
[ read or write operation ]
- Active state
- pahile DB khali baslela hota, aapan read kiva write operation kel
- mag database active state made aala
- active state made yeun DB read kiva write operation operation perform karel
- manje program kiva transaction, execution state made aala ahe
Active State: It is the initial state of transaction, during execution of statements, a transaction is in Active State
Active state madun transaction kuth jate
Active state nantr transaction la don options astat
- Partially committed
- Failed
Explain failed state of transaction
Partially committed state nantr pan Failed state yeu shekte ani Active state nantr pan failed state yeu shakte
Failed state nanter Aborted state aste, jith DB transaction la kill karto
Explain Aborted state of transaction
Aborted state, failed state nantr yete
Failed
[ kill ]
Aborted
[ Rollback ]
Database before execution starts
Jar active state nantr transaction fail nahi zali tr kontya state made jate
Jar transaction fail nahi zali Active State nantr tr, ti partially committed state made jate
partially committed state cha kay meaning hoto
transaction la je kam karycha hot te kam transction ne compelte kel,
he partially committed state cha meaning hoto
R( A )
A = A - 700
W = ( A )
R( B )
B = B + 700
W( B ) -> till here, partially committed
Transaction la je kam krycha hot te zal complete, fakt te commit nahi kel azun
commit manje kama la save karna
what is next step after partially committed state
5:00
partially committed main memory [ RAM ] me execute hota he ya primary memory me
5:20
After committed state which is the next state
teminated
jab committed state perform hogi tab kis memory ka use hoga
5:40
what are states of transaction
write one be one
active
partially committed
committed
failed
aborted
committed state ka kya matlab hota he
committed state ka matlab hota he, transaction complete hone ke baad, usse primary memory ya hard disk me update kiya jata he
explain each state of transaction in detail
when partially committed state takes place
a transaction is partially committed if it has violated serializability or integrity constraint or secondary storage failure for record updation, then the transaction has to be aborted.
what if transaction has been successful,
any updates can be safely recorded and the transaction can go to the committed state
when does the failed state occurs
it occurs if the transaction can not be committed or the transaction is aborted while in the active state. perhaps due to the user aborting the transaction or as a result of the concurrency control protocol aborting the transaction to ensure the serializability
due to user aborting the transaction or as a result of the concurrency control protocol aborting the transaction ensures what
serializability
ACID properties of a transaction
Atomicity
consistency
Isolation
Durability
Explain Atomicity
Explain Consistency
Explain Isolation
Explain Durability
explain architecture of database transaction
explain transaction manager
what does transaction manger do
what is another name of schedular
what does recovery manager do
what does buffer manager do
explain failed state
in any case if transaction cannot be proceeded further then transaction is in failed state
rollback kya hota he
rollback ka matlab hota he, transaction apne initial state me aa jata he
agar wo fail ho jata h, ya koi error aa jata he
definition of committed state
after completion of transaction, it comes in committed state
