November 30, 2007 at 12:03 am
I have a stored procedure which is broken into several steps. Steps are updating different columns of certain rows, which match the criteria. We a talking 30k rows out of 300k rows. This table is heavily used by other services and procedures, so it is important to minimize locking on it.
The problem i have that if for instance step 6 out of 6 fails all what was done before has to be rolled back.
If i will do everything as a whole transaction it is going to keep updated rows locked for a while (from 10-20 minutes), which is unacceptable.
I need to sort of commit the transaction in each step, to release the lock, but have points in time to which i can rollback.
Can i achieve this by using SAVE TRANSACTION?
November 30, 2007 at 12:40 am
no !
Savepoints only work within your transaction.
(check BOL)
You'll need to search for other solutions to minimize your locking elaps time.
- Try to optimize setbased handling of your data.
- AVOID cursors
- try to reduce your procedure sequences (proc1 ... proc2 ..proc777)
to get your functionality done.
- finetune your queries.
...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 30, 2007 at 12:49 am
but will using SAVE sort of commit the transaction but will make reversible
November 30, 2007 at 1:56 am
Books online explains it !
A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. If a transaction is rolled back to a savepoint, it must proceed to completion with more Transact-SQL statements if needed and a COMMIT TRANSACTION statement, or it must be canceled altogether by rolling the transaction back to its beginning. To cancel an entire transaction, use the form ROLLBACK TRANSACTION transaction_name. All the statements or procedures of the transaction are undone.
Play with it before you implement it, so you can see what it is doing.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 30, 2007 at 5:51 am
What about literally breaking the query up into smaller queries? You can call them all from a single wrapper query. You get more discrete, tuneable execution plans and you can isolate the transactions easier. It's not a panacea, but it should help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply