Hi,
Am Dienstag, den 06.12.2005, 14:15 +0100 schrieb Joost van der Sluis:
> > By the way, I write the data using the following code in the event "after
> > post":
> > begin
> > SQLQuery1.ApplyUpdates;
> > SQLTransaction1.Commit; //In this moment, after commit, the query is
> > closed !!!?
> > SQLQuery1.active:=true; //So, I have open it !!!? This does to
> > return for the first record !!! How to keep the current register?
> > end;
>
> You're the first ont noticing this, and I'm really suprised that no-one
> else did that before.
>
> For me this is one (the) main reason why it isn't useable in real-life
> applications (yet)
>
This is called "Transaction Isolation" and one of the four corner stones
of relational databases (atomicy, consistency, isolation, durability).
If you Commit a Transaction, it basically means you are done with it. I
think for the system to be closing the queries is the obvious thing to
do. The queries are not valid anymore either way.
You can think of it like with web forms. You have a form which shows
some data in your browser (the process of loading the form was starting
the transaction). Now, while the user is reading that, tons of people
can go change data on the server. The form does not (and should not!)
update automatically to reflect those changes of other people.
However, the moment you submit the form (i.e. commit the transaction),
the server will sanity check it, and if all is well, enter the data
somewhere. If not, you get an error.
(This is actually a quite close metapher for what Database Transactions
do, so do not think that I am kidding :))
> You should use CommitRetaining instead. With this function the
> transaction is comitted, but not closed. But only Firebird supports this
> feature.
Hmm... seems quite nice, however it fits badly into the above picture.
What you say is, "I just want everyone else to see the new data (mine
and the one from the other people merged), but only want to see the old
one (just mine) myself", correct?
That is kind of backwards, sounds like you aren't actually done with
entering all the data and should not be committing yet... (I see a
corner usecase for it to be in adding log-file-like entries though)
I can see that one wants the user to think that the user is the most
important person in the world, but one shouldn't lie by showing him
stuff that is not what is actually in the database after the "save"
button :)
However, if you argue that in-flight modifications are a corner case and
that it usually doesn't happen and therefore, the new data and the old
data have the same content, maybe... but that's wishful thinking :)
>
> > Is correct the code? The problem is that the query is closed. So I have
> > that to open again and the pointer goes for the first record. I used the
> > bookmark, but, not work. How to keep the pointer on the current register?
> > Do
> > you know?
>
> You should keep the select-transaction and commit-transaction seperate.
> So do the update-queries in another transaction then the select-queries.
No. For reading/modifying single records, just use one transaction in
total. Actually in most cases, when you have more than one transaction
open per application, something is seriously broken, probably (or it's
just complex... probably broken ;))
If you use the select-query only for rough live-browsing, then yeah,
just keep it outside the transaction - that is, with _another
connection_ with most database management systems (still need to refresh
that from time to time, though).
>
> For that a property must be added to TSQLQuery with the
> 'UpdateTransaction'. I think that with that option this problem can be
> solved.
>
No. A transaction is to keep a "Local Copy" of the data in the database.
It is so you have a point in time (when you started the transaction) to
rely on to synchronize the data with your brain (or whatever other
device).
If it were live-updating all the time, your brain state would go out of
sync with the current state of the database again and again because one
just can't keep track of all the changes with the eyes (AND you are
occupied just reading the data of the *current record* and searching for
stuff you want to change, so you don't have _time_ for it, either). I've
seen it in live, believe me. It's evil. :)
That said, there is a transaction isolation level to loosen what a
transaction shields from you (or from other connected entities).
Stuff that can happen:
1) Dirty Read: other guys can read all the stuff you are blabbering in
your slee... err.. transaction, even though it's not committed yet.
2) Non-repeatable read: whatever you read within the transaction does
not suddenly change when you aren't looking and look different to you
the next time like in a horror tale
3) Phantom rows: Rows suddenly appearing out of nowhere, like you
selected some table, and then someone else added a row, and you select
again some part of it and go "whooops! what is that row?" (or you
probably just modify it by accident and never notice).
so the transaction levels:
if you just want to witness the horrors :) "Read Uncommitted".
if you want to prevent 1), need at least "Read Committed".
If you want to prevent 2) as well, need at least "Repeatable Read".
If you want to prevent 3) as well, need at least "Serializable".
the command to set the transaction level is "SET TRANSACTION ISOLATION
LEVEL xyz". However, some database management systems are braindead and
change the isolation levels of ALL transactions of the entire system
(yes, not just yours). You have been warned :)
What I explained first was "Serializable".
Hope my explaination isn't too far off :)
cheers,
Danny
_________________________________________________________________
To unsubscribe: mail [EMAIL PROTECTED] with
"unsubscribe" as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
