several select queries in one transaction

[humanista (mruczus)]

I need to read data from several tables in one transaction. Each select
reads data from one table. What I need is for data not to be changed in all
the tables by another user throught all separate select's as data in one
table depend on data in another table. If I write data to the tables I know
I can make a transaction to ensure there is no change in the tables made by
another user, but if I just read data from tables in one transaction can I
also be sure the tables data are the same in transaction life?

[Bill Todd [TeamB]]

If the database you are using supports snapshot transaction isolation
you can get a stable view of the data for the life of the transaction.

Serializable isolation will give you a stable view of the data although
not necessarily a consistent view of the data.

Like most database questions the answer depends on what database you
are using.

[humanista (mruczus)]

As before for now I am using interbase open source version.

[Bill Todd [TeamB]]

Post by humanista (mruczus)
As before for now I am using interbase open source version.

Then using snapshot transaction isolation will give you a stable
snapshow of the database as it was at the instant the transaction
started.

[humanista (mruczus)]

Is it safe always to use READCOMMITTED with clientdataset? What I have in
mind is always to apply updates in afterpost so just one record is posted
and the query deals with only one table.

[Bill Todd [TeamB]]

Post by humanista (mruczus)
Is it safe always to use READCOMMITTED with clientdataset?

I am not sure what you mean by "safe" in this context. Transaction
isolation level controls when your transaction sees changes made by
other transactions. The isolation level has no effect on updates,
inserts or deletes made by your transaction

[humanista (mruczus)]

For example I have a master-detail. While reading data to the clientdataset
there is a select transaction. With READCOMMITTED after reading master,
another transaction (at a different client) may change the data (for both
master and detail) and my first transaction is going to read the changed
data but only for the detail table (because the master has already been read
in the same transaction). So using READCOMMITTED even for so simple
master-detail structure I can easily get an inconsistent database. In that
case I wonder if I should always use snapshot isolation level.

[Bill Todd [TeamB]]

In that case I wonder if I should always use snapshot isolation level.

Yes, you should. That is exactly the situation that snapshot is
designed for. The purpose of snapshot isolation is to give your
transaction a logically consistent view of the entire database at an
instant in time.

[humanista (mruczus)]

Is REPEATABLEREAD equal to interbase snapshot?
I have tried to set it in tSQLconnection.params but keep getting exceptions
just as there is a concurrent reading at another client with master-detail
clientdataset, so it is not possible to post any change. No wonder every one
is using READCOMMITTED.

Perhaps I am missing something. Perhaps one can get an inconsistent view of
data in the described case (master-detail grids connected via
clientdatasets) but cannot post them and the incorrectly displayed data for
master-detail grids are not so harmful?

[Bill Todd [TeamB]]

Are you using Delphi 2007?

[humanista (mruczus)]

I am using Delphi 7.

[Bill Todd [TeamB]]

Post by humanista (mruczus)
I am using Delphi 7.

AFAIK, the only way to use snapshot isolation when you open a CDS is to
start a transaction using snapshot isolation, open the CDS then commit
the transaction. There is no way to specify a default isolation level
that I am aware of.

[Craig Stuntz [TeamB]]

You don't have to use SNAPSHOT to open queries in the same transaction.

You can hook up one CDS to a DSP, then pass the data packets for the
other CDSs as optional params as part of the first DSPs data packet.
This works best for read-only queries as the resolution is no longer
automatic.

[Bill Todd [TeamB]]

Ignore my previous reply. In D7 you can set the default isolation in
the Params property editor after you have set the other SQLConnection
properties.