FIT1004 Lecture Notes - Lecture 12: Granularity, Transaction Log, Database Transaction
Lecture 12: Transaction, Maintenance and Concurrency
Transaction Properties
● Atomicity
○ SQL requests of a transaction must be entirely completed or entirely aborted
● Consistency
○ must take the database from one consistent state to another
● Isolation
○ must not intere with other concurrent transactions
○ data used during execution of a transaction can’t be used by a second
transaction until the first one is completed
● Durability
○ once completed the changes the transaction made to the data are durable, even
in the event of system failure
○ when the transactions are completed fully, the transaction must not ever be lost
Transaction Management
● Transaction boundaries
○ Start - first SQL statement is executed,some systems have a BEGIN WORK
type command
○ End - COMMIT or ROLLBACK
● Concurrency Management
○
○ Solution
■ locking mechanism
■ Locking and the released by a processes called Lock Manager
■ A lock is an indication that some part of the database is temporarily
unavailable for update because
● other transactions is reading it
● another transaction is updating it
Lock Granularity
Refers to the size of the units that are, or can be locked
Lock Types
● Shared lock
○ multiple processes can simultaneously hold shared locks, to enable them to read
without updating
● Exclusive lock
○ a process that needs to update a record must obtain an exclusive lock
○ application for a lock that will not proceed until all current locks are released
Document Summary
Sql requests of a transaction must be entirely completed or entirely aborted. Must take the database from one consistent state to another. Must not intere with other concurrent transactions. Data used during execution of a transaction can"t be used by a second transaction until the first one is completed. Once completed the changes the transaction made to the data are durable, even in the event of system failure. When the transactions are completed fully, the transaction must not ever be lost. Start - first sql statement is executed,some systems have a begin work type command. Locking and the released by a processes called lock manager. A lock is an indication that some part of the database is temporarily unavailable for update because. Refers to the size of the units that are, or can be locked. Multiple processes can simultaneously hold shared locks, to enable them to read without updating.