fixed typo: Am Dienstag, den 13.12.2005, 01:14 +0100 schrieb dannym: > 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
fixed grammar: 2) Non-repeatable read: whatever you read within the transaction suddenly changes when you aren't looking and look different to you the next time like in horror tales > > 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 > _________________________________________________________________ To unsubscribe: mail [EMAIL PROTECTED] with "unsubscribe" as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
