Chapter 12 Implementing Transactions, Error Handling, and Dynamic SQL Flashcards
What is a transaction?
A transaction is a logical unit of work. Either all the work completes as a whole unit or none of it does. For SQL Server, all changes to database data take place in the context of a transaction. In other words, all operations that in any way write to the database are treated by SQL Server as transactions. This includes DML (INSERT, UPDATE) and DDL (CREATE TABLE, CREATE INDEX). Technically, even single SELECT statements are a type of transaction (read-only transactions).
What do the terms commit and rollback mean?
The terms commit and rollback refer to the act of controlling the result of transactions in SQL Server. When the work of a transaction has been approved by the user, SQL Server completes the transaction’s changes by committing them. If an unrecoverable error occurs or the user decides not to commit, then the transaction is rolled back.
What does the ACID acronym stand for?
(A) Atomicity - Every transaction is an atomic unit of work, meaning that all database changes in the transaction succeed or none of them succeed, (C) Consistency - Every transaction whether successful or not leaves the database in a consistent state as defined by all objects and database constraints, (I) Isolation - Every transaction looks as though it occurs in isolation from other transactions in regard to database changes The degree of isolation can vary based on isolation level, (D) Durability - Every transaction endures through an interruption of service. When service is restored, all committed transactions are committed and all uncommitted transactions are rolled back.
How does SQL Server enforce transactional isolation?
SQL Server ensures that when a transaction makes multiple changes to the database, none of the objects being changed by that transaction are allowed to be changed by any other transaction. In other word’s, one transaction’s changes are isolated from any other transaction’s activities. If two transactions want to change the same data, one of them must wait until the other transaction is finished.
SQL Server accomplishes transactional isolation by means of locking as well as row versioning. SQL Server locks objects (rows and tables) to prevent other transactions from interfering.
How does SQL Server maintain transactional durability?
SQL Server maintains transactional durability by using the database transaction log. Every database change (DML or DDL) is first written to the transaction log with the original version of the data (in the case of updates and deletes).
For example, if the database server shuts down unexpectedly just after the fact of a successful commit has been written to the transaction log, when SQL Server starts back up, the transaction will be rolled forward and finished. On the other hand, if the database server shuts down unexpectedly before a successful commit could be written to the log, when the database server starts back up, the transaction will be rolled back and any database changes undone.
Which database have transaction logs?
Every database including every system database has a transaction log to enforce transaction durability.
Can you turn off the transaction log?
No. You cannot turn off a database’s transaction log or remove it. Some operations can somewhat reduce transaction logging. But all database changes are always written first to the transaction log.
Why is it important for SQL Server to maintain the ACID properties of transactions?
To ensure that the integrity of the database data will not be compromised.
What are the two types of transactions?
(1) System transactions and (2) User transactions. System transactions are not under user control. They are used by SQL Server to maintain its internal persistent system tables. User transactions are created by users in the process of changing and even reading data whether automatically, implicitly, or explicitly.
What DMV can you query to observe the names of the transactions?
sys.dm_tran_active_transactions. The default name for user transactions is user_transaction if one is not assigned by the user.
What command(s) do you use to mark the start of a transaction - explicitly?
BEGIN TRANSACTION, BEGIN TRAN. A name can be assigned to the transaction.
What command(s) do you use to mark the end of a transaction by committing it or rolling it back?
To commit a transaction, issue the COMMIT TRANSACTION command, which you can also write as COMMIT TRAN, COMMIT WORK, or just COMMIT. To roll back a transaction, issue the ROLL BACK TRANSACTION command or alternatively, ROLLBACK TRAN, ROLLBACK WORK, or just ROLLBACK.
Transactions can be nested.
What does the @@TRANCOUNT system function indicate?
@@TRANCOUNT can be queried to find the level of the transaction.
– @@TRANCOUNT = 0 indicates that, at this point, the code is not within a transaction.
– @@TRANCOUNT > 0 indicates that there is an active transaction and a number > 1 indicates the level of a nested transaction.
What does the XACT_STATE() function indicate?
– A state of 0 indicates that there is no active transaction.
– A state of 1 indicates that there is an uncommitted transaction and it can be committed but the nesting level is not reported.
– A state of -1 indicates that there is an uncommitted transaction but it cannot be committed due to a prior fatal error.
What are the three modes of user transactions in SQL Server?
(1) Autocommit, (2) Implicit transaction, (3) Explicit transaction
How does the autocommit mode of transactions behave?
Autocommit is the default transaction management mode. In autocommit mode, single DML and DDL statements are executed in the context of a transaction that will automatically be committed when the statement succeeds or automatically rolled back if the statement fails.
In the autocommit mode, you do not issue any surrounding transactional commands such as BEGIN TRAN, ROLLBACK TRAN, or COMMIT TRAN. Further, @@TRANCOUNT is not normally detectable.
Whatever changes you make to the database are automatically handled, statement by statement, as transactions.
How does the implicit mode of transactions behave?
In the implicit transaction mode, when you issue one or more DML or DDL statements or a SELECT statement, SQL Server starts a transaction, increments @@TRANCOUNT, but does not automatically commit or roll back the statement. You must issue a COMMIT or ROLLBACK to finish the transaction.
Implicit transaction mode is not the default, so you must enter the following command:
SET IMPLICIT_TRANSACTIONS ON;
or
SET ANSI_DEFAULTS ON;
Note that @@TRANCOUNT is incremented as soon as you enter any command to change data. Also, implicit transactions can span batches.
How does the explicit mode of transactions behave?
An explicit transaction occurs when you explicitly issue the BEGIN TRANSACTION or BEGIN TRAN command to start a transaction. As soon as you enter the BEGIN TRAN command, the value of @@TRANCOUNT is incremented by 1. Then you issue your DML or DDL commands, and when ready, issue COMMIT or ROLLBACK.
Note that you can use explicit transactions in implicit transaction mode, but if you start an explicit transaction when running your session in implicit transaction mode, the value of @@TRANCOUNT will jump to 2 immediately after the BEGIN TRAN command - effectively becoming a nested transaction.
What are some advantages of using implicit transactions?
(1) You can roll back an implicit transaction after the command has been completed.
(2) Because you must issue the COMMIT statement, you may be able to catch mistakes after the command finished.
What are some disadvantages of using implicit transactions?
(1) Any locks taken out by your command are held until you complete the transaction. Therefore, you could end up blocking other users from doing their work. (2) Because this is not the standard method of using SQL Server, you must constantly remember to set it for your session. (3) The implicit transaction mode does not work well with explicit transactions because it causes the @@TRANCOUNT value to increment by 2 unexpectedly. (4) If you forget to commit an implicit transaction, you may leave locks open.
Can transactions span batches?
Yes. Both implicit and explicit transactions can span batches - that is, GO statements. However, it is a best practice to ensure that each transaction takes place in one batch.
What are nested transactions?
When explicit transactions are nested, they are called nested transactions. In this scenario, the behavior of COMMIT and ROLLBACK change when you nest transactions.
An inner COMMIT statement has no real effect on the transaction - only decrementing @@TRANCOUNT by 1. Just the outermost COMMIT statement, actually commits the transaction.
Also, note that it doesn’t matter at what level you issue a ROLLBACK command. A transaction can only contain one ROLLBACK command and it will roll back the entire transaction and reset the @@TRANCOUNT to 0.
How do you mark a transaction?
You can name an explicit transaction by putting the name after the BEGIN TRAN statement. Transaction names must follow the rules for SQL identifiers; however, only the first 32 characters are acknowledged. The transaction name is displayed in the name column of the sys.dm_tran_active_transactions DMV.
e.g.
BEGIN TRANSACTION Tran1;
Note that SQL Server only records transaction names for the outermost transaction. If you have nested transactions, any names for the nested transactions are ignored.
Named transactions are used for placing a mark in the transaction log in order to specify a point to which one or more databases can be restored. When the transaction is recorded in the transaction log, the transaction mark is also recorded.
e.g.
BEGIN TRAN Tran1 WITH MARK;
COMMIT TRAN;
How do you restore the database to a specific transaction mark?
RESTORE DATABASE TSQL2012 FROM DISK = ‘C:\SQLBackups\TSQL2012.bak’
WITH NORECOVERY;
GO
RESTORE LOG TSQL2012 FROM DISK = ‘C:\SQLBackups\TSQL2012.trn’
WITH STOPMARK = ‘Tran1’;
GO
- You must use the transaction name with STOPMARK
- You can place a description after the clause WITH MARK, but SQL Server ignores it
- You can restore to just before the transaction with STOPBEFOREMARK.
- You can recover the dataset by restoring with either WITH STOPMARK or STOPBEFOREMARK.
- You can add RECOVERY to the WITH list but it has no effect.
What are Savepoints?
Savepoints are locations within a transaction that you can use to roll back a selective subset of work. (1) You can define a savepoint by using the SAVE TRANSACTION command. (2) The ROLLBACK statement must reference the savepoint. Otherwise, if the statement is unqualified, it will rollback the entire transaction.
What are Cross-database transactions?
Cross-database transactions span two or more databases on a single instance of SQL Server without any additional work on the user’s part. (1) SQL Server preserves the ACID properties of cross-database transactions without any additional considerations. (2) There are limitations on database mirroring when using cross-database transactions. A cross database transaction may not be preserved after a failover of one of the databases.
What are Distributed transactions?
Distributed transactions span more than one server by using a linked server.
How many COMMITs must be executed in a nested transaction to ensure that the entire transaction is committed?
One COMMIT must be issued for each level of the nested transaction. However, only the last COMMIT actually commits the entire transaction.
What are the two general modes of locking in SQL Server?
(1) Shared locks - Used for sessions that read data - readers. (2) Exclusive locks - Used for changes to data - writers.
Note that there are more advanced modes called update, intent, and schema locks used for special purposes.
What happens when a session sets out to change data?
SQL Server will attempt to secure an exclusive lock on the objects in question. These exclusive locks always occur in the context of a transaction, even if only in the autocommit mode. When a session has an exclusive lock on an object (row, table), no other transaction can change that data until this transaction either commits or rolls back. Except in special isolation levels, other sessions cannot even read exclusively locked objects.
What happens when a session sets out to read data?
By default, SQL Server will issue very brief shared locks on the resource (row and table). Two or more sessions can read the same objects because shared locks are compatible with other shared locks.
What happens when a session has a resource locked exclusively?
No other session can read the resource in addition to not being able to write to the resource.
The other session must wait until the first releases it’s exclusive lock.
Only shared locks are compatible with each other. An exclusive lock is not compatible with any other kind of lock.
What is the most granular type of resource?
The most granular type of resource for SQL Server is the row of a table. However, SQL Server may need to place lock on an entire page or an entire table.
How long are exclusive locks held?
In a transaction, exclusive locks are held to the end of the transaction (commit or rollback).
How long are shared locks held?
Shared locks are released as soon as the data is read, and they are not held to the end of the transaction except in higher isolation levels.
What is a deadlock?
A deadlock results from mutual blocking between two or more sessions. This occurs due to a cyclical relationship between two or more commands. SQL Server detects this cycle as a deadlock between the two sessions, aborts one of the transactions, and returns an error message 1205 to the client.
Can readers block writers?
Yes, even if only momentarily, because any exclusive lock request has to wait until the shared lock is released.
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.
What are the 6 different isolation levels?
(1) READ COMMITTED, (2) READ UNCOMMITTED, (3) READ COMMITTED SNAPSHOT, (4) REPEATABLE READ, (5) SNAPSHOT, (6) SERIALIZABLE