Very Strange MySQL Problem

[Google Mike]

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

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.

[Tom Thackrey]

On 9-Nov-2003, go********@hotpop.com (Google Mike) wrote:

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 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.

Just a thought, but are you using ignore_user_abort() 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
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@willglen.net (it's reserved for spammers)

[Google Mike]

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]

On 10-Nov-2003, go********@hotpop.com (Google Mike) wrote:

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.

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 ja*********@willglen.net (it's reserved for spammers)

[Google Mike]

"Tom Thackrey" <us***********@nospam.com> wrote

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.

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.