| Author |
sql server transaction configuration.
|
|
| sameer 2005-09-29, 1:23 pm |
| hi all, couple really easy questions for the guru out there.
1. is there any way i can totally switch off transactions in sql server so
that even if there are several statements with in a begin and commit\rollback
transaction , without waiting for either the commit or rollback , it just
commits all the changes.
2. If there is how can i swith it back to ON again.
3. Is there any default timeout value for a transaction ( not talking about
deadlock or locking timeouts) after which even if the query is doing inserts\
updates it rollsback everthing.
please suggest.
thanks
| |
| Jerry Spivey 2005-09-29, 8:23 pm |
| Sameer,
Transactions are used for data modifications whether or not they are
explicitly called or not. I think the only exception to this would be a
READ ONLY database.
HTH
Jerry
"sameer" <sameer@discussions.microsoft.com> wrote in message
news:BD46BC49-4585-4F43-BB7A- 35164F5B0B2F@microso
ft.com...
> hi all, couple really easy questions for the guru out there.
>
> 1. is there any way i can totally switch off transactions in sql server so
> that even if there are several statements with in a begin and
> commit\rollback
> transaction , without waiting for either the commit or rollback , it just
> commits all the changes.
>
> 2. If there is how can i swith it back to ON again.
>
> 3. Is there any default timeout value for a transaction ( not talking
> about
> deadlock or locking timeouts) after which even if the query is doing
> inserts\
> updates it rollsback everthing.
>
> please suggest.
> thanks
| |
| Geoff N. Hiten 2005-09-29, 8:23 pm |
| Comments Inline
"sameer" <sameer@discussions.microsoft.com> wrote in message
news:BD46BC49-4585-4F43-BB7A- 35164F5B0B2F@microso
ft.com...
> hi all, couple really easy questions for the guru out there.
>
> 1. is there any way i can totally switch off transactions in sql server so
> that even if there are several statements with in a begin and
> commit\rollback
> transaction , without waiting for either the commit or rollback , it just
> commits all the changes.
>
No.
> 2. If there is how can i swith it back to ON again.
>
No.
> 3. Is there any default timeout value for a transaction ( not talking
> about
> deadlock or locking timeouts) after which even if the query is doing
> inserts\
> updates it rollsback everthing.
>
No. You can create a maximum timeout for a resource wait, but this is a bad
thing as it tends to break transactional consistency. Transaction timeout
is typically controlled via the client application.
> please suggest.
> thanks
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
| |
| sameer 2005-09-29, 8:23 pm |
| thanks for your reply Geoff,
this one time when i was working on a sql server, i had couple insert
statements after a begin transaction and as soon as i ran it, it was almost
as if it was commited to the db even without a commint transaction. I could
not even roll it back, could you shed some light on this. I always though due
to some reason the dba might have swithed off the trasnaction handling and
made it such that changes are commited even if commit is not ran.
thanks
sameer
"Geoff N. Hiten" wrote:
> Comments Inline
> "sameer" <sameer@discussions.microsoft.com> wrote in message
> news:BD46BC49-4585-4F43-BB7A- 35164F5B0B2F@microso
ft.com...
> No.
>
> No.
>
> No. You can create a maximum timeout for a resource wait, but this is a bad
> thing as it tends to break transactional consistency. Transaction timeout
> is typically controlled via the client application.
>
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
>
| |
| sameer 2005-09-29, 8:23 pm |
| also is there any time out for abandoned transactions ?
"Geoff N. Hiten" wrote:
> Comments Inline
> "sameer" <sameer@discussions.microsoft.com> wrote in message
> news:BD46BC49-4585-4F43-BB7A- 35164F5B0B2F@microso
ft.com...
> No.
>
> No.
>
> No. You can create a maximum timeout for a resource wait, but this is a bad
> thing as it tends to break transactional consistency. Transaction timeout
> is typically controlled via the client application.
>
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
>
| |
| John J. Hughes II 2005-09-29, 8:23 pm |
| The SQL will roll back transactions if the connection is broken.
Regards,
John
"sameer" <sameer@discussions.microsoft.com> wrote in message
news:902AC15E-AF5F-44A2-BB5A- E0BED9D71AAA@microso
ft.com...[color=darkred]
> also is there any time out for abandoned transactions ?
>
> "Geoff N. Hiten" wrote:
>
| |
| sameer 2005-09-30, 3:23 am |
| how about abandoned transactions? how long do they stay active in the database.
"John J. Hughes II" wrote:
> The SQL will roll back transactions if the connection is broken.
>
> Regards,
> John
>
> "sameer" <sameer@discussions.microsoft.com> wrote in message
> news:902AC15E-AF5F-44A2-BB5A- E0BED9D71AAA@microso
ft.com...
>
>
>
| |
| Jerry Spivey 2005-09-30, 3:23 am |
| Sameer,
Ocassionaly a transaction will stay open and will prevent the log from
properly being truncated and may block other users to the locked data. The
DBCC OPENTRAN statement can be used to find these rouge transactions and the
KILL statement can be used to end them.
HTH
Jerry
"sameer" <sameer@discussions.microsoft.com> wrote in message
news:9BB78025-E660-4F7C-BE42- E5FA6FEBA944@microso
ft.com...[color=darkred]
> how about abandoned transactions? how long do they stay active in the
> database.
>
> "John J. Hughes II" wrote:
>
| |
| sameer 2005-09-30, 9:23 am |
| Jerry, but then won't there be a time out for transactions like these?
thanks
"Jerry Spivey" wrote:
> Sameer,
>
> Ocassionaly a transaction will stay open and will prevent the log from
> properly being truncated and may block other users to the locked data. The
> DBCC OPENTRAN statement can be used to find these rouge transactions and the
> KILL statement can be used to end them.
>
> HTH
>
> Jerry
> "sameer" <sameer@discussions.microsoft.com> wrote in message
> news:9BB78025-E660-4F7C-BE42- E5FA6FEBA944@microso
ft.com...
>
>
>
| |
| Jerry Spivey 2005-09-30, 9:23 am |
| No. KILL will need to be used.
"sameer" <sameer@discussions.microsoft.com> wrote in message
news:9B39F23F-7850-4745-B939- D44F6DCCEA30@microso
ft.com...[color=darkred]
> Jerry, but then won't there be a time out for transactions like these?
> thanks
>
> "Jerry Spivey" wrote:
>
| |
| Geoff N. Hiten 2005-09-30, 11:23 am |
| That should not happen. Perhaps you missed the BEGIN TRANSACTION statement
and it went into implicit transaction mode. Also, your connection can see
the changes without a commit, even though other ones can't (except those
using READ UNCOMMITTED isolation mode or using a WITH (NOLOCK) table hint.
GNH
"sameer" <sameer@discussions.microsoft.com> wrote in message
news:4F5CAB62-A13E-49A0-8BB5- A005974F2BED@microso
ft.com...[color=darkred]
> thanks for your reply Geoff,
> this one time when i was working on a sql server, i had couple insert
> statements after a begin transaction and as soon as i ran it, it was
> almost
> as if it was commited to the db even without a commint transaction. I
> could
> not even roll it back, could you shed some light on this. I always though
> due
> to some reason the dba might have swithed off the trasnaction handling and
> made it such that changes are commited even if commit is not ran.
>
> thanks
> sameer
>
> "Geoff N. Hiten" wrote:
>
| |
| Geoff N. Hiten 2005-09-30, 11:23 am |
| There is no automatic timeout, however SQL is notified by the OS that the
connection is broken, the rollback is immediate.
GNH
"sameer" <sameer@discussions.microsoft.com> wrote in message
news:9B39F23F-7850-4745-B939- D44F6DCCEA30@microso
ft.com...[color=darkred]
> Jerry, but then won't there be a time out for transactions like these?
> thanks
>
> "Jerry Spivey" wrote:
>
|
|
|
|