Ohne Titel

ACID Database Theory

• Atomicity - A set of events that must be handled as a single unit of work e.g. a SQL transaction
• Consistency - changes to data must conform to the rules of the database to ensure it is left in a valid state e.g. constraint, cascades, triggers
• Isolation - a transaction that behaves as though it is the only transaction interacting with the database for its duration
• Durability - a transaction that permanently changes the database and persists even during system shutdown(SQL does by default)
• The default isolation level in SQL guarantees all 4 ACID principles

Atomicity/Consistency

• examples.testparent is a primary key / foriegn key on another table
• Thistransaction will commit the first insert and then fail on the second. changes will not be rolled back.
• BEGIN TRANSACTION;
• INSERT INTO Examples.TestParent(ParentId, ParentName) VALUES (5, 'Isabelle');
• DELETE Examples.TestParent
• WHERE ParentName = 'Bob'; COMMIT TRANSACTION;
• Set xact abort on must be added to ensure that the first event is rolled back upon failure due to primary key constraint.
• SET XACT_ABORT ON;
• BEGIN TRANSACTION;
• INSERT INTO Examples.TestParent(ParentId, ParentName) VALUES (5, 'Isabelle');
• DELETE Examples.TestParent
• WHERE ParentName = 'Bob'; COMMIT TRANSACTION;
• syntax errors are automatically rolled back.
• Consistency is demonstrated in the constraint rules.

Isolation

• Isolation is demonstrated by the following. Executing this script without a commit transaction means that the transaction will remain active.
• BEGIN TRANSACTION; INSERT INTO Examples.TestParent(ParentId, ParentName) VALUES (5, 'Isabelle');
• When attempting to start a new transaction:
• SELECT ParentId, ParentName FROM Examples.TestParent;
• The transaction ticks over and will not complete as the first transaction is still active.
• Go back to the first transaction and commit transaction will make the second transaction execute successfuly
• This is because of SQL servers default isolation level READ COMMITED

Duribility

• If the system crashes after sql confirms a successful commit the changes made by the transaction are visable when the system becomes in a usable state.
• This is possibe using write ahead logging to hold changes in a log buffer then writes changes to the transaction log when the transaction commits or the log buffer becomes full. The log buffer then flushes the tran log and writes the chsnes first to the data cache and then permenently to disk.

Isolation levels and violations

• Violations
• Dirty Read
• When a transaction is allowed to read data from a row that has been modified by anothewr transaction but not yet commited. possible that the transaction is rolled back so the read would be incorrect.
• Nonrepeatable Read
• Data read twice in the same transaction cannot be guarunteed to be the same value. E.g. another transaction is able to update the value inbetween reads
• Phantom Read
• The same query executed twice within a transaction will return more or less rows in the second query due to another transaction inserting or deleting rows.

Isolation levels and violations 2

• Read uncommited: Dirty read, non Repeatable read, Phantom read
• Read commited: non Repeatable read, Phantom read
• Repeatable read: Phantom read
• Serializable: no violations

Isolation levels and violations 3

• Read commited snapshot
• Same as read committed however uses tempdb to store snapshot of the data so allows the data to be read by a transaction whilst being updated by another. transaction will repeatadly read data as it was at the start of the read statement not transaction.
• Read committed snapshot vs snapshot
• Read committed
• No update conflicts
• Can be used with distributed transactions
• Provides statement level read consistency
• Snapshot
• Update conflicts
• Cannot be used with distributed transactions
• Provides transaction level read consistency

Isolation level resource and performance

• Read committed
• Shared(S) lock for read operations held for the duration of that single operation.
• Exclusive(X) Lock for write operations. Changes to the data are not visible to other operations for the duration of the write operations transaction
• Read Uncommitted
• Ignores existing locks and reads bpth committed and uncommitted data. Does not aqcuire shared locks for read operations however schema modication locks can still block reads
• Repeatable Read
• S locks on the data(and up the lock hierarchy) for the duration of the transaction. Therefore reads block write operations in other transactions. Cannot manage as many concurrent proccesses and performance can be impacted as deadlocks can become more frequent.
• Serializable
• Locks data for a read operation and also uses key range locks to prevent other transactions from inserting or modifying the data for the duration of the transaction. This high level of locking reduces concurrency and potentially slows performance due to locking contention
• Snapshot
• No locks are acquired. Deadlocks and lock escalations occur less frequently, performance is faster and concurrency is higher. Read operations are not blocked by write operations and write operations are not blockd by read operations. These benefits come with an overhead cost as more space is required in tempdb to for row version storage and more cpu and memory is required by sql server to manage the versioning.

Folie 9