[fpc-pascal] SQLdb :: Commit, Rollback, etc.

[Ludo Brands]

> 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.

> 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