Hi all,

I have a situation where the user can add an item to my database by
clicking an add button which POSTs the form details and they are added
to the DB.

The addition requires manipulation of several tables, so I don't want
the PHP script getting killed half way through and leaving the DB in
an inconsistant state. I see that the ignore_user_abort() function
will do that for me. All good.

However, I pondered what would happen if the user clicked the add
button twice in quick succession. The first click would kick off the
PHP script which would not get aborted by the second click do to my
ignore_user_abort() call. The second click would then (I assume?) kick
off another instance of the script which would also try and add the
item. I would have two scripts both working on inserting the same
object and I can forsee lots of muck ups.

My tables are currently MyISAM for performance reasons, and also
because they have FULLTEXT indexes on them, so I have no native DB
transactional support.

I am pondering the idea of writing a random transaction key to the
form so that once the first script gets kicked off, it marks that key
as "processed" in the session data, so that the second script can
check it and abort. This will mean I have to write out the session
data mid script but I don't see that as a problem.

Any recommendations on whether this is a good idea or not? It's going
to be a real arse ache if I have to convert my tables to InnoDB :-/

Many thanks,
Jeremy

Jeremy@thebunnyshed.co.uk wrote:
> Hi all,
>
> I have a situation where the user can add an item to my database by
> clicking an add button which POSTs the form details and they are added
> to the DB.
>
> The addition requires manipulation of several tables, so I don't want
> the PHP script getting killed half way through and leaving the DB in
> an inconsistant state. I see that the ignore_user_abort() function
> will do that for me. All good.
>

That can , but it's not necessarily a good solution.

> However, I pondered what would happen if the user clicked the add
> button twice in quick succession. The first click would kick off the
> PHP script which would not get aborted by the second click do to my
> ignore_user_abort() call. The second click would then (I assume?) kick
> off another instance of the script which would also try and add the
> item. I would have two scripts both working on inserting the same
> object and I can forsee lots of muck ups.
>

First of all, the inserts shouldn't take long. If they do, you need to
fix that problem. And when you're done with the inserts, redirect to a
new page with header().

> My tables are currently MyISAM for performance reasons, and also
> because they have FULLTEXT indexes on them, so I have no native DB
> transactional support.
>

This you need to follow up on in comp.databases.mysql.


> I am pondering the idea of writing a random transaction key to the
> form so that once the first script gets kicked off, it marks that key
> as "processed" in the session data, so that the second script can
> check it and abort. This will mean I have to write out the session
> data mid script but I don't see that as a problem.
>

The hard way to go about it.

> Any recommendations on whether this is a good idea or not? It's going
> to be a real arse ache if I have to convert my tables to InnoDB :-/
>
> Many thanks,
> Jeremy
>

Nope. Fix the problems. Even several inserts should be done in very
short time (i.e. < 1 second). If they're taking long enough for the
user to abort, you have a problem.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Hi,

Like you have pointed out, transactions are the most reliable way to
perform this sequence of operations.

Using sessions should work fine. Technically speaking, some less-used
session handlers don't guarantee exclusive session access, but the
default one does.

FYI, another way is to insert that randomly generated token into a
table with a UNIQUE constraint. Other locking mechanisms include flock
and sem_acquire...

Regards,

John Peters

On Feb 29, 8:25 pm, Jer...@thebunnyshed.co.uk wrote:
> Hi all,
>
> I have a situation where the user can add an item to my database by
> clicking an add button which POSTs the form details and they are added
> to the DB.
>
> The addition requires manipulation of several tables, so I don't want
> the PHP script getting killed half way through and leaving the DB in
> an inconsistant state. I see that the ignore_user_abort() function
> will do that for me. All good.
>
> However, I pondered what would happen if the user clicked the add
> button twice in quick succession. The first click would kick off the
> PHP script which would not get aborted by the second click do to my
> ignore_user_abort() call. The second click would then (I assume?) kick
> off another instance of the script which would also try and add the
> item. I would have two scripts both working on inserting the same
> object and I can forsee lots of muck ups.
>
> My tables are currently MyISAM for performance reasons, and also
> because they have FULLTEXT indexes on them, so I have no native DB
> transactional support.
>
> I am pondering the idea of writing a random transaction key to the
> form so that once the first script gets kicked off, it marks that key
> as "processed" in the session data, so that the second script can
> check it and abort. This will mean I have to write out the session
> data mid script but I don't see that as a problem.
>
> Any recommendations on whether this is a good idea or not? It's going
> to be a real arse ache if I have to convert my tables to InnoDB :-/
>
> Many thanks,
> Jeremy

In article <TfGdnXUHEPyqW1XanZ2dnUVZ_q2hnZ2d@comcast.com>,
jstucklex@attglobal.net says...
> Nope. Fix the problems. Even several inserts should be done in very
> short time (i.e. < 1 second). If they're taking long enough for the
> user to abort, you have a problem.
>
>

if you're relying on the fact that inserts "should be done in a very
short time" for your software to work at all reliably then you're in real
trouble no amount of fixing this program will cure.

This response indicates a total disastrous approach to programming. Your
program shouldnt care how long it takes to do the update - or whether or
not the user aborts at any time - which - I think was your original
worry.

Jeremy@thebunnyshed.co.uk wrote:
> Hi all,
>
> I have a situation where the user can add an item to my database by
> clicking an add button which POSTs the form details and they are added
> to the DB.
>
> The addition requires manipulation of several tables, so I don't want
> the PHP script getting killed half way through and leaving the DB in
> an inconsistant state. I see that the ignore_user_abort() function
> will do that for me. All good.
>
> However, I pondered what would happen if the user clicked the add
> button twice in quick succession. The first click would kick off the
> PHP script which would not get aborted by the second click do to my
> ignore_user_abort() call. The second click would then (I assume?) kick
> off another instance of the script which would also try and add the
> item. I would have two scripts both working on inserting the same
> object and I can forsee lots of muck ups.
>
> My tables are currently MyISAM for performance reasons, and also
> because they have FULLTEXT indexes on them, so I have no native DB
> transactional support.
>
> I am pondering the idea of writing a random transaction key to the
> form so that once the first script gets kicked off, it marks that key
> as "processed" in the session data, so that the second script can
> check it and abort. This will mean I have to write out the session
> data mid script but I don't see that as a problem.
>
> Any recommendations on whether this is a good idea or not? It's going
> to be a real arse ache if I have to convert my tables to InnoDB :-/
>
> Many thanks,
> Jeremy
Its not clear as to whether you are using MySql or just raw files as the
database.

If using MYSQL simply LOCK the tables and unlock them after they are
updated.

If not using MySql, consider using it..ter are reaosns to funnel all
database access through a single management pioint, and you have just
identified the most useful one.

On Mar 1, 4:34am, petersprc <peters...@gmail.com> wrote:
> Usingsessionsshould work fine. Technically speaking, some less-used
> session handlers don't guaranteeexclusivesession access, but the
> default one does.

Ah! Now that's interesting! I was worried that using a key in the
session data would still cause problems if the second script managed
to read the key before the first script managed to write away the fact
that it was processing that key.

I've just had a quick ponder over the session docs at php.net but
can't find anything regarding exclusive access other than a user
comment that session_start() will block if another script it still
using the session.

If this is true it could solve all my problems! Thanks!

> FYI, another way is to insert that randomly generated token into a
> table with a UNIQUE constraint. Other locking mechanisms include flock
> and sem_acquire...

Thanks I will take a look at those options too. The token in the DB
doesn't particularly appeal to me since you then have extra storage
and index overhead, but it is certainly an option thanks.

Cheers,
Jeremy

On Mar 1, 2:23am, Jerry Stuckle <jstuck...@attglobal.net> wrote:

> Nope. Fix the problems. Even several inserts should be done in very
> short time (i.e. < 1 second). If they're taking long enough for the
> user to abort, you have a problem.

I'm afraid I disagree. Even if the inserts were done ultra quick it
would be possible for the second script to get in there if (for
example) the first script request got held up somewhere in network
traffic.
From working with highly multithreaded applications I've found that if
it can happen, it WILL happen :-(
I need some way of properly serialising access.

On Mar 1, 12:41pm, The Natural Philosopher <a...@b.c> wrote:

> Its not clear as to whether you are using MySql or just raw files as the
> database.
Yes, MySQL. (hence the table type comments)

> If using MYSQL simply LOCK the tables and unlock them after they are
> updated.
That's a thought yeah. Don't know much about locks as I'm a fair
newbie still, but I seem to remember that MyISAM tables lock the
entire table, and I would have to lock several tables at once. Dunno
if this will cause performance problems due to other users being
locked out. I will have to gen up on the lock mechanics a bit, but
thanks :-)