Transactions Flashcards
What does ACID Stand for
Atomicity (all or nothing), Consistency (correct), Isolation (no interference iwht others), Durability (permanent)
What are the 3 types of transactions
AutoCommit, Explicit and Implicit
What is the default transaction mode for SQL Server
AutoCommit
How does AutoCommit work
When you run a single UPDATE statement and implied transaction is added
How could you check if a trasaction has an error
if @error != 0 begin rollback tran return end
Which types of commands cannot be inside of a transaction
ALTER TABLE, DROP DATABASE i.e. long running commands
What does a savepoint in a transaction do?
sets a marker you can roll back to, but the remainder is commited
How many active transactions can you have per user
one
How can you tell how many transactions are open
SELECT @@trancount
How do you turn on implicit transactions
SET IMPLICIT_TRANSACTIONS ON
When would you likely use implicit transactions
When porting code from another db such as Oracle that uses implicit trans
How is a batch denoted in SQL
; or GO (SSMS only not T-SQL)
What happens if a transaction in a batch fails
The batch continues
Can transactions span multiple batches
yes, but it’s a bad idea
Best practice for stored procedures and tranactions
no nesting i.e. stored proc calling nested proce with tran