As always, it depends on what you're doing, how many users, how fast a network, how many transactions per minute, but:
1) FLOCKing a table means no one can update ANY record, where 2) RLOCKing the one record in the keys table that has the primay key you're incrementing has a much smaller window of opportunity for contention, and only one place in the code (the NewID() function you'll likely write as a Stored Procedure) where you have to handle the lock conflict, timeouts and retries. In my experience in most of the systems I've written, there are more updates than inserts. In addition, your FLOCK requires a more complex processing, SELECT MAX() that would require index access, file traversal, record readng, than the simple LOCK, READ, UPDATE, UNLOCK process of the separate table. I'm a big, big, big fan of non-data-bearing, integer primary keys for VFP tables. The entire engine is tuned to make them wickedly efficient, and programmer-friendly, too. If you've got to do data syncronization (master-master) there are other better designs, but for most of the SMB low-transaction data processing systems I've worked on, this is the way to go. Yes, it's true, VFP throws something called a "header lock" on a table any time it updates the number of records, and does some kind of index table locking when updating indexes, but the engine has the built in logic to handle the infrequent, transient conflicts these cause. There's even a SYS() function to tune the process. But in most day-to-day processes, these are too fast to worry about. On Sat, Aug 25, 2012 at 5:33 PM, Frank Cazabon <[email protected]> wrote: > No, if you do the flock, then you won't get duplicates. But you should only > try to lock records or tables for the minimum time necessary. So using a > separate table, locking the record, incrementing it and unlocking should be > faster than your example and result in less contention for the lock > > On 25 Aug 2012 12:31, "Lew Schwartz" <[email protected]> wrote: > > Getting back to max()+1 and ignoring one sided b-tree & dirty buffer > issues, are you guys telling me that: > > > flock(mytable) > > select max()+1 as idno into temp > > insert values (,,temp.idno) > > blah, blah ... > > unlock > > Won't reliably produce unique idno's. (I don't have a network avail to > test this out myself). > > > > --- StripMime Report -- processed MIME parts --- > multipart/alternative > text/plain (text body -- ... > > _______________________________________________ > Post Messages to: [email protected] > Subscription Main... > This message: > http://leafe.com/archives/byMID/profox/cafuu78fhtwfjto9h9exzxai-lsstxkxjbsgtjaxesmwcuux...@mail.gmail.com > > ** All postings, unless explicitly stated otherwise, are the opinions of > the author, and do not cons... > Report [OT] Abuse: > http://leafe.com/reportAbuse/cafuu78fhtwfjto9h9exzxai-lsstxkxjbsgtjaxesmwcuux...@mail.gmail.com > > > --- StripMime Report -- processed MIME parts --- > multipart/alternative > text/plain (text body -- kept) > text/html > --- > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/cacw6n4u2d-tg3hant9+ljzdtvquo6syegock6239g0ph+us...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
