Board index » delphi » Re: help - major problems after I changed CommitRetaining to Commit etc

Re: help - major problems after I changed CommitRetaining to Commit etc


2008-07-31 02:47:35 AM
delphi211
Quote
Do not take these suggestions; it is horrible advice. See my post
elsewhere.
"Craig Stuntz [TeamB]"
>* You can change the line 'StartTransaction' with code 'if not
>InTransaction then StartTransaction'
This is bad advice. It hides problems caused by improper transaction
control elsewhere. You should try to make your coding mistakes
obvious, not bury them.
But, in Sansalone's situation, it is a next step to develope program's
structe. He was in a trouble. When you are saying better program and better
bug election, he was saying his program does not work ! With the bad advice,
Sansalone can show a finished work to his boss and find a chance to develop
his programming skills. I am sure he can noticed some other details in time.
At least We both know he looks at newsgroups regularly.
Quote
* Yo should not commit after a select statement.
This is completely wrong. Committing is always the best way to end a
transaction when you do not need to rollback a successful statement.
Indeed, InterBase will convert a rollback to a commit whenever it can.
You should not commit after a select statement.
Is this true or false ? It depends. It depends on when ? Connect a dbgrid to
a datasource and also connect it to query, transaction and database. Okay
now, execute a select and then call Transaction.Commit. This is what I try
to avoid Sansalone to do. You will see an empty grid. You know why.
I also know what you mean when you say I have to commit a transaction. But
When the form is destroyed (IBTransaction is on form) or program terminates
(IBTransaction is on datamodule), it already Commits.
By the way, I want to thank to you and the other people in TeamB who helps
us. With your advices, your tones of answers, taking your hours, you are
doing really good job and a lot people take benefits from.
Ömür
 
 

Re: help - major problems after I changed CommitRetaining to Commit etc

Ömür Ölmez writes:
Quote
But, in Sansalone's situation, it is a next step to develope
program's structe. He was in a trouble. When you are saying better
program and better bug election, he was saying his program does not
work !
The correct solution to this problem is to manage transactions
correctly, not to defensively steer around bugs until you've produced
spaghetti which doesn't seem to raise an exception in *one* specific
use case.
Quote
You should not commit after a select statement.

Is this true or false ?
I said this before, and I will say it again: you should *always*
commit unless there is a successfully-executed statement you wish to
rollback. You should not rollback in any other case, and you should
always explicitly and a transaction that you start. Hence, you should
always commit after a SELECT unless, in the same transaction, you
successfully executed DML which you now wish to undo.
If, in a transaction, you do a SELECT, and only a SELECT, there is
*never* a reason to rollback.
Quote
By the way, I want to thank to you and the other people in TeamB who
helps us. With your advices, your tones of answers, taking your
hours, you are doing really good job and a lot people take benefits
from.
You're welcome.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Want to help make Delphi and InterBase better? Use QC!
qc.borland.com -- Vote for important issues
 

Re: help - major problems after I changed CommitRetaining to Commit etc

Vm|r Vlmez writes:
Quote
You should not commit after a select statement.

Is this true or false ? It depends. It depends on when ? Connect a
dbgrid to a datasource and also connect it to query, transaction and
database. Okay now, execute a select and then call
Transaction.Commit. This is what I try to avoid Sansalone to do. You
will see an empty grid. You know why.

I also know what you mean when you say I have to commit a
transaction. But When the form is destroyed (IBTransaction is on
form) or program terminates (IBTransaction is on datamodule), it
already Commits.
In addition to Craig's comments let me point out a possible problem
with your suggestion to commit when a form is destroyed or when the
program terminates. I single form may be open for hours. An application
may be open all day. Both the OIT and the OAT will be frozen at the
oldest active transaction. If the OIT does not advance garbage
collection stops. In addition, all transactions from the OIT up to the
next transaction must be tracked on the transaction inventory pages
(TIP). In a high transaction volume environment where a large number of
updates occur this can cause the size of the TIP to become very large
and can cause a large number of record versions to accumulate. The
worst case I have encountered on a production server was a table with
1,600 rows that had 863,000 versions. One row had ove 57,000 versions.
Needless to say, performance when querying this table was very poor.
The point is that you need to design your transaction handling so that
transactions will be committed often enough to prevent the problems
described above. How do you prevent the user from doing something that
starts a transaction and displays some records then walking away for
hours and leaving the transaction active? The easy way is to always use
ClientDataSets to display data in the U/I.
--
Bill Todd (TeamB)
 

Re: help - major problems after I changed CommitRetaining to Commit etc

Thank you all for your help.
Joe
"Bill Todd [TeamB]" <XXXX@XXXXX.COM>writes
Quote
Vm|r Vlmez writes:

>You should not commit after a select statement.
>
>Is this true or false ? It depends. It depends on when ? Connect a
>dbgrid to a datasource and also connect it to query, transaction and
>database. Okay now, execute a select and then call
>Transaction.Commit. This is what I try to avoid Sansalone to do. You
>will see an empty grid. You know why.
>
>I also know what you mean when you say I have to commit a
>transaction. But When the form is destroyed (IBTransaction is on
>form) or program terminates (IBTransaction is on datamodule), it
>already Commits.

In addition to Craig's comments let me point out a possible problem
with your suggestion to commit when a form is destroyed or when the
program terminates. I single form may be open for hours. An application
may be open all day. Both the OIT and the OAT will be frozen at the
oldest active transaction. If the OIT does not advance garbage
collection stops. In addition, all transactions from the OIT up to the
next transaction must be tracked on the transaction inventory pages
(TIP). In a high transaction volume environment where a large number of
updates occur this can cause the size of the TIP to become very large
and can cause a large number of record versions to accumulate. The
worst case I have encountered on a production server was a table with
1,600 rows that had 863,000 versions. One row had ove 57,000 versions.
Needless to say, performance when querying this table was very poor.

The point is that you need to design your transaction handling so that
transactions will be committed often enough to prevent the problems
described above. How do you prevent the user from doing something that
starts a transaction and displays some records then walking away for
hours and leaving the transaction active? The easy way is to always use
ClientDataSets to display data in the U/I.

--
Bill Todd (TeamB)
 

Re: help - major problems after I changed CommitRetaining to Commit etc

What will the Commit within the finally do if the SQL updated something that
another
connection recently changed but didn't commit? In other words, if it can't
Commit, should
I have something in the code that does a rollback?
Should I put all of the below code within a try/except and then rollback in
the case of
the Commit throwing an exception?
If the Commit fails (and no rollback) then StartTransaction will throw an
exception on the next call because there's still an active transaction,
right?
The code below is the template for a successful transaction? Was that the
assumption?
The reason I ask all this is that I did change my code BUT I got all sorts
of exceptions.
However, I was using Delphi 6 ... tonight I will be testing the app with
D2007 - same code.
I wasn't sure if all the exceptions plus loss of db connection was because
of Delphi 6 ...
so to be safe, I am going with D2007.
"Craig Stuntz [TeamB]" <XXXX@XXXXX.COM [a.k.a. acm.org]>wrote
in message news:48907698$XXXX@XXXXX.COM...
Quote

The problem is that you are not explicitly, consistently managing
transaction state. Changing Transaction. Active := True to
Transaction.StartTransaction has only one effect: your application will
throw an exception if the transaction is already active. That is
generally what you want, because it catches cases where you have failed
to commit the transaction elsewhere, but it has no effect on the
"expecting explicit transaction start" error, because that error means
that you failed to start the transaction at all, via Active or
StartTransaction.

Change your code so that, in every case where you execute a query, you
explicitly start and commit the transaction, and, further, you protect
the call to Commit in a try/finally, this:

FIBTransaction.StartTransaction;
try
FIBSQL.ParamByName('spid').AsString := user.comp.SpID;
FIBSQL.ParamByName('companyid').AsString := user.comp.CoID;
FIBSQL.ParamByName('userid').AsString := user.UID;
FIBSQL.ParamByName('dimname').AsString := aDimName;
FIBSQL.ParamByName('dn').AsString := DN;

FIBSQL.ExecQuery;

FIBSQL.Close;
finally
FIBTransaction.Commit;
end;

This ensures that, even if the SQL statement raises an exception
(e.g., the user entered data which violates a constraint), but the
transaction will still be ended with a Commit. This prevents you from
accidentally leaving the transaction open, which should be your goal.

--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
IB 6 versions prior to 6.0.1.6 are pre-release and may corrupt
your DBs! Open Edition users, get 6.0.1.6 from mers.com
 

Re: help - major problems after I changed CommitRetaining to Commit etc

Joe Sansalone writes:
Quote
What will the Commit within the finally do if the SQL updated
something that another connection recently changed but didn't commit?
In other words, if it can not Commit, should I have something in the
code that does a rollback?
The commit in the finally block assumes that you are executing a SELECT
statement. If you are executing a statement that changes data then you
should call Rollback in the finally block.
Quote

Should I put all of the below code within a try/except and then
rollback in the case of the Commit throwing an exception?
Yes.
Quote

If the Commit fails (and no rollback) then StartTransaction will
throw an exception on the next call because there's still an active
transaction, right?
Right.
Quote

The code below is the template for a successful transaction? Was
that the assumption?
Yes. The recipe is:
Start a transaction.
Execute some SQL
If the SQL fails and data may have been changed roll back.
If the SQL fails and data could not have been changed (i.e. the SQL was
a SELECT) commit.
If the SQL succeeds commit.
You must make sure that you end every transaction that you start.
Quote

The reason I ask all this is that I did change my code BUT I got all
sorts of exceptions. However, I was using Delphi 6 ... tonight I'll
be testing the app with Delphi 2007 - same code. I wasn't sure if all the
exceptions plus loss of db connection was because of Delphi 6 ... so
to be safe, I am going with D2007.

It is not likely that the Delphi version will make a difference.
--
Bill Todd (TeamB)
 

Re: help - major problems after I changed CommitRetaining to Commit etc

Thanks Bill.
I had a feeling rollback cases weren't taken care of.
I assumed the code I inherited was based on good assumptions. I was wrong!
Joe
"Bill Todd [TeamB]" <XXXX@XXXXX.COM>writes
Quote
Joe Sansalone writes:

>What will the Commit within the finally do if the SQL updated
>something that another connection recently changed but didn't commit?
>In other words, if it can not Commit, should I have something in the
>code that does a rollback?

The commit in the finally block assumes that you are executing a SELECT
statement. If you are executing a statement that changes data then you
should call Rollback in the finally block.

>
>Should I put all of the below code within a try/except and then
>rollback in the case of the Commit throwing an exception?

Yes.

>
>If the Commit fails (and no rollback) then StartTransaction will
>throw an exception on the next call because there's still an active
>transaction, right?

Right.

>
>The code below is the template for a successful transaction? Was
>that the assumption?

Yes. The recipe is:

Start a transaction.
Execute some SQL
If the SQL fails and data may have been changed roll back.
If the SQL fails and data could not have been changed (i.e. the SQL was
a SELECT) commit.
If the SQL succeeds commit.

You must make sure that you end every transaction that you start.

>
>The reason I ask all this is that I did change my code BUT I got all
>sorts of exceptions. However, I was using Delphi 6 ... tonight I'll
>be testing the app with Delphi 2007 - same code. I wasn't sure if all the
>exceptions plus loss of db connection was because of Delphi 6 ... so
>to be safe, I am going with D2007.
>

It is not likely that the Delphi version will make a difference.

--
Bill Todd (TeamB)
 

Re: help - major problems after I changed CommitRetaining to Commit etc

By "inherited code" I meant the code that was left behind by a programmer in
my company.
The code you (Bill) and Craig gave me was obviously based on correct
assumptions
and a solid understanding of Interbase.
Thanks again!
"Joe Sansalone" <XXXX@XXXXX.COM>writes
Quote
Thanks Bill.

I had a feeling rollback cases weren't taken care of.
I assumed the code I inherited was based on good assumptions. I was
wrong!

Joe

"Bill Todd [TeamB]" <XXXX@XXXXX.COM>writes
news:48925754$XXXX@XXXXX.COM...
>Joe Sansalone writes:
>
>>What will the Commit within the finally do if the SQL updated
>>something that another connection recently changed but didn't commit?
>>In other words, if it can not Commit, should I have something in the
>>code that does a rollback?
>
>The commit in the finally block assumes that you are executing a SELECT
>statement. If you are executing a statement that changes data then you
>should call Rollback in the finally block.
>
>>
>>Should I put all of the below code within a try/except and then
>>rollback in the case of the Commit throwing an exception?
>
>Yes.
>
>>
>>If the Commit fails (and no rollback) then StartTransaction will
>>throw an exception on the next call because there's still an active
>>transaction, right?
>
>Right.
>
>>
>>The code below is the template for a successful transaction? Was
>>that the assumption?
>
>Yes. The recipe is:
>
>Start a transaction.
>Execute some SQL
>If the SQL fails and data may have been changed roll back.
>If the SQL fails and data could not have been changed (i.e. the SQL was
>a SELECT) commit.
>If the SQL succeeds commit.
>
>You must make sure that you end every transaction that you start.
>
>>
>>The reason I ask all this is that I did change my code BUT I got all
>>sorts of exceptions. However, I was using Delphi 6 ... tonight I'll
>>be testing the app with Delphi 2007 - same code. I wasn't sure if all the
>>exceptions plus loss of db connection was because of Delphi 6 ... so
>>to be safe, I am going with D2007.
>>
>
>It is not likely that the Delphi version will make a difference.
>
>--
>Bill Todd (TeamB)


 

Re: help - major problems after I changed CommitRetaining to Commit etc

Can we also use this ?
Start a transaction.
Execute some SQL
If the SQL fails and data may have been changed roll back.
If the SQL fails and data could not have been changed (i.e. the SQL was a
SELECT) ROLL BACK
If the SQL succeeds commit.
I ask because it makes simplify things. Especially in a data operation which
has both select sqls and insert/update/delete sqls.
Ex:
start a transaction
try
select sql
insert/update/delete sql
commit transaction
except
rollback transaction //if select fails roll back
raise
end
Select sql does not change database. So what is the difference between
commit and roll back for select sql ?
Regards
Ömür Ölmez
"Bill Todd [TeamB]" <XXXX@XXXXX.COM>writes
Quote
Joe Sansalone writes:

>What will the Commit within the finally do if the SQL updated
>something that another connection recently changed but didn't commit?
>In other words, if it can not Commit, should I have something in the
>code that does a rollback?

The commit in the finally block assumes that you are executing a SELECT
statement. If you are executing a statement that changes data then you
should call Rollback in the finally block.

>
>Should I put all of the below code within a try/except and then
>rollback in the case of the Commit throwing an exception?

Yes.

>
>If the Commit fails (and no rollback) then StartTransaction will
>throw an exception on the next call because there's still an active
>transaction, right?

Right.

>
>The code below is the template for a successful transaction? Was
>that the assumption?

Yes. The recipe is:

Start a transaction.
Execute some SQL
If the SQL fails and data may have been changed roll back.
If the SQL fails and data could not have been changed (i.e. the SQL was
a SELECT) commit.
If the SQL succeeds commit.

You must make sure that you end every transaction that you start.

>
>The reason I ask all this is that I did change my code BUT I got all
>sorts of exceptions. However, I was using Delphi 6 ... tonight I'll
>be testing the app with Delphi 2007 - same code. I wasn't sure if all the
>exceptions plus loss of db connection was because of Delphi 6 ... so
>to be safe, I am going with D2007.
>

It is not likely that the Delphi version will make a difference.

--
Bill Todd (TeamB)
 

Re: help - major problems after I changed CommitRetaining to Commit etc

Ömür Ölmez writes:
Quote
Can we also use this ?

Start a transaction.
Execute some SQL
If the SQL fails and data may have been changed roll back.
If the SQL fails and data could not have been changed (i.e. the SQL was a
SELECT) ROLL BACK
If the SQL succeeds commit.

I ask because it makes simplify things. Especially in a data operation which
has both select sqls and insert/update/delete sqls.
Ex:

start a transaction
try
select sql
insert/update/delete sql
commit transaction
except
rollback transaction //if select fails roll back
raise
end

Select sql does not change database. So what is the difference between
commit and roll back for select sql ?
Rollbacks are actually harder to do on the engine than a commit. Actually
whenever possible the engine actually determines if it is safe to turn a
rollback into a commit before actually doing a rollback. Also note that if you
only do one insert/update/delete statement and it fails, you can commit the
transaction because no changes were made to the database, you only need to
rollback when you are doing 2 or more and one of them succeeds but another one
fails.
--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
 

Re: help - major problems after I changed CommitRetaining to Commit etc

Ömür Ölmez writes:
Quote
If the SQL fails and data may have been changed roll back.
If a single statement fails (for example, an UPDATE fails because the
new values violate a constraint), then no data has been changed. You
should commit, not rollback.
If one statement succeeds and a second statement fails, and you need
both statements to succeed or fail as a group, then you should rollback.
Quote
If the SQL fails and data could not have been changed (i.e. the SQL
was a SELECT) ROLL BACK
No. In this case, you should commit.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Everything You Need to Know About InterBase Character Sets:
blogs.teamb.com/craigstuntz/articles/403.aspx
 

Re: help - major problems after I changed CommitRetaining to Commit etc

Should the Commit somehow fail, the next time StartTransaction is
called it could throw an exception, right?
Should we check if InTransaction before calling StartTransaction?
(especially since the StartTransaction is not within the try/except)
"Jeff Overcash (TeamB)" <XXXX@XXXXX.COM>writes
Quote
Ömür Ölmez writes:
>Can we also use this ?
>
>Start a transaction.
>Execute some SQL
>If the SQL fails and data may have been changed roll back.
>If the SQL fails and data could not have been changed (i.e. the SQL was a
>SELECT) ROLL BACK
>If the SQL succeeds commit.
>
>I ask because it makes simplify things. Especially in a data operation
>which has both select sqls and insert/update/delete sqls.
>Ex:
>
>start a transaction
>try
>select sql
>insert/update/delete sql
>commit transaction
>except
>rollback transaction //if select fails roll back
>raise
>end
>
>Select sql does not change database. So what is the difference between
>commit and roll back for select sql ?

Rollbacks are actually harder to do on the engine than a commit. Actually
whenever possible the engine actually determines if it is safe to turn a
rollback into a commit before actually doing a rollback. Also note that
if you only do one insert/update/delete statement and it fails, you can
commit the transaction because no changes were made to the database, you
only need to rollback when you are doing 2 or more and one of them
succeeds but another one fails.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
 

Re: help - major problems after I changed CommitRetaining to Commit etc

So Commit really means commit all successful changes of this transaction,
even though
some SQL statements failed.
"Craig Stuntz [TeamB]" <XXXX@XXXXX.COM [a.k.a. acm.org]>wrote
in message news:4892fbf2$XXXX@XXXXX.COM...
Quote
Ömür Ölmez writes:

>If the SQL fails and data may have been changed roll back.

If a single statement fails (for example, an UPDATE fails because the
new values violate a constraint), then no data has been changed. You
should commit, not rollback.

If one statement succeeds and a second statement fails, and you need
both statements to succeed or fail as a group, then you should rollback.

>If the SQL fails and data could not have been changed (i.e. the SQL
>was a SELECT) ROLL BACK

No. In this case, you should commit.

--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Everything You Need to Know About InterBase Character Sets:
blogs.teamb.com/craigstuntz/articles/403.aspx
 

Re: help - major problems after I changed CommitRetaining to Commit etc

Joe Sansalone writes:
Quote
So Commit really means commit all successful changes of this
transaction, even though some SQL statements failed.
Every statement that you run has an implicit savepoint. When a
statement fails, changes made by the partial execution of the statement
are rolled back to the implicit savepoint which was started when the
statement began. Hence, when a statement fails with an error, you can
commit, since there are no changes saved after the error has been
raised.
The implicit savepoint works just like the explicit savepoints that
you can write manually.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Please read and follow Borland's rules for the user of their
server: support.borland.com/entry.jspa
 

Re: help - major problems after I changed CommitRetaining to Commit etc

If there are 3 different SQL updates within a transaction, and the first
succeeds,
the second fails and the 3rd succeeds ... will a Commit after the 3rd
statement change
the data to reflect SQL statements 1 and 3?
"Craig Stuntz [TeamB]" <XXXX@XXXXX.COM [a.k.a. acm.org]>wrote
in message news:489320f6$XXXX@XXXXX.COM...
Quote
Joe Sansalone writes:

>So Commit really means commit all successful changes of this
>transaction, even though some SQL statements failed.

Every statement that you run has an implicit savepoint. When a
statement fails, changes made by the partial execution of the statement
are rolled back to the implicit savepoint which was started when the
statement began. Hence, when a statement fails with an error, you can
commit, since there are no changes saved after the error has been
raised.

The implicit savepoint works just like the explicit savepoints that
you can write manually.

--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Please read and follow Borland's rules for the user of their
server: support.borland.com/entry.jspa