Hi,
Why TSQLTransaction.Commit calls CloseDataSets?
Always that I call StartTransaction and Commit/Rollback and I have to reopen my DataSets?!
Marcos Douglas
Mailing lists / fpc-pascal / 2011-11
SQLdb :: Commit, Rollback, etc.
7 messages · 3 people · –
- Marcos Douglas 4
- Ludo Brands 2
- Sven Barth 1
Reply by email Subscribe to this list Your subscription
Read-only archive. Use j and k to move between messages.
Marcos Douglas wrote 5 lines
Hi,
Why TSQLTransaction.Commit calls CloseDataSets?
Always that I call StartTransaction and Commit/Rollback and I have to
reopen my DataSets?!
Use CommitRetaining and RollbackRetaining. These don't call CloseDataSets.
Regards,
Sven
Sven Barth wrote 9 lines
Am 30.11.2011 15:47, schrieb Marcos Douglas:
>
> Hi,
>
> Why TSQLTransaction.Commit calls CloseDataSets?
> Always that I call StartTransaction and Commit/Rollback and I have to
> reopen my DataSets?!
Use CommitRetaining and RollbackRetaining. These don't call CloseDataSets.
Right, but I will have keep the transaction in the server all the time?! I work with ADO in Delphi 7. I keep the DataSet open on the form. If I need to execute a process in many tables, e.g., I use transaction. So, just call BeginTran and Commit/Rollback... and all DataSets keep open!
I can't keep a transaction (server locked) all the time.
Marcos Douglas
I can't keep a transaction (server locked) all the time.
What database are you using that locks up the server when a transaction is started?
Oracle for example starts an transaction as soon as you send an executable SQL Statement. Opening a connection starts a transaction on the server. Therefor it is important to match this behavior on the client side and force the use of a TSQLTransaction. Using TADOConnection doesn't change this. ADO is just hiding it from you.
As long as you use a transaction level that is lower or equal to Read committed, a transaction is almost at no cost to a database server (other than access that doesn't support transactions or mysql that uses a different backend to support transactions).
In a client server application, datasets are local copies of (a part of) the database and should be short lived for tables that are updated regularly. Re-opening (re-fetching) data from such tables after a commit or rollback is almost a must to avoid the client working with old/wrong data. If you have datasets linked to "low or no update" tables, attach them to a different connection.
Ludo
Ludo Brands wrote 6 lines
> I can't keep a transaction (server locked) all the time.
>
What database are you using that locks up the server when a transaction is
started?
MS SQLServer 2008. Lock the registers, not the server... sorry.
Show quoted text 5 lines
Oracle for example starts an transaction as soon as you send an executable
SQL Statement. Opening a connection starts a transaction on the server.
Therefor it is important to match this behavior on the client side and force
the use of a TSQLTransaction. Using TADOConnection doesn't change this. ADO
is just hiding it from you.
Ok, I can accept the TADOConnection can hiding a lot of things from me, but I think it do open many transactions all the time. Using SQLServer Profiler, I can see all SQL that are executing on the server. So, I do not see BEGIN TRAN all the time, just when I call BeginTrans.
As long as you use a transaction level that is lower or equal to Read committed, a transaction is almost at no cost to a database server (other than access that doesn't support transactions or mysql that uses a different backend to support transactions).
I'm a tester of the new connector TMSSQLServer to SQLdb, by LacaK: http://lazarus.freepascal.org/index.php/topic,15135.msg80922.html#msg80922
If the component Open a Query I see BEGIN TRAN and... the transaction isn't closed!
Well, maybe there is a bug in the new class by I see the SQLdb implementation and that class follow the design.
What I know is: "if a transaction is open, it's should be closed as fast you can"... but if you right, the phrase should be " "if a transaction is open, it's should be closed as fast you can, IF you update something". Is that correct to you?
Show quoted text 6 lines
In a client server application, datasets are local copies of (a part of) the
database and should be short lived for tables that are updated regularly.
Re-opening (re-fetching) data from such tables after a commit or rollback is
almost a must to avoid the client working with old/wrong data. If you have
datasets linked to "low or no update" tables, attach them to a different
connection.
Ok, but I shoud be the controller of my DataSets, shouldn't it? If, eg., I use RAD, how can I controller the DataSets that are using for the users in DBGrid or something, if all DataSets will be closed if he update something (that need a transaction)?
Marcos Douglas
MS SQLServer 2008. Lock the registers, not the server... sorry.
I guess you mean rows, pages or tables.
Show quoted text 6 lines
Ok, I can accept the TADOConnection can hiding a lot of
things from me, but I think it do open many transactions all
the time. Using SQLServer Profiler, I can see all SQL that
are executing on the server. So, I do not see BEGIN TRAN all
the time, just when I call BeginTrans.
That is because SQL server defaults to autocommit. A BEGIN TRANSACTION starts an explicit transaction and disables the autocommit temporarely.
I'm a tester of the new connector TMSSQLServer to SQLdb, by LacaK: http://lazarus.freepascal.org/index.php/topic,15135.msg80922.h
tml#msg80922
You can set the connection parameter AutoCommit=true when you want to simulate ADO behavior. Unfortunately you have to set the parameter back to false when starting an explicit transaction. Perhaps there should be an easier way of doing this.
Show quoted text 9 lines
If the component Open a Query I see BEGIN TRAN and... the
transaction isn't closed! Well, maybe there is a bug in the
new class by I see the SQLdb implementation and that class
follow the design.
What I know is: "if a transaction is open, it's should be closed
as fast you can"... but if you right, the phrase should be "
"if a transaction is open, it's should be closed as fast you can,
IF you update something". Is that correct to you?
Depends on the isolation level. If you use the default Read committed level, and you do only some selects, there is no reason to close a transaction quickly. Nothing is locked as long as you don't update or insert anything. So, yes, your last statement is correct for isolation levels that don't use repeatable reads.
Ok, but I shoud be the controller of my DataSets, shouldn't it?
Yes.
If, eg., I use RAD, how can I controller the DataSets that are using for the users in DBGrid or something, if all DataSets will be closed if he update something (that need a transaction)?
Use several connections with their own TSQLTransaction. Link all your datasets that are not under transaction control to a connection with AutoCommit=true. Use CommitRetaining for those datasets, part of a transaction, that you want to keep opened.
DBGrid is updating the local dataset and only when applying updates, the data is sent to the server. Are you updating the server for every line updated/inserted? I don't think so. Use CommitRetaining if you wan't to store your changes but continue working with your current context/data (that is what the "retaining" stands for) and use Commit when you are done with the data.
I agree that, because of the default autocommit, ADO matches better the SQLServer behavior. Surprising, isn't it ;) But Oracle, firebird, interbase, ... all start an implicit transaction with the first executable statement. Implicit transaction is the default in the SQL92/99 standard. I prefer sql-db matches the standard as close as possible.
Ludo
Ludo Brands wrote 23 lines
> MS SQLServer 2008. Lock the registers, not the server... sorry.
>
I guess you mean rows, pages or tables.
> Ok, I can accept the TADOConnection can hiding a lot of
> things from me, but I think it do open many transactions all
> the time. Using SQLServer Profiler, I can see all SQL that
> are executing on the server. So, I do not see BEGIN TRAN all
> the time, just when I call BeginTrans.
>
That is because SQL server defaults to autocommit. A BEGIN TRANSACTION
starts an explicit transaction and disables the autocommit temporarely.
> I'm a tester of the new connector TMSSQLServer to SQLdb, by
> LacaK:
> http://lazarus.freepascal.org/index.php/topic,15135.msg80922.h
tml#msg80922
You can set the connection parameter AutoCommit=true when you want to
simulate ADO behavior. Unfortunately you have to set the parameter back to
false when starting an explicit transaction. Perhaps there should be an
easier way of doing this.
No, because I need to use REAL transactions.
As I said in forum:
If we use aParams (StartDBTransaction) we can do this very easy. Again, see TIBConnection...
I can't modify Connection.Params (a global variable) many times. This is not thread-safe. IMHO, should be for each Transaction.
Show quoted text 15 lines
> If the component Open a Query I see BEGIN TRAN and... the
> transaction isn't closed! Well, maybe there is a bug in the
> new class by I see the SQLdb implementation and that class
> follow the design.
>
> What I know is: "if a transaction is open, it's should be closed
> as fast you can"... but if you right, the phrase should be "
> "if a transaction is open, it's should be closed as fast you can,
> IF you update something". Is that correct to you?
Depends on the isolation level. If you use the default Read committed level,
and you do only some selects, there is no reason to close a transaction
quickly. Nothing is locked as long as you don't update or insert anything.
So, yes, your last statement is correct for isolation levels that don't use
repeatable reads.
In these cases we can use AUTOCOMMIT (a new Transaction object) just to read.
Show quoted text 12 lines
> Ok, but I shoud be the controller of my DataSets, shouldn't it?
Yes.
> If, eg., I use RAD, how can I controller the DataSets that are
> using for the users in DBGrid or something, if all DataSets will
> be closed if he update something (that need a transaction)?
Use several connections with their own TSQLTransaction. Link all your
datasets that are not under transaction control to a connection with
AutoCommit=true. Use CommitRetaining for those datasets, part of a
transaction, that you want to keep opened.
You propose to use more than one connection? I can't do that in some apps...
Show quoted text 6 lines
DBGrid is updating the local dataset and only when applying updates, the
data is sent to the server. Are you updating the server for every line
updated/inserted? I don't think so. Use CommitRetaining if you wan't to
store your changes but continue working with your current context/data (that
is what the "retaining" stands for) and use Commit when you are done with
the data.
I understand. My older doubt was about start a transaction (BEGIN TRAN command in the server) for a long time. But you said this is normal, so..
Show quoted text 5 lines
I agree that, because of the default autocommit, ADO matches better the
SQLServer behavior. Surprising, isn't it ;) But Oracle, firebird, interbase,
... all start an implicit transaction with the first executable statement.
Implicit transaction is the default in the SQL92/99 standard. I prefer
sql-db matches the standard as close as possible.
Ok, I can live with the way that works SQLdb, no problem! ;-) I only want to learn to make my apps using Lazarus and SQLdb and the new Connector TMSSQLConnection (BTW, good work).
Marcos Douglas