Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
na2173
Starting Member
38 Posts |
 Posted - 2002-08-08 : 13:02:00
|
| Is there a way we can set the time for a Transaction For example if i began batch that uses a Transacation and a update is done on a table, if a transaction does not complete say for 60 seconds i want to abort that transaction, is there any way we can do that |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-08 : 13:36:06
|
| Not from within the transaction itself. You'd have to monitor the process and then kill the spid (process ID) after 60 seconds or so. Plus, if you abort after 60 seconds, SQL Server will just spend another 60 seconds rolling back the transaction...there's no way to avoid it. A better approach would be break a large update operation up into smaller pieces. Say if you have to update 100,000 rows, you can use SET ROWCOUNT 10000 to do 10,000 rows at a time, and loop the same update statement until all of the rows are finished. It might take a little longer but the smaller batch size will allow for finer transaction control, and won't leave you with a very large transaction that could fill the log or take a long time to rollback. This ability does depend on the kinds of transactions that are more-or-less self-describing, like "UPDATE TableA SET col1=1 WHERE col1<>1".It's a bad idea to break off a transaction just because it takes too long...do you need to perform the update or not? If it's important, then it needs to be done no matter how long it takes. If not, then don't run it in the first place. You wouldn't put off a heart transplant just because the doctor takes a long time to perform the surgery! |
 |
|
|
|
|
|
Setting time out for a Transaction