Please enable JavaScript.
Coggle requires JavaScript to display documents.
DB Transactions by Junior SE G2, Auto-Commit Mode, Scheduling, Validation…
DB Transactions by Junior SE G2
ACID Properties
Isolation
changes are
persistent when successful
Consistency
consistent state conserved before/after transactions
Durability
successful transaction --> persistent changes
Atomicity
All or nothing
Atomic Operations
Ensure Data-Base moves forward in time :check:
Operations change the
state
of the database
Keep Database consistent :check:
Concurrency Control (Locking)
:star:
Locking :lock:
Procedure used to control concurrent access to data (items)
When one transaction is accessing a data item, a lock may deny access to other transactions.
Granularity of Locks :closed_lock_with_key:
Entire DB
A File
A Page
A Record
A Field Value of a Record
Shared Locks (Read) :handshake:
If a transaction has a shared lock on a data item, it can read the item but not update it.
Read locks cannot conflict, so more than one transaction can hold shared locks on the same item.
Write Exclusive Locks :writing_hand:
Transaction can read and update item.
Only one transaction at a time can hold this lock on an item.
Other transactions cannot lock, read or update that item.
Releasing Locks :unlock:
It explicitly releases lock during execution.
Transaction terminates (commits or aborts).
Recovery Techniques
Mirroring
Shadow Paging
Transaction Log
Backups
Immediate Backup
Archival Backup
Type of backups
Complete copy of the whole database
Incremental backup Copy of modifications made since the last complete or incremental backup
Backups of the logs file
Deadlock
Prevention
Wound-Wait Scheme
Wait-Die Scheme
Avoidance
Wait-for lock
Wait-for Graph
Transactions :
a series of actions executed as a whole
Type of backup
complete copy of the DB
incremental backup( A copy of the modifications of the changes created since the last incremental or complete backup)
backups of the logs file
Batch Services :recycle:
Automate the execution of specially selected queries, usually used to avoid any anomalies
Set a Query schedule
Collect the query history log
Set a Target DB
Auto-Commit Mode
In transactions, auto-commit should always be off
DB acts as if every single command is wrapped with BEGIN ... COMMIT
Commit & Rollback must be done explicitly
Commit behaves like a save/check point.
Rollback loads back the state the last commit is holding.
Scheduling
Serial Schedule
Operations of each transaction are executed concurrently
No interleaved operations from other transactions
Transactions cannot interfere with each other
Serializability
Serializable schedules:
Equivalent to serial schedules
Produce same final result as serial schedule
Non-Serial Schedule
Operations from a set of concurrent operations are interleaved
Potential for interference between concurrent transactions
Validation-based Protocols