Chapter 12: Manage Transactions, Error Handling & Dynamic SQL

Question 1

What’s a transaction?

Answer 1

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.

Question 2

What’s ACID?

Answer 2

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.

Question 3

Why is it important for SQL Server to maintain the ACID quality of transactions?

Answer 3

To ensure that the integrity of database data will not be compromised.

Question 4

How does SQL Server implement transaction durability?

Answer 4

By first writing all changes to the database transaction log before making changes to the database data.

Question 5

What are the types of transactions?

Answer 5

System transactions and user transactions.

Question 6

What are the typicals commands for handle transaction?

Answer 6

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.

Question 7

What are the functions to know the transaction level and its states?

Answer 7

@@TRANCOUNT and XACT_STATE()

Question 8

What are the modes to work with SQL Server database transactions?

Answer 8

Autocommit mode,
Implicit Transaction mode,
Explicit Transaction mode

Question 9

What does do the @@TRANCOUNT?

Answer 9

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.

Question 10

Review and practice

Answer 10

IMPLICIT TRANSACTION MODE AND EXPLICIT TRANSACTION MODE

Question 11

What’s a nested transaction?

Answer 11

When a transaction contains another transaction inside them, and so on.

Question 12

Exam tip about nested transaction and COMMIT

Answer 12

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.

Question 13

Exam tip about nested transaction and ROLLBACK

Answer 13

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.

Question 14

What are the aditional transaction options?

Answer 14

Savepoints, Cross - database transactions and Distributed transactions.

Question 15

How many ROLLBACKs must be executed in a nested transaction to roll it back?

Answer 15

Only one ROLLBACK. A ROLLBACK always rolls back the entire transaction, no matter how many levels the transaction has.

Question 16

How many COMMITs must be executed in a nested transaction to ensure that the entire transaction is committed?

Answer 16

One COMMIT for each level of the nested transaction. Only the last COMMIT actually commits the entire transaction.

Question 17

What are the modes of locking?

Answer 17

Shared locks: used for sessions that read data.

Exclusive locks: used for changes to data.

Question 18

What’s deadlock?

Answer 18

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.

Question 19

If two transactions never block each other, can a deadlock between them
result?

Answer 19

No. In order to deadlock, each transaction must already have locked a resource the other transaction wants, resulting in mutual blocking.

Question 20

Can a SELECT statement be involved in a deadlock?

Answer 20

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.

Question 21

NOTE

Answer 21

Read the page marked as favorite for understand better read uncommited, and so on. Also, check the chapter 12.sql

Question 22

What’s the default isolation level in a session?

Answer 22

The READ COMMITED, which permits non repeatable reads and phantom reads.

Question 23

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)

Answer 23

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.

Question 24

Is there a way to prevent readers from blocking writers and still ensure that readers only see committed data?

Answer 24

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.

Question 25

How SQL Server alerts that a error has come?

Answer 25

When the @@ERROR function returns an positive integer value ( > 0).

Question 26

What are the parts of an Error Message in SQL Server?

Answer 26

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘FROM’.

Msg (Error Number): gives a description of the error, such like “Incorrect syntax near the keyword ‘T - SQL Keyword’ “. This error number can be found in the sys.messages view.

Level (Error Level): determines the severity of the error, and his value is from 0 to 25, whereas 0 to 10 are warning, 11 to 16 are user errors, from 17 to 19 are serious exceptions and from 20 to 25 are fatal connections and server level exceptions

State (Error State): it’s an internally error message SQL Server.

Question 27

What happen to a TSQL code which is not inside of a TRY/CATCH block?

Answer 27

The error message will be passed through to the client and cannot be intercepted in T SQL code.

Question 28

How We can raise our own error messages?

Answer 28

With two commands: RAISERROR or THROW command.

Question 29

Can THROW be used without parameters?

Answer 29

Yes, it can.

Question 30

Exam tip about THROW

Answer 30

The statement before the THROW statement must be terminated by a semicolon (;). This reinforces the best practice to terminate all T-SQL statements with a semicolon.

Question 31

How can you add custom error messages?

Answer 31

You can use the system stored procedure sp_addmessage to add your own custom error messages.

Question 32

NOTE FOR HANDLE ERROR:

Answer 32

Review the T SQL code wrote.

Question 33

NOTE FOR DYNAMIC SQL:

Answer 33

Review Dynamic SQL.

Question 34

What does do the XACT_ABORT property?

Answer 34

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.