Please enable JavaScript.
Coggle requires JavaScript to display documents.
DB Transactions by SE Junior G1moodle, Mehdi Ayadi and Aziz Salhi, Mehdi…
DB Transactions by SE Junior G1
moodle
Definiiton
A transaction is a sequence of operations that form a
single unit of work
There are two main transaction issues
concurrent execution of multiple transactions
recovery after hardware failures and system crashes
In many SQL implementations, each SQL statement is a
transaction on its own
A transaction Ti transforms one consistent database state into another consistent database state
ACID Properties
Atomicity
either all operations of a transaction are reflected in the database
or none of them (all or nothing)
Consistency
if the database was is a consistent state before the transaction started, it will be in a consistent state after the transaction has been executed
Isolation
if transactions are executed in parallel, the effects of an ongoing
transaction must not be visible to other transactions
Durability
after a transaction finished successfully, its changes are
persistent and will not be lost (e.g. on system failure)
Transaction States
Active
initial state; transaction is in this state while executing
Partially committed
after the last statement has
been executed
Committed
:check:
after successful completion
Failed
after discovery that a normal execution is no longer possible - logical error (e.g. bad input), system error (e.g. deadlock) or system crash
Aborted :red_cross:
after the rollback of a transaction
transaction management
transaction manager
ensures that we proceed from one consistent state to another
consistent state (database consistency)
ensures that transactions will not violate integrity constraints
Transaction requests
Results
scheduler
provides a specific strategy for the execution of transactions and
the corresponding concurrency control
avoids or resolves conflicts during concurrent data access
Serial schedulers
Non serial scheduler
Serializable schedules
Conflict serialiable
View serializable
Non-serial schedules
Recoverable schedule
Cascading schedule
Strict schedule
Cascadeless schedule
Non recoverable schedule
recovery manager
Server session
restore the database to the state it was in before a failure
occurred (e.g. due to software bug or hardware problem) while
executing one or multiple transactions
Backup and copy files
Recover datafiles
Transaction coordinator
Transaction managers that are at other sites
Conflict Serialisability
Two operations of transactions Ti and Tj form a conflict pair if at least one of them is a write operation
Concurency problems
Dirty Read
Not repeatable read
Phantom Read
Lost Update
Incorrect Summary
Concurency Solution
Locking
Optimisitc locking
Validate
Write
Read
Pessimistic locking
Use shared executive locks
Deadlock
Avoidance
Recovery
Prevention
TimeStamping
Unrepeatable Read
Amine , Aziz B
Scripting
Example
Begin ; Create table account( id int , name varchar(50) ) ; Commit ;
Definition
A set of instruction pre-written ready to be executed sequentially which helps avoiding the overhead of writing instructions one by one each time
Error handling
Error between Begin ; Commit ;
Rollback and no change happens
Error outside Begin ; Commit ; section
The instructions outside the Begin commit section get executed separately
Mehdi Ayadi and Aziz Salhi
Mehdi Ayadi and Aziz Salhi
