MySQL is not a transactional database system. It does not use T-SQL. You cannot issue a statement
like the one in the previous example and expect it to work. This may seem like a disadvantage. You
may be asking why MySQL doesn't have this feature. Well, here's why. MySQL is primed for speed and
ease of use. It takes time for the transactional database server to process every command. This is time
that could be spent executing your commands. This is one reason why MySQL doesn't use something
like T-SQL.
Another reason MySQL doesn't have the COMMIT/ROLLBACK TRANS feature is that it would take away
from the ease-of-use and low maintenance aspects of MySQL. In a transactional system, every
transaction is written to a special log called the transaction log. That is why it can roll back a transaction
if needed. The size of this log is set by the administrator. If he or she sets the size too small for the
number of transactions that occur, the log will become full and no more transactions will take place.
That means that nobody will be able to insert, update, or delete any records on a production database—
talk about disasters. This is a huge concern for most DBAs on transactional databases. They are very
concerned about this log—for good reason—so they watch it carefully. MySQL has a logging feature,
but it will not cause the sort of problems that the transaction log in a transactional database will cause.Hopefully, you can see why the people at TcX chose not to include this feature. It would take away from
the speed of MySQL, as well as increase the amount of administration that needs to be performed. This
feature probably will not show up in future releases of MySQL.Stored Procedures
Stored procedures are another feature that takes advantage of T-SQL. A stored procedure is a series of T-
SQL commands that have been compiled and stored within the database. The query plan for the series of
commands is saved, so future calls will execute quickly because a plan does not need to be created. It is
treated internally the same way a table or any other database object is treated.
Stored procedures have many advantages. One advantage is that stored procedures fit the modular
programming model in that they are created to serve a specific purpose. They also lend themselves to
reusability. Another benefit they provide is that they can take and pass parameters. This allows a
programmer to link several stored procedures together, creating a kind of batch program. Yet another
benefit is displaced security. A user, who may not have explicit permissions to a table, may be able to
use that table through a stored procedure. This is handy if you want to limit the user's access but still
provide him or her a way to manipulate data safely through a controlled environment that you provide.Stored procedures can become very complex. T-SQL provides most of the tools that programming
languages have to offer. In a stored procedure, a programmer can declare variables, create loops, and
execute conditional statements—not to mention any of the intrinsic functions the database server has to
offer. This can let a programmer accomplish a lot of the programming logic on the database server.One of the major benefits of stored procedures is that they execute faster than regular statements. This
is because the stored procedure saves the query plan. It has already thought out the best way to do
something. It's kind of like having your whole day planned for you; You just follow the plan. This is a lot
quicker then planning it as you go. It is also a lot cleaner in the application code to call one stored
procedure than it is to have a series of SQL statements.There are no real disadvantages to stored procedures. In fact, they are the preferred means of
executing T-SQL on a transaction-based database system. The only drawback is that you need them
beforehand. If a database was extremely fast—so fast that a series of statements executed from a
remote system returned results faster then a stored procedure—why would you need them anyway?
Understandably, it is nice to have code execute on the server and return just the results. But is it
enough to sacrifice the overwhelming speed MySQL possesses?The next version of MySQL may have a stored procedure option. Remember, the more fluff that is
added, the more speed is decreased. MySQL is fast enough without stored procedures, but sometimes
stored procedures make a lot of sense. For this, there is a workaround.Stored Procedure Workarounds
The only available workaround right now for stored procedures is using user-defined functions (UDF). UDFs
are functions written in C or C++. They are quite similar to the intrinsic functions of MySQL, and they are
called the same way within MySQL.