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

[Marcos Douglas]

On Thu, Dec 1, 2011 at 11:26 AM, Ludo Brands <ludo.brands at free.fr> wrote:
>> 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.

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

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

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

> 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