[4] Transactions

Question 1

Define a transaction.

Answer 1

A logical unit of work: either it completes as a whole or not at all.

Question 2

Which of the following are transactions: DML, DDL?

Answer 2

Both DML(e.g. update) and DDL (e.g. create statements) are transactions.

Question 3

What is ACID?

Answer 3

They are the properties of Transactions: Atomicity, Consistency, Isolation and Durability

Question 4

Describe atomicity.

Answer 4

Every transaction is a unit of work: all succeed or none succeed.

Question 5

Describe consistency in transactions.

Answer 5

Transactions must result in maintaining a consistent state for the database.

Question 6

Describe isolation in transactions.

Answer 6

One transaction’s changes must not affect the changes of another transaction.

Question 7

Describe durability in transactions.

Answer 7

For interruptions in service, all committed transactions are rolled forward and all uncommitted are rolled back.

Question 8

What does Commit and Rollback mean in T-SQL?

Answer 8

An approved transaction is committed. An unrecoverable error results in a rollback.

Question 9

Describe the 2 types of transactions.

Answer 9

1. System: maintains internal persistent system tables (not user controlled).
2. User: user-made to change/ read data.

Question 10

Syntax to find transactions in the database

Answer 10

select distinct [name]

from sys.dm_tran_active_transactions

Question 11

Describe the following transaction levels:

(a) @@TRANCOUNT = 0
(b) @@TRANCOUNT > 0
(c) @@TRANCOUNT > 1

Answer 11

a. 0 means code not within transaction.
b. > 0 means there is an active transaction.
c. > 1 indicates nested level of transaction.

Question 12

Describe the following transaction states:

(a) XACT_STATE() = 0
(b) XACT_STATE() = 1
(c) XACT_STATE() = -1

Answer 12

(a) no active transaction.
(b) uncommited transaction that can be committed, but nested level not reported.
(c) uncommited transaction with fatal error.

Question 13

What is the purpose of the @@TRANCOUNT and XACT_STATE() syntaxes?

Answer 13

@@TRANCOUNT checks transaction nesting level and the XACT_STATE() reports (un)committed.

Question 14

What are the 3 transaction modes?

Answer 14

1. Autocommit
2. Implicit transaction
3. Explicit transaction

Question 15

The diagram below describes which type of transaction mode and how does it differ from the other modes?
                 \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
                 |  @@TRANCOUNT = 0    |
                 |\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_|
	                              |
	                              |
                   \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
                   |    Work = DDL/DML    |
                   |\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_|
	                              |
	                              |

Answer 15

Autocommit: there is no requirement for BEGIN TRAN, COMMIT TRAN, or ROLLBACK TRAN statement.