On 10-Nov-2003,
googlemike@hotpop.com (Google Mike) wrote:
[color=blue]
> Thanks to the programmers of PHP, they made the SharedMemory API. That
> solved the problem. You see, it appears that the API in PHP that
> writes to MySQL works somewhat asynchronously sometimes, especially
> when forced by someone hitting pages very rapidly. If one wants to do
> an insert, followed by an update, they may end up with two different
> records in some cases where they don't use the unique ID column, but
> some other column or set of columns. The resolution I used was to make
> one single insert with everything, including the unique ID, but the
> trick question I had for awhile was, "How do I ensure that I'm going
> to be the one holding the record with the unique ID, and not some
> other process take that ID?"
>
> The answer, you see, is shared memory, but let's back up a bit and see
> the process.
>
> 1) I need to select a record from the TicketsOpen table and I happen
> to know it's ID. This record has an ID column with a guaranteed unique
> ID, and a tracking number (TN) with an approximate unique ID.
>
> (The TN is short and is used to give out to customers. It cannot be
> guessed easily, so customers cannot fib. We only use the TN ID when we
> want to zoom in on the record approximately. We have to ask further
> questions to ensure we are working with the right ticket. The
> algorithm for TN is based on second, month, day, and a small random
> number.)
>
> 2) Okay, now I need to void this record, so I need to move this to the
> TicketsVoided table.
>
> (Why not just have a Void bit on the record in the TicketsOpen table?
> Well, there's two reasons: (a) table size is an issue, and (b) we want
> faster SELECTS. Even though I can restrict a SELECT to ignore a Void
> bit, it still is faster if there are no voided records in the opened
> tickets table. You see?)
>
> I now do a SELECT MAX(ID) FROM TicketsVoided. And, of course, I want
> to increment that var with ++. But that won't work, will it? I mean,
> another user could be on this same web page, doing the same thing with
> another record, and we could end up clobbering each other's records.
>
> The solution is to use shared memory in PHP. I create a shared memory
> variable that stores my username if I'm currently doing the write
> process to the target table. Then, when I'm finished, I clear the
> variable to nothing. So, before any user can do SELECT MAX(ID)...,
> they must wait in a small loop until the shared memory var is free.
> Then, they can stick their username in there. While waiting, I use
> usleep(500) to sleep a series of half seconds, up to 4 seconds max,
> until this var is free. If that 4 seconds is up, then I assume
> something is wrong and I just go ahead and let the user use the shared
> memory var and start doing SELECT MAX(ID).
>
> 3) Okay, now that I have guaranteed without a doubt 100% that I now
> have the latest ID for a record to insert into TicketsVoided, I do an
> INSERT to write it there.
>
> 4) I now delete the record from the source table (TicketsOpen).
>
> 5) Now the record is moved, so I clear the shared memory variable,
> freeing up any other process that's waiting to have the permission to
> do a move to this table.
>
> In a sense, shared memory is my workaround for transactions.
>
> And so why didn't I do this with MySQL's transactions, or another
> database? Because I'm trying to stay ANSI-92 here, and I want to
> produce something that could use almost any database backend,
> including PostgreSQL, MS SQL, Oracle, etc.[/color]
An alternative to the shared memory would be a table to store the used ids,
just add a row and retrieve the id it generated. If someone else is doing
the same they'll get a different row/id.
--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to
jamesbutler@willglen.net (it's reserved for spammers)
