Transactions Flashcards

1
Q

What does ACID Stand for

A

Atomicity (all or nothing), Consistency (correct), Isolation (no interference iwht others), Durability (permanent)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What are the 3 types of transactions

A

AutoCommit, Explicit and Implicit

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is the default transaction mode for SQL Server

A

AutoCommit

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

How does AutoCommit work

A

When you run a single UPDATE statement and implied transaction is added

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

How could you check if a trasaction has an error

A
if @error != 0
 begin 
   rollback tran
   return
end
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Which types of commands cannot be inside of a transaction

A

ALTER TABLE, DROP DATABASE i.e. long running commands

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What does a savepoint in a transaction do?

A

sets a marker you can roll back to, but the remainder is commited

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

How many active transactions can you have per user

A

one

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How can you tell how many transactions are open

A

SELECT @@trancount

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

How do you turn on implicit transactions

A

SET IMPLICIT_TRANSACTIONS ON

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

When would you likely use implicit transactions

A

When porting code from another db such as Oracle that uses implicit trans

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

How is a batch denoted in SQL

A

; or GO (SSMS only not T-SQL)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What happens if a transaction in a batch fails

A

The batch continues

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Can transactions span multiple batches

A

yes, but it’s a bad idea

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Best practice for stored procedures and tranactions

A

no nesting i.e. stored proc calling nested proce with tran

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What types of locks are there?

A

exclusive (data mod) shared or read

17
Q

What does the WITH (NO LOCK) hint do?

A

Performs “dirty” data with a “READ Uncommited” transaction isolation level

18
Q

What is the default tran isolation level for sql server

A

READ COMMITTED