What’s the secret to never accidentally updating or deleting SQL data again? Wrap your SQL code in a TRANSACTION whenever your SQL is modifying data.
Below is how to do so. The full SQL code we used is at the end of this post as well.
The Scenario
Say for instance, you need to update a single Vendor’s Name. Please note that normally we would never do anything directly in SQL that can and should be done using the application, but for demonstration purposes, we’re keeping it simple.
SELECT * FROM PM00200 WHERE VENDORID = ‘PAT’
Once we find the record we need to update, we are ready to run the update script.
Good habits are to always take backups of the data you’re about to modify. This tip assumes you follow good practice and have a backup strategy of your own. For this example, we only backed up the table we’re updating.
SELECT * INTO PM00200_04242018 FROM dbo.PM00200
First, make sure you have records in that backup table.
SELECT * FROM PM00200_04242018
Once you confirm you have a good backup copy, you can issue the UPDATE statement.
BEGIN TRAN
UPDATE PM00200 SET VENDNAME = ‘PATRICK’
Evidently, we moved too quickly and affected all 104 records! The good news is that we started the statement with BEGIN TRAN. We can now issue a ROLLBACK, and it will UNDO all those 104 updates.
But before we do, let’s see what SQL is holding in memory.
SELECT * FROM PM00200
As we though, all 104 Vendors have been renamed to PATRICK. It’s a simple matter to undo it.
ROLLBACK
Our typical practice is to run COMMIT or ROLLBACK multiple times to ensure there are no open TRANSACTIONS. If you run ROLLBACK again, it should error out, letting you know there are no open TRANSACTIONS.
ROLLBACK
Alternatively, you can check for an open TRANSACTION.
DBCC OPENTRAN
There are no active open transactions. If there were, something like this would appear instead:
NOTE: When you use BEGIN TRAN, it starts a transaction and waits for the ROLLBACK and COMMIT to complete the TRANSACTION. Until the ROLLBACK or COMMIT is issued, those records are locked, impacting all users. Make sure you have your SQL code ready and are prepared to issue either a COMMIT or ROLLBACK relatively quickly as to not impact users of the system.
Now run the select again, and you should see the data back to the way it was.
Now that we’re back to where we started, we can see why our update affected all of the records instead of just one record.
Looks like we did not have the needed where clause. We can make the modification and run again. Remember to include the BEGIN TRAN!
BEGIN TRAN
UPDATE PM00200 SET VENDNAME = ‘PATRICK’ WHERE VENDORID = ‘PAT’
Much better result. Now we need to do one final check before we issue a COMMIT. Let’s make sure the update did what we anticipated.
SELECT * FROM PM00200 WHERE VENDORID = ‘PAT’
Yes, it did. While you are in the same session as the BEGIN TRAN, you can select the data and see what it is before you issue the COMMIT or ROLLBACK. Above shows the change. Once you are happy with this change, you can COMMIT it. If you aren’t happy, ROLLBACK again.
COMMIT
Our habit is to run it twice.
Now that we’re happy with the change, we can clean up after ourselves. We don’t need that backup anymore, so let’s drop that table.
BEGIN TRAN
DROP TABLE PM00200_04242018
Yes, BEGIN TRAN works on all SQL statements. If we wanted to, we could ROLLBACK this as well and it would UNDO the DROP. Until we COMMIT or ROLLBACK, it remains in memory. Let’s quickly check to make sure the right table was dropped.
SELECT * FROM PM00200_04242018
This is expected. We just dropped it, so it should not exist. Make sure to complete this transaction and issue a ROLLBACK (which will put this table back) or COMMIT (which will drop the table, deleting it for good.)
ROLLBACK
Run it again to make sure all transactions are complete.
SELECT * FROM PM00200_04242018
Since we rolled back, the table is still there. Let’s do it again and COMMIT.
BEGIN TRAN
DROP TABLE PM00200_04242018
COMMIT
Always run two or more times to make sure all transactions are complete.
Now that we know it has been both dropped and committed, if we try and select from it, we should get the following error.
SELECT * FROM PM00200_04242018
Summary
Whenever running SQL that updates, inserts, or deletes, you should get into the habit of using the BEGIN TRAN statement and then always make sure you COMMIT or ROLLBACK to complete the transactions.
Our Experience
The reason we started using the BEGIN TRAN, as you can imagine, was because of a simple SQL statement that was not fully validated and was executed before it was intended to be and the data was forever modified. From that point forward we made it a habit (even for the most simple updates) to use the BEGIN TRAN. It has saved us lots of work over the years. It follows the same idea of a carpenter to “measure twice, cut once.” Take a little extra time the first time, and it can prove to benefit you in the long run. The extra two minutes to wrap your code in BEGIN TRAN could save you a late night of trying to recover lost data.
Why do we run the ROLLBACK or COMMIT multiple times? I had at one point in my career ran a statement wrapped in a TRANSACTION and did not realize I had another TRANSACTION already open. I then went to lunch. (I left my SQL session open). When I came back from lunch, there were many upset users not to mention my boss and his boss, because the system was unusable. The records I had updated were common records used by most modules in the system. Since I used a BEGIN TRAN and did not COMMIT or ROLLBACK, it put a LOCK on every record I updated and no user could access the data. It basically brought the system to a halt. Of course, at that time I did not realize I had been the cause of this, and it had taken some additional time before realizing I had these records locked. I had not realized I had not COMMITTED all of my open TRANSACTIONS. Once I did everything was fine again.
What had happened was, while executing my scripts, I had run the BEGIN TRAN statement more than once without completing them by running the ROLLBACK or COMMIT statement. Sometimes when you run your SQL, it fails. If you start that SQL with a BEGIN TRAN, in some cases it starts a TRANSACTION, even if the SQL fails. I did not realize this. So now, to be safe, we always issue COMMIT or ROLLBACK more than one time to make sure there are no left TRANSACTIONS hanging open and potentially causing issues.
If you don’t have a dedicated IT partner to help you with your technology needs big and small, contact Sikich today and let’s see how we can help.
Full SQL Code Used
Below is the SQL we used for this example.
/*
Tip for updating records in SQL
Use TRANSACTIONS
Use Comments
I’m a big fan of commenting out my SQL statements when I have multiple and then I highlight and execute the ones I want, when I need to
Main purpose: If I accidentally press F5 or click the Execute button I won’t inadvertently run all the SQL. It will only
run what is not commented
Purpose:
By wrapping your SQL INSERT UPDATE or DELETE statement in a TRANSACTION you have the ability to ROLLBACK or COMMIT your changes.
This gives you an additional layer of checks and balances to make sure you update only what you intend to update.
If not, you can issue a ROLLBACK and it will be as-if you did not issue the SQL statement at all
PROS:
– Additional checks and balances. It allows you to control when the COMMIT happens and more importantly
gives you the option to ROLLBACK in the event something was off
– Piece of mind knowing if even the most simple of update statements goes wrong you can ROLLBACK
CONS:
– It holds onto that record and the records around it until the COMMIT or ROLLBACK is issued, potentially locking other users
– If you have many records to update it could take time and memory
– Can take a little more time to execute your statements because you have to wrap the code in the TRAN statement
– You can forget to issue the COMMIT or ROLLBACK and that locks the record and potentially other records around it
*/
/*-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
EXAMPLE:
Here’s a simple UPDATE statement scenario:
Need to update the Vendor Name for 1 specific vendorid
You write a SELECT statement to make sure you find this vendor and only this 1 vendor
You then write your update statement.
However, in a rush you forget to add the WHERE clause and it updates all records in the table.
If you wrap the SQL in a TRAN statement you can run the ROLLBACK command and undo what you accidentally did.
We’ve all been here and wish there were a way to undo what was done.
Hopefully a backup was taken. At minimum you had taken a backup of that table you updated.
If not, this one very minor and simple update has now ruined your day and you will be spending the time trying to get back all of your vendor names
DBCC OPENTRAN
*/
/*
Here are the steps I follow:
*/
/*-=-=-=-=-=-=-=-= Step 1: Take a backup. =-=-=-=-=-=-=-=-=-=-=-=-=-
Some situations require a full database backup and others only a table. This tip is assuming you know when to do what.
In this scenario I simply do a SELECT into and make a copy of the Vendor Master
–Make a backup of the table I am updating
SELECT * INTO PM00200_04242018 FROM dbo.PM00200
–Make sure you have records in that backup table
SELECT * FROM PM00200_04242018
*/
/*-=-=-=-=-=-=-=-= Step 2: Find the record I want to update. =-=-=-=-=-=-=-=-=-=-=-=-=-
–I do this to also find the number of records that should be updated.
SELECT * FROM PM00200 WHERE VENDORID = ‘PAT’
/*-=-=-=-=-=-=-=-= Step 3: Execute my update statement, wrapped in a TRANSACTION =-=-=-=-=-=-=-=-=-=-=-=-=-
–Here’s where the update occurs, within a TRANSACTION statement
–Highlight from BEGIN TRAN to the end of ‘PATRICK’
BEGIN TRAN
UPDATE PM00200 SET VENDNAME = ‘PATRICK’
–Confirm the number of records updated. Should be 1 and only 1. Anything other than that and I want to ROLLBACK and then figure out why.
–In this example I left the WHERE clause off and it updated all 104 Vendor Records in my Vendor Master table
–I used the BEGIN TRAN statement so it is waiting for a COMMIT or ROLLBACK
–Until I issue one of those commands the update is held in memory, in my session.
–IMPORTANT: All of the vendor records are LOCKED until the COMMIT or ROLLBACK is issued as well, so do not take too long.
–If you are unsure of something, run the ROLLBACK and it will reset everything and release the locks while you figure out the issue
–In my case I expected 1 record to be updated and it updated all 104. I obviously want to run the ROLLBACK.
–Before I ROLLBACK, however, run a select to see the data held in memory
SELECT * FROM PM00200
–You will see every vendor now has a name PATRICK
–Simply highlight the word ROLLBACK and click execute or F5 to run it.
–TIP:
–I always run the ROLLBACK or COMMIT 2 or more times. The reason for this is you could have inadvertently created multiple transactions waiting for a
–COMMIT or ROLLBACK.
–If you run a COMMIT or ROLLBACK and there is no open TRANSACTION it will produce an error.
–Alternatively you could check for an open tran by using DBCC OPENTRAN
COMMIT
ROLLBACK
–Add the WHERE clause and re-run
BEGIN TRAN
UPDATE PM00200 SET VENDNAME = ‘PATRICK’ WHERE VENDORID = ‘PAT’
–This time I got the expected result. 1 record updated.
–Now I can run the COMMIT
/*-=-=-=-=-=-=-=-= Step 4: If you are satisfied with the results, you can delete the backup table you created in Step 1 =-=-=-=-=-=-=-=-=-=-=-=-=-
–Yes you can wrap a DROP statement into a TRANSACTION as well
BEGIN TRAN
DROP TABLE PM00200_04242018
–Before you COMMIT or ROLLBACK, select from that table
SELECT * FROM PM00200_04242018
–This should produce an error because this object no longer exists.
–If you issue a ROLLBACK, it will undo your DROP
–Otherwise COMMIT the DROP to permanently delete that table
COMMIT
ROLLBACK
*/
