begin tran and commit tran

begin tran and commit tran

Post by Matt Douha » Mon, 25 Nov 2002 02:40:49



Hi

I am currently trying to avoid damage to my db during failure conditions

I have a a simple update caluse as follows

update jongel


where the variables are supplied by the GUI frontend to a SP, now how can I
avoid table corruption if there is a power outage during the update?

I tried with begin tran and commit tran but what if there is a series of
updates and if any one of them fail I want the others undone, how can I
handle that, like follows

/* start of a series of updates */
update jongel


update jongel2


/* end of the series of updates */

is it possible that if the second update fails to undo all updates ??

--
Matt Douhan
www.fruitsalad.org

*** ping elvis ***
*** elvis is alive ***

 
 
 

begin tran and commit tran

Post by BP Margoli » Mon, 25 Nov 2002 08:34:20


Matt,

begin transaction
update jongel


update jongel2


commit transaction

Essentially the definition of a transaction is that either all the actions
within the transaction space are permanently written to the database, or
none of the action are written ... it's a pretty simple either / or
situation   :-)

You might take a look at the section "Controlling Transactions" in the SQL
Server Books Online for additional information.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> Hi

> I am currently trying to avoid damage to my db during failure conditions

> I have a a simple update caluse as follows

> update jongel


> where the variables are supplied by the GUI frontend to a SP, now how can
I
> avoid table corruption if there is a power outage during the update?

> I tried with begin tran and commit tran but what if there is a series of
> updates and if any one of them fail I want the others undone, how can I
> handle that, like follows

> /* start of a series of updates */
> update jongel


> update jongel2


> /* end of the series of updates */

> is it possible that if the second update fails to undo all updates ??

> --
> Matt Douhan
> www.fruitsalad.org

> *** ping elvis ***
> *** elvis is alive ***


 
 
 

begin tran and commit tran

Post by Doug Mill » Thu, 28 Nov 2002 08:25:29



> Matt,

> begin transaction
> update jongel


> update jongel2


> commit transaction

note also that you have to have all of these in the same connection.
so if you are doiong some OOP app that creates a new connection each
time, you have to rethink your strategy.
same connection though, and this is EXACTLY what you are after.
-doug miller

> Essentially the definition of a transaction is that either all the actions
> within the transaction space are permanently written to the database, or
> none of the action are written ... it's a pretty simple either / or
> situation   :-)

> You might take a look at the section "Controlling Transactions" in the SQL
> Server Books Online for additional information.

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > Hi

> > I am currently trying to avoid damage to my db during failure conditions

> > I have a a simple update caluse as follows

> > update jongel


> > where the variables are supplied by the GUI frontend to a SP, now how can
>  I
> > avoid table corruption if there is a power outage during the update?

> > I tried with begin tran and commit tran but what if there is a series of
> > updates and if any one of them fail I want the others undone, how can I
> > handle that, like follows

> > /* start of a series of updates */
> > update jongel


> > update jongel2


> > /* end of the series of updates */

> > is it possible that if the second update fails to undo all updates ??

> > --
> > Matt Douhan
> > www.fruitsalad.org

> > *** ping elvis ***
> > *** elvis is alive ***

 
 
 

begin tran and commit tran

Post by xAvai » Thu, 28 Nov 2002 22:43:47


Matt,

Quote:> I am currently trying to avoid damage to my db during failure conditions

If your failure conditions include power outages, look into
battery-packed disk cache to maintain your data consistent.

Experts, correct me if I am wrong but, transactions won't help if the
power goes out?

Hope that helps,
BZ


> Hi

> I am currently trying to avoid damage to my db during failure conditions

> I have a a simple update caluse as follows

> update jongel


> where the variables are supplied by the GUI frontend to a SP, now how can I
> avoid table corruption if there is a power outage during the update?

> I tried with begin tran and commit tran but what if there is a series of
> updates and if any one of them fail I want the others undone, how can I
> handle that, like follows

> /* start of a series of updates */
> update jongel


> update jongel2


> /* end of the series of updates */

> is it possible that if the second update fails to undo all updates ??

 
 
 

begin tran and commit tran

Post by BP Margoli » Fri, 29 Nov 2002 02:42:24


BZ,

Quote:> Experts, correct me if I am wrong but, transactions won't help if the
> power goes out?

One of the most fundamental tenets of enterprise level RDBMS's is that they
must conform to the ACID properties (defined in section "Transactions" of
the SQL Server Books Online. The ACID properties essentially define the
characteristics of a transaction.

If the server crashes for any reason, all enterprise level RDBMS's will
rollback uncommitted transactions when the database is restarted.

I think you might be referring more to the following (from "Inside Microsoft
SQL Server 2000" by Kalen Delaney, and quoted by permission of the author):

"Your disk controller must guarantee that any write operation reported to
the operating system as successful will actually be completed. You should
never use a write-back caching controller that can "lie" (report a write as
completed without guaranteeing that it will actually perform the write)
because your databases can become corrupted. In a write-back cache scheme,
performance is increased because the bits are simply written to the cache
(memory) and the I/O completion is immediately acknowledged. The controller
writes the bits to the actual media a moment later. This introduces a timing
window that makes the system vulnerable unless the controller designer has
protected the cache with a battery backup and has provided correct recovery
logic.

Let's say, for example, that a power failure occurs immediately after the
controller reports that a write operation has completed but before the cache
is actually written to disk. With write-ahead logging, SQL Server assumes
that any change is physically written to its transaction log before it
acknowledges the commit to the client. If the controller has just cached the
write and then fails and never completes it, the system's integrity is
broken. If a controller provides write-back caching as a feature, it must
also guarantee that the writes will be completed and that they will be
properly sequenced if they are reported to the operating system as
successful. To be reliable, the caching controller also must guarantee that
once it reports that an I/O has completed, the I/O will actually be carried
out, no matter what. Such a controller typically employs a built-in battery
backup and has solutions that guarantee that any write reported as completed
will in fact be completed. If the system fails before a write operation has
been completed, the operation is maintained in the controller's memory and
is performed immediately after the system restarts, before any other I/O
operations occur.

If a controller cannot guarantee that the write will ultimately be
completed, you should disable the write-back caching feature of the
controller or use a different controller. Disabling write-back caching in an
I/O-intensive environment (more than 250 I/Os per second) might result in a
SQL Server performance penalty of less than 5 percent. (After all, those
writes must be performed eventually. The caching reduces the latency, but
ultimately just as much I/O must be carried out.) In a less I/O-intensive
test, the effect would likely be negligible. Realistically, the amount of
the penalty doesn't matter much if the write isn't guaranteed to complete.
Write-back caching must be disabled, or you run the risk of corrupting your
data."

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> Matt,

> > I am currently trying to avoid damage to my db during failure conditions

> If your failure conditions include power outages, look into
> battery-packed disk cache to maintain your data consistent.

> Experts, correct me if I am wrong but, transactions won't help if the
> power goes out?

> Hope that helps,
> BZ




- Show quoted text -

> > Hi

> > I am currently trying to avoid damage to my db during failure conditions

> > I have a a simple update caluse as follows

> > update jongel


> > where the variables are supplied by the GUI frontend to a SP, now how
can I
> > avoid table corruption if there is a power outage during the update?

> > I tried with begin tran and commit tran but what if there is a series of
> > updates and if any one of them fail I want the others undone, how can I
> > handle that, like follows

> > /* start of a series of updates */
> > update jongel


> > update jongel2


> > /* end of the series of updates */

> > is it possible that if the second update fails to undo all updates ??

 
 
 

begin tran and commit tran

Post by Doug Mill » Fri, 29 Nov 2002 08:01:50



> Experts, correct me if I am wrong but, transactions won't help if the
> power goes out?

Sure it does. Transactions are EXACTLY for that kind of thing.

-Doug Miller

 
 
 

begin tran and commit tran

Post by xAvai » Wed, 04 Dec 2002 04:39:24


BP:

Quote:>>If the controller has just cached the
>>write and then fails and never completes it, the system's integrity is
>>broken.

That is exactly what I meant :)
I knew I read that somewhere, now I know exactly where it is!

Thanks for the response.

BZ


> BZ,

> > Experts, correct me if I am wrong but, transactions won't help if the
> > power goes out?

> One of the most fundamental tenets of enterprise level RDBMS's is that they
> must conform to the ACID properties (defined in section "Transactions" of
> the SQL Server Books Online. The ACID properties essentially define the
> characteristics of a transaction.

> If the server crashes for any reason, all enterprise level RDBMS's will
> rollback uncommitted transactions when the database is restarted.

> I think you might be referring more to the following (from "Inside Microsoft
> SQL Server 2000" by Kalen Delaney, and quoted by permission of the author):

> "Your disk controller must guarantee that any write operation reported to
> the operating system as successful will actually be completed. You should
> never use a write-back caching controller that can "lie" (report a write as
> completed without guaranteeing that it will actually perform the write)
> because your databases can become corrupted. In a write-back cache scheme,
> performance is increased because the bits are simply written to the cache
> (memory) and the I/O completion is immediately acknowledged. The controller
> writes the bits to the actual media a moment later. This introduces a timing
> window that makes the system vulnerable unless the controller designer has
> protected the cache with a battery backup and has provided correct recovery
> logic.

> Let's say, for example, that a power failure occurs immediately after the
> controller reports that a write operation has completed but before the cache
> is actually written to disk. With write-ahead logging, SQL Server assumes
> that any change is physically written to its transaction log before it
> acknowledges the commit to the client. If the controller has just cached the
> write and then fails and never completes it, the system's integrity is
> broken. If a controller provides write-back caching as a feature, it must
> also guarantee that the writes will be completed and that they will be
> properly sequenced if they are reported to the operating system as
> successful. To be reliable, the caching controller also must guarantee that
> once it reports that an I/O has completed, the I/O will actually be carried
> out, no matter what. Such a controller typically employs a built-in battery
> backup and has solutions that guarantee that any write reported as completed
> will in fact be completed. If the system fails before a write operation has
> been completed, the operation is maintained in the controller's memory and
> is performed immediately after the system restarts, before any other I/O
> operations occur.

> If a controller cannot guarantee that the write will ultimately be
> completed, you should disable the write-back caching feature of the
> controller or use a different controller. Disabling write-back caching in an
> I/O-intensive environment (more than 250 I/Os per second) might result in a
> SQL Server performance penalty of less than 5 percent. (After all, those
> writes must be performed eventually. The caching reduces the latency, but
> ultimately just as much I/O must be carried out.) In a less I/O-intensive
> test, the effect would likely be negligible. Realistically, the amount of
> the penalty doesn't matter much if the write isn't guaranteed to complete.
> Write-back caching must be disabled, or you run the risk of corrupting your
> data."

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > Matt,

> > > I am currently trying to avoid damage to my db during failure conditions

> > If your failure conditions include power outages, look into
> > battery-packed disk cache to maintain your data consistent.

> > Experts, correct me if I am wrong but, transactions won't help if the
> > power goes out?

> > Hope that helps,
> > BZ



> > > Hi

> > > I am currently trying to avoid damage to my db during failure conditions

> > > I have a a simple update caluse as follows

> > > update jongel


> > > where the variables are supplied by the GUI frontend to a SP, now how
>  can I
> > > avoid table corruption if there is a power outage during the update?

> > > I tried with begin tran and commit tran but what if there is a series of
> > > updates and if any one of them fail I want the others undone, how can I
> > > handle that, like follows

> > > /* start of a series of updates */
> > > update jongel


> > > update jongel2


> > > /* end of the series of updates */

> > > is it possible that if the second update fails to undo all updates ??

 
 
 

1. BEGIN TRAN and COMMIT TRAN from a three tier system

Hello All,

We have a three tier application; the back-end being
SQLServer, the front-end being a Java application and the
middle-tier being a web-logic server.

The users enter data via the Java application. Yesterday,
I was going through the profiler when I came across
something. I noticed that for every transaction, it puts
another dummy transaction,ie, in the example shown below,
BEGIN TRAN -- usp_Booking_Insert -- COMMIT TRAN is one and

Now, I dont know why it introduces the second transaction.
I noticed that it consumes about 16ms in the profiler.
Since our application is an OLTP application, I'm worried
because next month we will adding another 100 users.

I would really appreciate some help from others.

Thanks,
rgn

BEGIN TRAN



2. Networked Database Lockups

3. begin tran / commit tran

4. SP Encryption

5. Help: multiple calls and BEGIN TRAN....COMMIT TRAN

6. NJ-Lincroft-260933--Java-Javascript-Perl-ORACLE-UNIX-Unix Developer

7. Begin Tran-Commit Tran

8. ms-SQL server on a Novell3.12 LAN

9. Help: simultaneous calls of a SP containing BEGIN TRAN....COMMIT TRAN

10. Begin, Commit tran - tran count not matched

11. begin/end begin tran/commit in while

12. Programmatic BEGIN TRAN, ROLLBACK and COMMIT

13. Performance realted to begin/commit tran