Hi,
Am Dienstag, den 13.12.2005, 13:36 +0100 schrieb Joost van der Sluis:
> Hi,
>
> thanks for your extensive answer,
>
> > 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).
>
> On that I disaggree with you. Most of the 'simple'-open source RDBM's
> implemented the Transaction Isolation that way, but it can be far more
> advanced.
>
> > 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.
>
> Why not? If you want to share the changes you made with the outside
> world, but you don't want to see the changes that are made by the
> outside world. How would you do that? The select queries are still
> valid. They obtain the data from the moment that the transaction was
> started, plus the local changes. Those changes are also 'committed' to
> the outside world.
>
> > 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.
>
> And that's exactly the problem. In web-forms it's designed this way,
> since a more advanced approach is not possible. And since most basic
> databases (do I hear MySQL?) are written mostly for web-designers, they
> implemented the transaction isolation that way.
>
> You can safely browse the database in a transaction. Then with the
> commit(retaining) you can save your changes, without changing the start
> of your transaction, thus the data in the database.
I see ...
>
> > 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 :))
>
> In the most basic way, yes.
>
> > > 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?
>
> No, you see the old data plus your own changes. That last part is
> handled bu SqlDB. Or if you use a more advanced database, like
> Oracle/Firebird (only more advanced in this perspective?), by Oracle.
>
> > 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)
>
> For example. But also if you have all your data stored in a local memory
> buffer. Then you don't have to refresh your data after each commit. (If
> you want this, is up to the developer)
Yeah... I see the point in caching data locally and wanting to put it
into the database as soon as modifying something ...
>
> And this behaviour is very usefull in 3-(or more) thiered applications.
>
> > 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 :)
>
> As a developer you could choose for that. But you can also choose to
> show his local data, and add a 'refresh'-button to see what was
> happening in the outside world.
>
> > 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 :)
>
> The transaction(s) are very well isolated. You see the data from the
> moment of the start of the transaction. Plus your own changes, and you
> can commit those changes during the progress.
Yes... I see now
>
> > > > 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 choose to select and edit only one record, you're right. (As you
> would in a web-app) But if you are working with a more sophisticated
> language...
>
> Why not select 20 customers at once, put them in a local buffer, filter
> three out of them, change their name. Commit, select three others and
> change their names to? Commit and close the transaction and the
> application?
>
> And only one transaction at a time..... That's only for the basics. With
> Oracle I can start a transaction, make some changes, ask someone 10.000
> km away to log in into that same transaction so he can check the data.
> Then he can make his changes (in a sub-transaction), commit them, so I
> can see them. And only if we both aggree on committing this 'global
> transaction' it is committed to the rest of the users, which are working
> in other transactions.
I see... didn't know that is possible...
>
> And how about transactions which work on more then one db-
> server/database at the same time? (one transaction, two db-servers?)
Are these what is called two-phase commit or are they still something
else?
>
> Transactions is so much more then that.
>
>
> > 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).
>
> Only databases that don't support advanced transactions use the
> connection as a sort of transaction. That is what you describe here.
I see... on other database management systems 1 connection can have n
concurrently open transactions ?
Very nice...
How does that look in SQL ? (or is it mostly accessible by some API)
>
> An application should have one connection to one server/db. But can work
> with more then one transaction on the same server.
I see... I seem to be biased by the database management systems that I
used... (or just prejudice ;))
So the transactions are like mini-worlds in that a commit of one
transaction does not change what another transaction's queries see?
So the DB stuff in a programming language should be more like this?:
A connection can have n transactions.
A transaction can have n queries.
Refreshing a query only can see data within the assigned transaction.
Data returned by queries can be edited.
When it is, the transaction can be committed. The commit can be made
"Retaining", which will cause the transaction start marker stay where it
is but the data be committed to the database for the others to see.
>
> > > 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 a developer want that, he can. If he wants something more advanced,
> he can. If he want to screw up, he can.
>
> But it's not a script-language in which we have to protect the
> developers from theirselves, imho.
>
> > 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. :)
>
> It doesn't update. It only saves live. Your local copy stays the same.
I see... that's a good middle path to choose :)
>
> > 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".
>
> This is really database-dependent. The systems I use have far more
> transaction-isolation levels, and combinations of them.
>
> > 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 :)
>
> That is completely to the developer. On most systems you can however
> give the transaction-isolation of you start the transaction. Atm there
> is a property on TSQLTransaction which you can use to set this.
Really? Is there a SQL command (or command sequence) for that?
>
> > What I explained first was "Serializable".
>
> > Hope my explaination isn't too far off :)
>
> It's a very good explanation, and I think it is also a good basis for a
> sort of Transaction-basics article or something. Not only fpc-related,
> but maybe something for on the lazarus/fpc-wiki?!?
>
Why not :)
(Better weed out the obvious mistakes first though :))
cheers,
Danny
_________________________________________________________________
To unsubscribe: mail [EMAIL PROTECTED] with
"unsubscribe" as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
