Transaction-Based Databases
Some databases use a form of SQL called Transact-SQL or T-SQL (Microsoft SQL Server and Sybase
Adaptive Server). This form of SQL extends regular SQL by adding the ability to create programs. These are
not programs in the sense that they are compiled or can stand alone, but they contain the same sets of
controls that most programming languages have, such as loops and IF statements. For example, a T-SQL
command set may look something like the following:
BEGIN TRAN update_orders
UPDATE Orders
SET Order_Shipped = 'Y'
WHERE Order_ID = 1454
IF @@ERROR = 0
BEGIN
COMMIT TRAN update_orders
END
ELSE
BEGIN
ROLLBACK TRAN update_orders
PRINT "Error encountered. Transaction failed"
ENDAs you can see, these statements are a little more complex than the SQL commands you have seen so
far. The ability to perform complex routines and include logic on the database server opens the doors
for numerous opportunities.These opportunities have been added as a feature set for these databases. They include stored
procedures, the ability to roll back transactions, and cursors. These features are not found in the current
version of MySQL. The reasons why they are not included, as well as workarounds for you die hards,
will be discussed next.BEGIN TRANS and ROLLBACK TRANS
These databases are built on transactions. A transaction is considered to be any action that can be
performed on a database. For example, every INSERT, UPDATE, or DELETE is considered a transaction, as
well as any CREATE or DROP statements.
Every T-SQL command is processed by the database system before it is executed. This allows the
engine to read all the commands and develop a good query plan. It will then execute the commands
based on its query plan. Because the engine sees all the commands before it actually executes them, it
allows for a very handy pair of features—the BEGIN TRAN and ROLLBACK TRAN.
The ROLLBACK TRAN is like a "Do Over." Remember when you were a kid and were playing kickball or
baseball and somebody did something with which somebody else didn't agree? Usually a shouting
match ensued. Then somebody would yell, "Do Over!!", and you replayed the last series of events as if
nothing ever happened. Transactional databases allow a "Do Over." You can state a series of
commands, and if something doesn't work out as expected, you can roll everything back and the
transaction will not be committed. Take a look at the following example:
BEGIN TRAN
DELETE FROM Customers
WHERE Customer_ID = 13
ROLLBACK TRAN
The database engine would create a query plan for this series of commands, but because a ROLLBACK
was issued instead of a COMMIT, the DELETE will never happen. The server would tell you how many
rows would have been affected had you committed this transaction, but that's all it would do. As you can
see, this is a pretty handy feature. You could safely try out your SQL commands with little fear of any
repercussions. You could see how long a large query would take without affecting any of the data in
your database. These are all really great things, but there is a price, and that price is speed. Because
the server must process every command that is issued, it is spending time looking and checking the
request instead of actually processing it, so a big performance hit is taken.