Transaction management

[JLouro]

What is best?

To control the transactions in code or to leave the DBx to handle it for me?



Jlouro

[Bill Todd [TeamB]]

Post by JLouro
To control the transactions in code or to leave the DBx to handle it for me?

Not all databases will handle transactions automatically. If they do
the most you get is each SQL statment wrapped in a separate
transaction. Some database components will try to handle transactions
for you. What they do and how they do it depends on the component set
you are using. In the case of dbExpress each call to
TClientDataSet.ApplyUpdates is wrapped in a transaction.

[JLouro]

Ok
The choice DB in this case is BalckFish

Should I take care of the transaction in my code ?

Post by Bill Todd [TeamB]

Post by JLouro
To control the transactions in code or to leave the DBx to handle it for me?

Not all databases will handle transactions automatically. If they do
the most you get is each SQL statment wrapped in a separate
transaction. Some database components will try to handle transactions
for you. What they do and how they do it depends on the component set
you are using. In the case of dbExpress each call to
TClientDataSet.ApplyUpdates is wrapped in a transaction.
--
Bill Todd (TeamB)

[Bill Todd [TeamB]]

Post by JLouro
The choice DB in this case is BalckFish
Should I take care of the transaction in my code ?

If you are using TClientDataSet and TDataSetProvider I would let them
handle the transaction unless there is some reason for you to override
their behavior.

[Bill Todd [TeamB]]

In addition, I should have said that if you are doing updates in code
using SQL you should explicitly handle transactions in your code.

[JLouro]

I don't understand!?

Post by Bill Todd [TeamB]
In addition, I should have said that if you are doing updates in code
using SQL you should explicitly handle transactions in your code.
--
Bill Todd (TeamB)

[Bill Todd [TeamB]]

In a database application that uses dbExpress there are two ways to
change data in the database. One way is to use a ClientDataSet. This is
most often used to allow users to view and edit data through the user
interface. When you call TClientDataSet.ApplyUpdates the
TDataSetProvider automatically starts a transaction, updates the
database then either commits or rolls back the transaction.

The other way to edit data is using SQL directly by using a TSQLDataSet
component and setting the CommandText property to an SQL INSERT, UPDATE
or DELETE statement then executing that statement by calling
TSQLDataSet.ExecSQL. When you do this you must start and commit or roll
back the transaction in your code.

[JLouro]

OK
now its cristal clear
thanks

Post by Bill Todd [TeamB]
In a database application that uses dbExpress there are two ways to
change data in the database. One way is to use a ClientDataSet. This is
most often used to allow users to view and edit data through the user
interface. When you call TClientDataSet.ApplyUpdates the
TDataSetProvider automatically starts a transaction, updates the
database then either commits or rolls back the transaction.
The other way to edit data is using SQL directly by using a TSQLDataSet
component and setting the CommandText property to an SQL INSERT, UPDATE
or DELETE statement then executing that statement by calling
TSQLDataSet.ExecSQL. When you do this you must start and commit or roll
back the transaction in your code.
--
Bill Todd (TeamB)

[Erick Sasse]

Post by Bill Todd [TeamB]
The other way to edit data is using SQL directly by using a
TSQLDataSet component and setting the CommandText property to an SQL
INSERT, UPDATE or DELETE statement then executing that statement by
calling TSQLDataSet.ExecSQL. When you do this you must start and
commit or roll back the transaction in your code.

I assume the same is true for SQLConnection.ExecuteDirect method, right?
I have some updates and deletes using this method but I wasn't starting
and commiting transactions.

[Bill Todd [TeamB]]

Post by Erick Sasse
I assume the same is true for SQLConnection.ExecuteDirect method,
right? I have some updates and deletes using this method but I
wasn't starting and commiting transactions.

Some databases, SQL Server for example, will start and commit a
transaction for you. Personally, I think it is always a good idea to
include specific transaction control statements so it is clear where
transactions start and end and so the code will be portable to a
database that does not provide automatic transaction control.

[Erick Sasse]

Post by Bill Todd [TeamB]
Some databases, SQL Server for example, will start and commit a
transaction for you. Personally, I think it is always a good idea to
include specific transaction control statements so it is clear where
transactions start and end and so the code will be portable to a
database that does not provide automatic transaction control.

I'm going to add the code to handle transaction. Do you know if
Firebird has this automatic control? If some databases don't have this
automatic control, they just start a transaction and leave it open?

[Cesar Romero]

Post by Erick Sasse
I'm going to add the code to handle transaction. Do you know if
Firebird has this automatic control? If some databases don't have this
automatic control, they just start a transaction and leave it open?

I think it uses the Connection Default Transaction, that stays alive
until connection is open.
If for some reason the application frozen and you kill with Task
manager you can lose the work.

Someone report this problem few days ago in Trollnet NG, in that case
the data will be on limbo and will be cleaned when you run
backup/restore with clear limbo option.

[Erick Sasse]

Post by Cesar Romero
I think it uses the Connection Default Transaction, that stays alive
until connection is open.

This would be better than creating a new transaction for every command.

But then the changes would not be visible to any other transactions
until this "global transaction" is commited, right?

Maybe with new features in Firebird 2.1 we can monitor the transactions
better and understand this.

[Cesar Romero]

Post by Erick Sasse

Post by Cesar Romero
I think it uses the Connection Default Transaction, that stays alive
until connection is open.

This would be better than creating a new transaction for every
command.

I do prefer knows when the transactions begin/ends.

Post by Erick Sasse
But then the changes would not be visible to any other transactions
until this "global transaction" is commited, right?

That depends on the params you are using in the connection,
eg: only using READ COMMITED will hide from others transactions.

Post by Erick Sasse
Maybe with new features in Firebird 2.1 we can monitor the
transactions better and understand this.

That is a good idea, maybe you can post that on you blog :D

[Bill Todd [TeamB]]

Post by Cesar Romero
Someone report this problem few days ago in Trollnet NG, in that case
the data will be on limbo and will be cleaned when you run
backup/restore with clear limbo option.

Are you sure about that? The transaction should not be in limbo. Either
it will be active or, if the server detects that the connection is
gone, the server will rollback the transaction.

[Cesar Romero]

Post by Bill Todd [TeamB]

Post by Cesar Romero
Someone report this problem few days ago in Trollnet NG, in that
case the data will be on limbo and will be cleaned when you run
backup/restore with clear limbo option.

Are you sure about that? The transaction should not be in limbo.

Im sure that something like that really happen, maybe for another
reason.

Post by Bill Todd [TeamB]
Either it will be active or, if the server detects that the
connection is gone, the server will rollback the transaction.

That happen when the server cant identify that the connection is gone,
and this I saw happen few times too, also in another ng from others
users. I dont know if this is something that still happen in FB 2.1.

For my personal taste, I really prefer to keep the control of all
transactions in my code.

[Bill Todd [TeamB]]

Post by Erick Sasse
I'm going to add the code to handle transaction. Do you know if
Firebird has this automatic control? If some databases don't have this
automatic control, they just start a transaction and leave it open?

Firebird and InterBase do not automatically start a transaction. I do
not know if the TSQLConnection component will automatically start a
transaction or not when you use ExecuteDirect. I have never tried it.