RE: [PEAR-DEV] Idea for pear sequence tables

Yes, this is true that there will be lock contention, but there would be lock contention to a certain extent on a "sequence" table also. The smallest piece that mosts DBs can lock for a write is a single block, which in small tables like this, most likely contains more that one row, maybe even the whole table. When something was committed to disk, then the whole block again has to be locked (although pre and post imaging hide this somewhat). That said, it does not change the fact that I agree that it would be better to put all of the sequences in one table. There are many situations where unique ids per table, or at least in some tables, is a requirement. In most cases it is desired per table simply so the sequences are reasonable. The only reason that I can see to leave them in a separate table is the perception that it is faster. The reason that I say perception is that in a small table like this, I would bet that the access time is miniscule whether you are reading two dozen records, or one. The down side of them being in separate tables is that you have two dozen separate tables junking up the schema. This is not a trivial thing. So, why would a "single" sequence table not be done? Thanks, Rob > -----Original Message----- > From: Jeroen Houben [mailto:jeroen@terena.nl] > Sent: Tuesday, September 16, 2003 4:18 AM > To: Tomas V.V.Cox > Cc: Stowe, Nola; pear-dev@lists.php.net > Subject: Re: [PEAR-DEV] Idea for pear sequence tables > > > Tomas V.V.Cox wrote: > > >On Friday, September 12, 2003 17:52, Stowe, Nola wrote: > > > > > > > >>I'm sure someone has thought of this and you have a good reason for NOT > >>doing it.. but..I'm curious on why you didn't do it this way? > >> > >> > > > > > > > >>Instead of having another table created for each table I need a > sequence on, > >>what about having a sequence table (seq_name, seq_key) with > rows for each > >>table and then just fetching and updating that table with the next key? > >> > >> > > > >Well, a sequences is just that a pool of unique id's for using in your > >tables. I never understood why one would like to have multiple > >sequences, while just one for all your tables is enough. > > > > > > > I'm not postive about this, but I think having one sequence for all your > tables slows things down. A lock will be accuired to get the unique ID, > so if there is a lot of data written to various tables all using the > same sequence they all have to wait for eachother to get the next ID. > > Another reason is different tables might use different sequences: > Table A might use 2,4,6,8,10 ... > Table B might use 2003.001, 2003.002, 2003.003, 2003.004 .... > > HTH > > Jeroen > > -- > PEAR Development Mailing List (http://pear.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >