Link to home
Start Free TrialLog in
Avatar of curiouswebster
curiouswebsterFlag for United States of America

asked on

T-SQL: New to using transactions

I am in a job where I sometimes need to make a "hot fix" to the live production database, and have been told we need to start using transactions to reduce the risk of causing a bigger problem than we are solving with the query.

Please give me some idea how to approach this.

Can a transaction be rolled back at a later time? How would we fit a test phase into this method, where a test may involve checking on the production version of our website that the change is being properly displayed??

Thanks
SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of curiouswebster

ASKER

I was afraid of that...

For the use I suggested, of directly updating a production database, what kinds of assertions or validations can I make before executing commit?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I do clearly see the value of using transactions, particularly when any part of a workflow can fail, like the bank withdrawal example. But I will have very specific updates on a production database, and the query will not be part of any workflow.

For example, in a specific table, for three unique records, change a NULL string column to contain "No".

The only way I can see to "test" this update was executed correctly is to log onto our website and select each of the three accounts, one by one, and verifying the value for a specific control is "No".

So, regarding transactions for this kind of dangerous update, how can I test it?

I suppose, if I wanted to protect from a catastrophic error, like using a "where" condition that's too general, which updates the entire table instead of just the three rows, I could get fancy and do the following in a transaction:

1) query the number of rows in the table with a value of "No"
2) run and update query on the three rows that I want to switch from NULL to "No"
3) query the number of rows in the table with a value of "No" and if the number is 3 larger than before, COMMIT
4) Otherwise, ROLLBACK

Is this how I can protect myself from being the cause of a terrible error?

On another topic, I like the idea of a backup but current database on which to try the query. What database tools are there to create a virtually identical copy of the database? But do so with the click of a button?

That's another way to reduce the risk of the solution becoming more a problem than what was originally being fixed.

Thanks
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes, I always do a SELECT before any UPDATE. But at my company was told I should always use a transaction when updating live data, but actually see no value to doing this since it can not be tested until after it's committed, when it's too late .

Thanks for all the help!