intro
SQL transactions are a part of the life of every DBA. Every developer has heard of them, too – and they’re not too hard to explain. Look at the query below:
Here’s what’s happening:
That’s a transaction. Not that hard to comprehend!
What is an SQL Transaction?
In simple terms, a transaction in a database world symbolizes “work” that the database needs to perform at once. Think of transactions as units – units that contain work for the database. You start a transaction, define work the database needs to do, then finish it up by committing, or saving, your changes inside of the database.
Transactions can not only be committed (saved) - they can also be rolled back in case an accident occurs. For that, we use the ROLLBACK statement.
Understanding SQL Transactions
Before you start using transactions in your database management system, there are a couple of things you need to know:
Other database management systems may implement the process a little differently, but the core premise is the same.
Working with SQL Transactions
Now that you understand SQL transactions from the inside, working with them is a piece of cake. There are only a couple of things you need to keep in mind for your transactions to help your database out:
SQL transactions should be used whenever there's a possibility of someone else meddling in between you and your data.
Building SQL Transactions
Already have a good understanding of the aforementioned aspects? Perfect, time to build your transaction. Use a trusted SQL client like the one provided by DbVisualizer, and follow these steps – follow them yourself, and later check on our example below:
As easy as that!
Our SQL queries should now look like this (“//” and “--” denote comments):
1
autocommit = 0; // disabling automatic commits
2
START TRANSACTION; -- beginning a transaction
3
-- setting variables
4
@demo_variable = 100;
5
@username = 'Jack';
6
UPDATE accounts SET balance = balance-@demo_variable WHERE username = @username; -- using variables in a transaction.
7
COMMIT;
In this transaction, we set two variables, then run an UPDATE statement. Not too complex, but not too shabby either. Keep in mind that the more complex your transactions are, the more time it will take for your database to execute them. You may also need to remove indexes if you’re running UPDATE / DELETE / INSERT queries for your queries to be faster.
Real Life Examples and Caveats of SQL transactions
Transactions are great and we’ve provided an example of how they can be used above. In the real world, things aren’t always as simple as running an UPDATE though – look at this example to start with. Some of the answers within that StackOverflow post illustrate transactions in MySQL well: they are only a thing if we use the InnoDB or XtraDB (the same InnoDB, but with enhanced capabilities) storage engines since they’re the only storage engines that support ACID.
Bear in mind that in some cases, you may also need “checkpoints” to save your progress – your database calls them “savepoints.” A savepoint is basically an identifier of a checkpoint for a transaction and this identifier can be used for rollback operations, release operations, etc. To use a savepoint, use the SAVEPOINT query and after that, name your savepoint. See an example below:
After you know about savepoints, educate yourself on deadlocks too: a deadlock is basically a “deadly” situation for your database where two or more transactions are waiting for each other to release a resource.
To avoid deadlocks, avoid “Coffman” conditions: these are mutual exclusion, hold-and-wait scenario, circular waiting (waiting for one another), and no preemption possible.
Make sure these conditions are not present in your transactions, save your progress and know your way around ACID – that should help. After you know your way around those, consider using top-rated SQL clients to work with your database: they will help you complete tedious tasks of a DBA with ease. Also, follow our blog – we share a lot of interesting stuff here too!
Summary
In this blog, we’ve walked you through what transactions in your database consist of. Keep in mind that for transactions to be possible, it’s wise to use ACID-compliant database management systems such as MySQL and be aware of the conditions that can make your transactions hell.
Make sure to try the powerful features of DbVisualizer to keep your databases out of trouble today, keep in touch with us by following our blog, say hi to the author if you’ve liked this blog, and until next time!
