Sql server tutorial 17 Managing SQL Transactions - XADAT.COM
post-template-default,single,single-post,postid-15847,single-format-standard,qode-quick-links-1.0,ajax_fade,page_not_loaded,,qode-theme-ver-11.1,qode-theme-bridge,wpb-js-composer js-comp-ver-5.1.1,vc_responsive

Sql server tutorial 17 Managing SQL Transactions No ratings yet.

Sql server tutorial 17 Managing SQL Transactions

Key Skills & Concepts

  • Understand SQL Transactions
  • Set Transaction Properties
  • Start a Transaction
  • Set Constraint Deferability
  • Create Savepoints in a Transaction
  • Terminate a Transaction

In Chapter 4, I spend a considerable amount of time discussing data integrity and the
methods supported by SQL to ensure that integrity. These methods include the creation
of constraints, domains, and assertions, all of which are used by your database in one way
or another to ensure that your SQL data remains valid. However, these methods alone are
not always enough to maintain the integrity of that data. Take, for example, the situation
that can arise when more than one user tries to access and modify data in the same table at
the same time, or when their actions overlap and impact the same data. Actions may be
taken by one user based on data that is no longer valid as a result of actions taken by the
other user. Data might become inconsistent or inaccurate, without either user knowing that
a problem exists. To address situations of this type, SQL supports the use of transactions to
ensure that concurrent actions do not impact the validity of the data that is seen by any one
user. In this chapter, I describe how transactions are implemented in an SQL environment
and how you can control their behavior. You will learn how to set transaction properties,
start transactions, terminate them, and use other options that extend their functionality.

Understand SQL Transactions

Relatively few databases exist in which only one user is trying to access data within that
database at any given point in time. For the most part, databases are used by different
types of users for many different purposes, and often these users are trying to access the
same data at the same time. The greater the number of users, the greater the likelihood that
problems will arise when users attempt to view or modify the same data at the same time.
However, problems can arise even if only two users are accessing data at the same time,
depending on the nature of their operations. For example, one user might view data in a
table, take some sort of action based on that data, then return to the table to verify the data
once more. However, if another user updates that table between the two times that the first
user views it, the first user will see different data the second time, and might even notice
that the action taken by the second user invalidated the change they made after they
viewed the table the first time. For example, the first user might notice that a customer’s
phone number is incorrect and apply the correction. However, a second user might be
looking at the same customer’s data, and while updating the customer’s credit status,
might inadvertently put the old phone number back into the database record (because the

data they were looking at contained the old number), overlaying the change that the first
user made.
To address these sorts of data inconsistencies, SQL uses transactions to control the
actions of individual users. A transaction is a unit of work that is made up of one or more
SQL statements that perform a related set of actions. For example, your application might
use a transaction to change the number of CDs in stock. The process of updating the
applicable table or tables and reporting the updated information back to you is treated as a
single transaction. The transaction might include a number of SQL statements, each
performing a specific task.
In order for a set of actions to qualify as a transaction, it must pass the ACID test.
ACID is an acronym commonly used when referring to the four characteristics of a
Atomic    This characteristic refers to the all-or-nothing nature of a transaction. Either
all operations in a transaction are performed or none are performed. If some statements
are executed, the results of these executions are rolled back if the transaction fails at
any point before it is completed. Only when all statements are executed properly and
all actions are performed is a transaction complete and the results of that transaction
applied to the database.

Consistent   The database must be consistent at the beginning and at the end of the
transaction. In fact, you can think of a transaction as a set of actions that takes the
database from one consistent state to another. All rules that define and constrain the
data must be applied to that data as a result of any changes that occur during the
transaction. In addition, all structures within the database must be correct at the end of
the transaction.

Isolated Data that might temporarily be in an inconsistent state during a transaction
should not be available to other transactions until the data is once again consistent. In
other words, no user should be able to access inconsistent data during a transaction
implemented by another user when the data impacted by the transaction is in an
inconsistent state. In addition, for a transaction to be isolated, no other transactions can
affect that transaction.

Durable Once the changes made by a transaction are committed, those changes must
be preserved, and the data should be in a reliable and consistent state, even if hardware
or application errors occur.

If any problems arise at any time during a transaction, the entire transaction is rolled
back and the database is returned to the state it was in before the transaction started. Any
actions that were taken are undone, and the data is restored to its original state. If the
transaction is successfully completed, all changes are implemented. Throughout the entire
process, regardless of whether the transaction is successfully completed or must be rolled
back, the transaction always ensures the integrity of the database.

SQL supports a number of statements related to transaction processing. You can use
these statements to begin and end transactions, set their properties, defer constraint
enforcement during the transaction, and identify places within a transaction that act as
stopping points when you roll back transactions. Throughout the rest of this chapter, we’ll

0 >1>2>3>4>5>6>7>8>9>10>11>12>13>14>15>16>17>18>19>20>



No Comments

Sorry, the comment form is closed at this time.