Re: Alternative MySQL PEAR DB sequence behavior

Oleg Rekutin wrote: > > uksi@uksiland.com (Oleg Rekutin) wrote in > news:Xns90E697DCA66F4ukstah@216.92.131.4: > > > Uh oh, just as I began implementing LOCK TABLES during the conversion, > > I realized that we really should not it because LOCK TABLES unlocks all > > previous locked tables and commits any active transactions. This will > > mess up any application that attempts to retrieve the next ID during a > > transaction or if it locked some other tables in the system. > > > > I'm thinking of ways how to eliminate race conditions and not break any > > table locks or transactions. > > Alright, attached (hopefully the attachment goes thru, trying for the first > time w/ this client) is a patch that: > > - fixes new IDs to start from 1 > - puts a user-level lock on the conversion to prevent a race condition > where two threads attempt to convert the IDs table at the same time > > Now, there are a few catches w/ the conversion. I avoid using LOCK TABLES, > and instead use GET_LOCK, which does not commit any current transactions, > nor does it abort any current table locks. It does clear any current user- > level locks, but that's better than no lock at all. > > Possible multi-threading issues: > > 1) Two threads attempt to convert the table at the same time. > Outcome: one of the threads will perform the conversion, then > the second one will either time out waiting on the conversion > to perform (unlikely) or will attempt to perform the conversion > again (likely). The latter will have no effect (DELETE will affect > 0 rows). > > 2) One thread begins conversion (pre-lock), while another thread > attempts to UPDATE to get the nextId. > Outcome: the 2nd thread's UPDATE will fail due to duplicates > and it will attempt to perform the conversion, with the case #1 > occurring. > > 3) One thread is after SELECT highest ID but before DELETE all but > highest ID, while another thread attempts to UPDATE to get the > nextId. > Outcome: the 2nd thread's UPDATE will fail due to duplicates > and it will attempt to perform the conversion, moving on to case #1. > > So I think we're in good shape... Looks good (but I haven't run any concurrency tests), so I'll commit it. - Stig