Begin Tran-Commit Tran

Begin Tran-Commit Tran

Post by Shailesh Khana » Wed, 26 May 1999 04:00:00



I know that even if you have nested Begin and Commit trans, rollback
will roll back everything within the outermost begin commit.

I am processing about 200,000 records so I wanted to commit every 1000,
and I have a construct like the following:

Counter=0
Begin tran
start while loop
    counter=counter+1

    if counter=1000
        commit tran
        counter = 0
        begin tran
   endif

  ---process records
end while
commit tran

I expected it to commit for every 1000 records and start a new
transaction for next 1000. My procedure bombed and it seems it is
rolling back all the records. It is meant to be that way in SQL, has
anyone tried commiting by parts.

For me, since I am commiting and starting a new transaction, it is not
nested transaction. Because commit comes before another begin. Why isn't
it working the way it is supposed?

--
Shailesh Kh*

 
 
 

Begin Tran-Commit Tran

Post by Alexander Tarasu » Wed, 26 May 1999 04:00:00


Seems like optimizer trying to build too largge plan for your proc.
Split it into 2 procedures one for external loop and one which
begin tran, loop 1000 records and commit them.
This should work.
Also you may find out that you need to give time for checkpoint to do it
job.
For this purpose you may call waitfor delay between your transactions.

-------------------------------------------
Alexander Tarasul
Microsoft Certified Solution Developer(SQL Server,VB)
Certified Borland Delphi Client Server Developer

http://www.veryComputer.com/~shleym
SQL Answers located at
http://www.veryComputer.com/~shleym/sql_answers.htm

---------------


>I know that even if you have nested Begin and Commit trans, rollback
>will roll back everything within the outermost begin commit.

>I am processing about 200,000 records so I wanted to commit every 1000,
>and I have a construct like the following:

>Counter=0
>Begin tran
>start while loop
>    counter=counter+1

>    if counter=1000
>        commit tran
>        counter = 0
>        begin tran
>   endif

>  ---process records
>end while
>commit tran

>I expected it to commit for every 1000 records and start a new
>transaction for next 1000. My procedure bombed and it seems it is
>rolling back all the records. It is meant to be that way in SQL, has
>anyone tried commiting by parts.

>For me, since I am commiting and starting a new transaction, it is not
>nested transaction. Because commit comes before another begin. Why isn't
>it working the way it is supposed?

>--
>Shailesh Kh*


 
 
 

Begin Tran-Commit Tran

Post by Shailesh Khana » Wed, 26 May 1999 04:00:00


I need to do it in the same procedure because the processing logic is quite
complex so I can't branch out to an external procedure.

The checkpoint only writes the pages in memory to disk, how does it matter
for rollback? My question was whether my logic is fine for splitting the
transaction and commiting every 1000 records.

Thanks


> Seems like optimizer trying to build too largge plan for your proc.
> Split it into 2 procedures one for external loop and one which
> begin tran, loop 1000 records and commit them.
> This should work.
> Also you may find out that you need to give time for checkpoint to do it
> job.
> For this purpose you may call waitfor delay between your transactions.

> -------------------------------------------
> Alexander Tarasul
> Microsoft Certified Solution Developer(SQL Server,VB)
> Certified Borland Delphi Client Server Developer

> http://www.veryComputer.com/~shleym
> SQL Answers located at
> http://www.veryComputer.com/~shleym/sql_answers.htm

> ---------------

> >I know that even if you have nested Begin and Commit trans, rollback
> >will roll back everything within the outermost begin commit.

> >I am processing about 200,000 records so I wanted to commit every 1000,
> >and I have a construct like the following:

> >Counter=0
> >Begin tran
> >start while loop
> >    counter=counter+1

> >    if counter=1000
> >        commit tran
> >        counter = 0
> >        begin tran
> >   endif

> >  ---process records
> >end while
> >commit tran

> >I expected it to commit for every 1000 records and start a new
> >transaction for next 1000. My procedure bombed and it seems it is
> >rolling back all the records. It is meant to be that way in SQL, has
> >anyone tried commiting by parts.

> >For me, since I am commiting and starting a new transaction, it is not
> >nested transaction. Because commit comes before another begin. Why isn't
> >it working the way it is supposed?

> >--
> >Shailesh Kh*

--
Shailesh Kh*
LSS Corporation
(412) 922-1800
 
 
 

Begin Tran-Commit Tran

Post by <tony> » Wed, 26 May 1999 04:00:00


This shouldn't be a problem, in fact I've done this type of thing more times
than you count.

Are you absolutely sure there is no existing transaction ?


--
Hope the above helps
-----------------------------------
Tony Rogerson MCP / SQL Server MVP
Torver Computer Consultants Ltd.
London, England


Quote:>I know that even if you have nested Begin and Commit trans, rollback
>will roll back everything within the outermost begin commit.

>I am processing about 200,000 records so I wanted to commit every 1000,
>and I have a construct like the following:

>Counter=0
>Begin tran
>start while loop
>    counter=counter+1

>    if counter=1000
>        commit tran
>        counter = 0
>        begin tran
>   endif

>  ---process records
>end while
>commit tran

>I expected it to commit for every 1000 records and start a new
>transaction for next 1000. My procedure bombed and it seems it is
>rolling back all the records. It is meant to be that way in SQL, has
>anyone tried commiting by parts.

>For me, since I am commiting and starting a new transaction, it is not
>nested transaction. Because commit comes before another begin. Why isn't
>it working the way it is supposed?

>--
>Shailesh Kh*

 
 
 

1. begin tran and commit tran

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 ***

2. (Another) Logsegment full question

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

4. ???What's wrong in my connectionstring(connect to SQLSERVER2k)???

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

6. ODBC and VFP

7. Begin, Commit tran - tran count not matched

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

9. begin tran / commit tran

10. begin/end begin tran/commit in while

11. Programmatic BEGIN TRAN, ROLLBACK and COMMIT

12. Performance realted to begin/commit tran