Chapter 12: Manage Transactions, Error Handling & Dynamic SQL Flashcards
What’s a transaction?
It’s an unit of work: it’s all done or none, that’s partial result it’s not acceptable, it’s all or nothing.
What’s ACID?
It’s the properties that defines an database transaction. ACID stands for:
A: Atomicity. Every transaction is an atomic unit of work, that is, all database changes in the transactions are succeed or none of them succeed.
C: Consistency. Every transaction, whether successful or not, need to leave the database in a consistent state. If a inconsistent occur when a transaction happens, then it’ll rollback to the previous state, where the consistency was.
I: Isolated. A transaction don’t need to interference with another database transactions.
D: Durability. This property ensures that once the transaction has completed execution, the updates and modifications to the database are stored in and written to disk and they persist even is system failure occurs. These updates now become permanent and are stored in a non-volatile memory. The effects of the transaction, thus, are never lost.
Why is it important for SQL Server to maintain the ACID quality of transactions?
To ensure that the integrity of database data will not be compromised.
How does SQL Server implement transaction durability?
By first writing all changes to the database transaction log before making changes to the database data.
What are the types of transactions?
System transactions and user transactions.
What are the typicals commands for handle transaction?
BEGIN TRANSACTION tells that all codes below belong to this transaction.
COMMIT TRANSACTION tells that the changes needs to be commited to the database.
ROLLBACK TRANSACTION tells that all the changes will be reversed.
What are the functions to know the transaction level and its states?
@@TRANCOUNT and XACT_STATE()
What are the modes to work with SQL Server database transactions?
Autocommit mode,
Implicit Transaction mode,
Explicit Transaction mode
What does do the @@TRANCOUNT?
Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.
The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.
Review and practice
IMPLICIT TRANSACTION MODE AND EXPLICIT TRANSACTION MODE
What’s a nested transaction?
When a transaction contains another transaction inside them, and so on.
Exam tip about nested transaction and COMMIT
An inner COMMIT statement has no real effect on the transaction, only decrementing
@@TRANCOUNT by 1. Just the outermost COMMIT statement, the one executed when
@@TRANCOUNT = 1, actually commits the transaction.
Exam tip about nested transaction and ROLLBACK
Note that it doesn’t matter at what level you issue the ROLLBACK command. A transaction
can contain only one ROLLBACK command, and it will roll back the entire transaction and
reset the @@TRANCOUNT counter to 0.
What are the aditional transaction options?
Savepoints, Cross - database transactions and Distributed transactions.
How many ROLLBACKs must be executed in a nested transaction to roll it back?
Only one ROLLBACK. A ROLLBACK always rolls back the entire transaction, no matter how many levels the transaction has.
How many COMMITs must be executed in a nested transaction to ensure that the entire transaction is committed?
One COMMIT for each level of the nested transaction. Only the last COMMIT actually commits the entire transaction.
What are the modes of locking?
Shared locks: used for sessions that read data.
Exclusive locks: used for changes to data.
What’s deadlock?
Let’s say we have two transactions: T1 and T2. T1 updates a database object called O1. T2 updates another database object called O2. So far, everything it’s fine. Now, T1 wants to update the object O2. It get’s blocked until T2 release that object. But now, T2 wants to update O1, it’s also get blocked because T1 locked O1. So this cause a deadlock between two transactions (T1 and T2). What SQL Server does it’s kill one of them, and let the other finish his process.
If two transactions never block each other, can a deadlock between them
result?
No. In order to deadlock, each transaction must already have locked a resource the other transaction wants, resulting in mutual blocking.
Can a SELECT statement be involved in a deadlock?
Yes. If the SELECT statement locks some resource that keeps a second transaction from finishing, and the SELECT cannot finish because it is blocked by the same transaction, the deadlock cycle results.
NOTE
Read the page marked as favorite for understand better read uncommited, and so on. Also, check the chapter 12.sql
What’s the default isolation level in a session?
The READ COMMITED, which permits non repeatable reads and phantom reads.
If your session is in the READ COMMITTED isolation level, is it possible for one of your queries to read uncommitted data? (REVIEW THIS QUESTION)
Yes, if the query uses the WITH (NOLOCK) or WITH (READUNCOMMITTED) table hint. The session value for the isolation level does not change, just the characteristics for reading that table.
Is there a way to prevent readers from blocking writers and still ensure that readers only see committed data?
Yes, that is the purpose of the READ COMMITTED SNAPSHOT option within the READ COMMITTED isolation level. Readers see earlier versions of data changes for current transactions, not the currently uncommitted data.
