Very Strange MySQL Problem

**Tom Thackrey** · Jul 17 '05, 01:02 AM

Re: Very Strange MySQL Problem

On 9-Nov-2003, googlemike@hotp op.com (Google Mike) wrote:

[color=blue]
> I have RH9 and am using the PHP and MySQL that came with it. I was
> doing fine with all manner of my web pages for this app until I
> started having this very strange problem.
>
> It's a work order mgmt system. I have 3 tables for the orders:
> TicketsOpen, TicketsVoided, and TicketsResolved . When one wants to
> void a ticket, they click it, choose Void, and it is copied to the
> TicketsVoided table, then removed from the TicketsOpen. And I can
> imagine you can apply that same concept to the TicketsResolved table.
> Likewise, I have a process where one can unvoid and reopen (unresolve)
> a ticket, copying back to TicketsOpen, and deleting from the previous
> table.
>
> (What I'm about to mention below will take some brain cells. Use em if
> you got em. Otherwise, skip this posting.)
>
> The Void.php web page will SELECT the ticket from TicketsOpen. I use 2
> IDs for a ticket. One is the identity column: ID. The other is a
> TRACKING_NUMBER (TN for short), which is a random, non-unique
> alphanumeric string that is used for informing the customer. It's
> simply based on month, day, a random number in the thousands, and then
> is Base36 encoded to shorten it up. The Void.php page is told what the
> ID is, so it reads the record into variables. Then, it changes the TN
> variable to (TN . ' capture') -- we'll call that TN2 for short. Then,
> it does an INSERT to write this record data to TicketsVoided. Then, it
> does a SELECT on TN from TicketsVoided for the TN2 value. When it
> finds it, it gets the new ID. (ID2, let's call it.) Then, it does an
> UPDATE where record ID is ID2, setting the TN (that had a value of
> TN2) back to TN w/o ' capture'. Then, it does a DELETE on TicketsOpen
> by the original ID. It concludes by providing a link to go back to the
> ticket queue for TicketsOpen. Again, the pseudocode is:
>
> id1 = initial ticket ID of TicketsOpen that we want to void
> orec = select our void request on id = ID1 from TicketsOpen
> tn1 = orec.tn
> tn2 = orec.tn + ' capture'
> orec.tn = tn2
> insert orec into TicketsVoided
> vrec = select from TicketsVoided where tn = tn2
> id2 = vrec.id
> vrec.tn = tn1
> update TicketsVoided with vrec where tn = tn2
> delete from TicketsOpen where id = id1[/color]
[color=blue]
> Well, this runs just fine if I go slow. But if I start firing void.php
> faster with my mouse, mostly likely by the fourth or fifth time it
> will run without an error message, but return an incorrect result in a
> variety of ways. Sometimes it never deletes the original record from
> TicketsOpen. Sometimes it creates two records in TicketsVoided but
> with only one of them having the TN set to TN2, while the other is set
> to TN1.
>
> And I bet you anything that if I move the whole project to PostgreSQL,
> this problem won't occur.[/color]

Just a thought, but are you using ignore_user_abo rt() to prevent the script
from terminating mid transaction?

Why are you using TN instead of id in your where clauses? If TN is not
unique, you are risking some very weird db problems down the road. Why not
use the unique record id instead of the random number in composing the TN so
it will be unique or don't use TN as the key in your select/insert/updates.

You might consider one table for tickets and marking them with a status or
having a status table which could prevent the problem caused by having to
move records between tables.

--
Tom Thackrey

870 Creative Light

https://www.creative-light.com

tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@wil lglen.net (it's reserved for spammers)

**Google Mike** · Jul 17 '05, 01:03 AM

Re: Very Strange MySQL Problem

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.

**Tom Thackrey** · Jul 17 '05, 01:03 AM

Re: Very Strange MySQL Problem

On 10-Nov-2003, googlemike@hotp op.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

870 Creative Light

https://www.creative-light.com

tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@wil lglen.net (it's reserved for spammers)

**Google Mike** · Jul 17 '05, 01:05 AM

Re: Very Strange MySQL Problem

"Tom Thackrey" <use.signature@ nospam.com> wrote[color=blue]
> 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.[/color]

Yeah. No duh. Man, I must have been stupid. I forgot that I want this
app to be web-farmable, and have taken every effort to make all
session state be in encrypted browser cookies. Well, when I have to
use shared memory to manage transaction queues of users trying to move
records between the various tables, I completely forgot that this only
works in a one-server environment. So, it looks I'm going to have to
use the idea you suggest. Of course, if I didn't have this MySQL
problem where the INSERTS and UPDATES didn't seem to run
asynchronously in a web page if the page is hammered, I wouldn't need
this, but I guess I'm going to need this.

Very Strange MySQL Problem

Very Strange MySQL Problem

Comment

Comment

Comment

Comment