On Thu, 31 Jan 2002, DL Neil wrote: > Date: Thu, 31 Jan 2002 21:28:04 -0000 > From: DL Neil <[EMAIL PROTECTED]> > To: "William R. Mussatto" <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED] > Subject: Re: next insert id (slightly OT) > > William, > > > > What should be understood is that MySQL's implementation of AUTO_INCREMENT >requires a particular/different > > > philosophical view: that first the 'primary row' is to be stored, then the >AUTO_INCREMENT data captured, and > > > finally the dependent row is stored (in the second table) - a reversal of the >earlier-mentioned approach. As > > > soon as one understands how the whole job is to be done, the logic of the >individual components becomes > > > 'obvious'. (and the old dog has to remember his new tricks!) > > ... > > I've run into one case where knowing the "next" ID would be useful was > > where file names, being stored in the record contained the ID (for > > performance reasons or maybe my bad design) since I was storing graphics > > separately (for web work it makes great sense since the name needs to go > > on the page not the data). I had to do an insert, get the > > LAST-INSERTED-ID, and then update the record. I suspect that this is > > really faster if there are multiple updated going on than locking the > > table. Also, it enables me to use fixed length records. > > =one of the previous correspondents in this (the original) thread described how any >anticipatory calculation > based upon LAST_INSERTED to arrive at the next AUTO_INCREMENT value was essentially >flawed. It will work in a > single-user environment, but without locking, risks corruption if the scenario >allows for multiple > concurrent-updates to the table. That is why I did an add, got the LAST-INSERTED-ID.
> > =in this case, you avoided the 'look-ahead' but at the cost of an extra db update >call! (two for the price of > one?) > > =do you mean that the graphic file name was the same as the AUTO_INCREMENT value in >the corresponding tbl row? > Do you further mean that the design featured only the one column, or was there an ID >column and a graphic name > column? The name was derived from the ID. This was so the display program could just grab it and go. > > =if the ID was the name, then to avoid the multi-user problem, the row could have >been saved and then > LAST_INSERTED used to (re-)name the graphic file. This sequence being congruent to >the philosophy of MySQL's > operation (as mentioned earlier/above) - why did you feel the need to name the file >before storing its data in > the table? (why not sequence them the other way around?) Ah, but the name had to go into the database at some point. > =if the ID and the filename were separate columns, then there was definitely no need >to use > ancitipation/look-ahead, any name would do as long as it was unique - you don't care >and the machine knows no > reason! In other words for row-x you could use the LAST_INSERTED value - the fact >that it was a copy of the ID > from the previous row of the table would be neither here-nor-there (there's no >implicit, nor need there be an > explicit, relationship between the two columns - and relational rules suggest that >there should not be in any > case). This presumes that you will name the first file/data in the first row >yourself - filename 0000.filetype > for example! > =it's always a risk if you use a single field to mean two different things and yet >fulfill objectives in two > different contexts (trouble is, we all try to get away with it...) Caught me, I use a configuration table to tell the use of the various fields and if the field is of type file the contents is read and subsitituted. This was mainly a reaction to a version of mysql (since fixed) that required the server be halted to do table reorgs. (Yes yes I know its no longer true). Also I'm a fan of fixed lenght records and putting large things on the file system and things that need to be searched in the database. > > =BTW: yes I note that this is ancient history and not a current problem to be solved >- just watching out for the > risk-factors that you discuss below, and the fact that someone else might want to >'learn' from your post. > > > Didn't even think of locking the tables. > > =too much hassle for this "bear of very little brain" too! > > > BTW: an earlier poster mentioned DB2. You have to have a separate Key > > table and use an Update with a Sub Select and lock that table to get the > > next key since DB2 still (as of the version I was using) have > > auto-increment fields. > > =there you go, I don't remember using AUTO_INCREMENT since I first used DB2 (that >was when the paint was still > wet, and the performance about as slow pouring!) I think I've only ever 'patched' >DB2 calls - or translated them > into ORACLE for the last 15 years... > > > Don't know if its true, but a DBA I worked with insisted that MicroSoft's > > SQL's auto-increment was flawed so we had to do the same thing there, > > even though it did have auto-increment. Perhaps a hold over from the > > "doing it by hand" days.. > > =I've never been a great fan of MS SQL-Server, but I seem to recall that there was a >'discovered fault' of this > nature in one (?early) release. It was fairly arcane IIRC. It was reasonably quickly >patched though. Meantime I > do confess to using the incident as part of my 'proof' that the product wasn't 'up >to snuff' for > corporate/enterprise systems... > > =These days I would always use the RDBMS' AUTO_INCREMENT (or equivalent) feature. I >can think of no good reason > (in any current 'mainstream' RDBMS) to manufacture a tbl ID/sequence - and if you >have to manufacture some > accumulator in the dependent part of a row, then there is no way out (in a >multi-user environment) but to use > locking and MAX() etc. [see also this topic discussed somewhere in the MySQL manual] > > =Thanks for the memories! > =dn > > Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
