SQL Part 2 Flashcards
What are the properties a transaction must follow?
atomicity, consistency, isolation, and durability.
types of isolation levels in sql
Read Uncommitted
Read Committed
Repeatable Read
Serializable
Snapshot
What is Read Uncommitted isolation level?
the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transactions, thereby allowing dirty reads
What phenomena does Read Uncommitted isolation level prevent?
Transactions running at this level do not issue shared locks to prevent other transactions from modifying data read by the current transaction
What is Read Committed isolation level?
This isolation level guarantees that any data read is committed at the moment it is read
What phenomena does Read Committed isolation level prevent?
So read-committed isolation level prevents dirty read phenomenon
What is Repeatable Read isolation level?
only sees data committed before the transaction began; it never sees either uncommitted data or changes committed during transaction execution by concurrent transactions.
What phenomena does Repeatable Read isolation level prevent?
prevents dirty reads and not-repeatable reads
What is Serializable isolation level?
provides the strictest transaction isolation. This level emulates serial transaction execution for all committed transactions; as if transactions had been executed one after another, serially, rather than concurrently.
What phenomena does Serializable isolation level prevent?
protects from all phenomena that a Repeatable Read does, plus prevents phantom inserts,
preventing other users from updating or inserting rows into the data set until the transaction is complete
What is Snapshot isolation level?
specifies that data read within a transaction will never reflect changes made by other simultaneous transactions, avoids most locking and blocking by using row versioning
What phenomena does Snapshot isolation level prevent?
Dirty reads are prevented because only committed data is visible, and the static nature of the snapshot prevents both non-repeatable reads and phantoms from being encountered
What is the difference between joins and set operators?
join combine columns from separate tables; whereas, set operations combine rows from separate tables
What are the types of joins?
inner join,
full outer join,
left outer join
right outer join
What is inner join?
selects all rows from both participating tables as long as there is a match between the columns