Database Reference
In-Depth Information
•
Do not require input from users during a transaction.
Get all required input from users before a transaction is started. If additional user
input is required during a transaction, roll back the current transaction and restart
the transaction after the user input is supplied. Even if users respond immediately,
human reaction times are vastly slower than computer speeds. All resources held
by the transaction are held for an extremely long time, which has the potential to
cause blocking problems. If users do not respond, the transaction remains active,
locking critical resources until they respond, which may not happen for several
minutes or even hours.
•
Do not open a transaction while browsing through data, if at all possible.
Transactions should not be started until all preliminary data analysis has been
completed.
•
Keep the transaction as short as possible.
After you know the modifications that have to be made, start a transaction,
execute the modification statements, and then immediately commit or roll back.
Do not open the transaction before it is required.
•
Make intelligent use of lower cursor concurrency options, such as optimistic
concurrency options.
In a system with a low probability of concurrent updates, the overhead of dealing
with an occasional “somebody else changed your data after you read it” error can
be much lower than the overhead of always locking rows as they are read.
•
Access the least amount of data possible while in a transaction.
The smaller the amount of data that you access in the transaction, the fewer the
number of rows that will be locked, reducing contention between transactions.
How to Code Transactions
The following three T-SQL statements control transactions in SQL Server:
BEGIN TRANSACTION
: This marks the beginning of a transaction.
•
COMMIT TRANSACTION
: This marks the successful end of a transaction. It signals the
database to save the work.
•
•
ROLLBACK TRANSACTION
: This denotes that a transaction hasn't been successful and
signals the database to roll back to the state it was in prior to the transaction.
Note that there is no
END TRANSACTION
statement. Transactions end on (explicit or implicit) commits
and rollbacks.
Coding Transactions in T-SQL
You'll use a stored procedure to practice coding transactions in SQL. It's an intentionally artificial
example but representative of transaction-processing fundamentals. It keeps things simple so you can
focus on the important issue of what can happen in a transaction. That's what you really need to
