| Subject | RE: [firebird-support] Who's guilty?--- |
|---|---|
| Author | Martin Dew |
| Post date | 2003-12-03T09:08:47Z |
Once again, thanks to you all for offering advice..
Our bde settings are set to COMMIT RETAIN=FALSE
The driver flags where null inside the
BDE/Configuration/Drivers/Native/Interbase - should I use 4096 here with
COMMIT REATIN=FALSE or TRUE ??
In our app we call a Tdatabase.StartTransation
Then call Tquery.ApplyUpdates and Tquery.CommitUpdates on any dataset
using CachedUpdates=True and then call Tdatabase.Commit with an
exception handler calling a TDatabase.Rollback if an exception is
raised. Not all of our queries inside the app use Cached Updates. I
unfortunately inherited this application from another developer, so am
unaware of exactly why he chose to do it this way.
TIA
Martin
-----Original Message-----
From: Johannes Pretorius [mailto:johannes@...]
Sent: 03 December 2003 05:40
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Who's guilty?---
Good day
\=-\=-\=-\-=-
If I may ask why don't you use the
TDatabase.ApplyUpdates([query1,query2,...]);
function ?
This will make sure that ALL cahced updates are forced to the database
and does it all in a transaction, if anything fails, ALL rollsback and
returns to you. You can wait for 2 seconds and try again for instance
?,.,.
In short using the TDatabase.ApplyUpdates will insure that your
Transactions is CLOSED correctly and if you use the DRIVER_FLAGS 4608
then doing a TDatabase.StartTransaction and then immediatly a
TDatabase.Commit before a re-read of a Query will force all previous
transactions out and open you up for the newly added entries.
Here is some info on the DRIVER FLAGS
//========== SNIP START ==============
Roles
-----------
InterBase allows users to log in by specifying a role.
ROLE NAME is a new BDE configuration parameter that allows BDE clients
to pass a role name. ROLE NAME can also be passed as an optional
parameter when using a TDatabase component (Delphi and C++Builder).
This information supersedes that in the InterBase 5.5 Operations Guide,
which indicates that the BDE has no property for specifying a role name.
Transaction Isolation Levels & InterBase
----------------------------------------------
By adding 4096 to the setting of DRIVER FLAGS in the Borland Database
Engine (BDE) configuration, you can specify that the InterBase SQL Links
driver should use soft commits. Soft commits are a feature of InterBase
that let the driver retain the cursor when committing changes. Soft
commits improve performance on updates to large sets of data. When not
used, the BDE must re-fetch all the records, even for a single record
change. With soft commit the cursor is retained, and a re-fetch is not
needed. The soft commit property corresponds to the COMMIT RETAINING
transaction option in the InterBase documentation.
DRIVER FLAGS Isolation level Commit type
------------ --------------- ------------
0 Read committed hard commit
512 Repeatable read hard commit
4096 Read committed soft commit
4608 Repeatable read soft commit
The 4096 driver flag bit only affects implicit transaction behavior. Use
the COMMIT RETAINING property to control the default commit behavior of
explicit transactions.
Explicit transactions
---------------------------
The new BDE configuration parameter COMMIT RETAIN allows explicit
transactions to use soft commit. The new database property
dbCOMMITRETAIN also does this. Only explicit transactions are affected.
The existing driver flags are still valid for explicit transactions.
COMMIT RETAINING = TRUE calls isc_commit_retaining()
COMMIT RETAINING = FALSE calls isc_commit_transaction()
Transaction WAIT
----------------------
Currently, the InterBase SQL Links driver uses NOWAIT when resolving
lock conflicts and returns an error immediately if there is a resource
conflict. Setting the WAIT ON LOCK configuration parameter allows SQL
Links to use WAIT on InterBase transactions. This can also be done by
setting the r/w database property dbWAITONLOCK to TRUE.
//============== SNIP END ================
Otherwise is the BDE SQLPASSTHRU MODE = SHARED AUTOCOMMIT.
I had problems that if this was set to SHARED NOAUTOCOMMIT that if a app
dies that the record is logged for a while, but this will create
Deadlock problems anyways.
I hope this helps in anyway
Johannes
At 11:23 02/12/2003 +0000, you wrote:
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.545 / Virus Database: 339 - Release Date: 27/11/2003
[Non-text portions of this message have been removed]
To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/
Our bde settings are set to COMMIT RETAIN=FALSE
The driver flags where null inside the
BDE/Configuration/Drivers/Native/Interbase - should I use 4096 here with
COMMIT REATIN=FALSE or TRUE ??
In our app we call a Tdatabase.StartTransation
Then call Tquery.ApplyUpdates and Tquery.CommitUpdates on any dataset
using CachedUpdates=True and then call Tdatabase.Commit with an
exception handler calling a TDatabase.Rollback if an exception is
raised. Not all of our queries inside the app use Cached Updates. I
unfortunately inherited this application from another developer, so am
unaware of exactly why he chose to do it this way.
TIA
Martin
-----Original Message-----
From: Johannes Pretorius [mailto:johannes@...]
Sent: 03 December 2003 05:40
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Who's guilty?---
Good day
\=-\=-\=-\-=-
If I may ask why don't you use the
TDatabase.ApplyUpdates([query1,query2,...]);
function ?
This will make sure that ALL cahced updates are forced to the database
and does it all in a transaction, if anything fails, ALL rollsback and
returns to you. You can wait for 2 seconds and try again for instance
?,.,.
In short using the TDatabase.ApplyUpdates will insure that your
Transactions is CLOSED correctly and if you use the DRIVER_FLAGS 4608
then doing a TDatabase.StartTransaction and then immediatly a
TDatabase.Commit before a re-read of a Query will force all previous
transactions out and open you up for the newly added entries.
Here is some info on the DRIVER FLAGS
//========== SNIP START ==============
Roles
-----------
InterBase allows users to log in by specifying a role.
ROLE NAME is a new BDE configuration parameter that allows BDE clients
to pass a role name. ROLE NAME can also be passed as an optional
parameter when using a TDatabase component (Delphi and C++Builder).
This information supersedes that in the InterBase 5.5 Operations Guide,
which indicates that the BDE has no property for specifying a role name.
Transaction Isolation Levels & InterBase
----------------------------------------------
By adding 4096 to the setting of DRIVER FLAGS in the Borland Database
Engine (BDE) configuration, you can specify that the InterBase SQL Links
driver should use soft commits. Soft commits are a feature of InterBase
that let the driver retain the cursor when committing changes. Soft
commits improve performance on updates to large sets of data. When not
used, the BDE must re-fetch all the records, even for a single record
change. With soft commit the cursor is retained, and a re-fetch is not
needed. The soft commit property corresponds to the COMMIT RETAINING
transaction option in the InterBase documentation.
DRIVER FLAGS Isolation level Commit type
------------ --------------- ------------
0 Read committed hard commit
512 Repeatable read hard commit
4096 Read committed soft commit
4608 Repeatable read soft commit
The 4096 driver flag bit only affects implicit transaction behavior. Use
the COMMIT RETAINING property to control the default commit behavior of
explicit transactions.
Explicit transactions
---------------------------
The new BDE configuration parameter COMMIT RETAIN allows explicit
transactions to use soft commit. The new database property
dbCOMMITRETAIN also does this. Only explicit transactions are affected.
The existing driver flags are still valid for explicit transactions.
COMMIT RETAINING = TRUE calls isc_commit_retaining()
COMMIT RETAINING = FALSE calls isc_commit_transaction()
Transaction WAIT
----------------------
Currently, the InterBase SQL Links driver uses NOWAIT when resolving
lock conflicts and returns an error immediately if there is a resource
conflict. Setting the WAIT ON LOCK configuration parameter allows SQL
Links to use WAIT on InterBase transactions. This can also be done by
setting the r/w database property dbWAITONLOCK to TRUE.
//============== SNIP END ================
Otherwise is the BDE SQLPASSTHRU MODE = SHARED AUTOCOMMIT.
I had problems that if this was set to SHARED NOAUTOCOMMIT that if a app
dies that the record is logged for a while, but this will create
Deadlock problems anyways.
I hope this helps in anyway
Johannes
At 11:23 02/12/2003 +0000, you wrote:
>The app uses the BDE to connect to the database and do its work.----------
>
>We call TDabase.Commit inside a try except block, the exception handles
>calling Tdatabase.Rollback.
>
>I am not too sure what you mean by autocommit and hard commits.
>
>The databases have forced writes enabled, and we seem to handle
>ApplyUpdates, CommitUpdates in the Tquery objects, then call a
>Tdatabase.Commit.
>
>Is there something else we should be doing, if so please please please
>tell me as my customers are also rather annoyed with having to be taken
>down (they are 24/7 medical operations) to backup and restore....
>
>Thanks for any help..
>
>-----Original Message-----
>From: Helen Borrie [mailto:helebor@...]
>Sent: 02 December 2003 11:16
>To: firebird-support@yahoogroups.com
>Subject: RE: [firebird-support] Who's guilty?
>
>At 09:36 AM 2/12/2003 +0000, you wrote:
> > I have similar issues with some of my sites using IB5.6 and
> >IB6.0.2.0, I cannot replicate this in my test rig, and I have checked
> >my commiting code numerous times to make sure I am always calling
> >either a .commit or a .rollback ,
>
>I suppose you do understand that, if commit fails, your application
>will have to handle the failure...
>
>..and that Autocommit prevents the record versions from the involved
>transactions from being made "uninteresting"...
>
> >but I get I huge gaps in transaction statistics, I cannot do a sweep
> >as
>
> >the gap is not between the OIT and OAT (see example) so my only
> >option is a backup and restore. I have tried IB_AFFINITY to set it to
> >one processor in the dual processor machine, I have also had the IT
> >team turn off hyperthreading in the bios, all to no avail.
> >
> >OT 566738
> >OAT 566739
> >NT 940551
> >OS 564916
> >
> >After checking this weekend the OT,OAT and OS have not moved from
> >above, but the NT is now more than a gap of 1 million to these other
>stats..
>
>1 million Autocommits and no hard commits?
>
>/heLen
>
>
>
>
>To unsubscribe from this group, send an email to:
>firebird-support-unsubscribe@yahoogroups.com
>
>
>
>Your use of Yahoo! Groups is subject to
>http://docs.yahoo.com/info/terms/
>
>
>
>
>
>
>To unsubscribe from this group, send an email to:
>firebird-support-unsubscribe@yahoogroups.com
>
>
>
>Your use of Yahoo! Groups is subject to
>http://docs.yahoo.com/info/terms/
>
>
>
>---
>Incoming mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.545 / Virus Database: 339 - Release Date: 27/11/2003
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.545 / Virus Database: 339 - Release Date: 27/11/2003
[Non-text portions of this message have been removed]
To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/