Lester Caine wrote:

> Jochem Maas wrote:
>
>
>>Lester Caine wrote:
>>
>>
>>>I'm having a number of giggley little problems which are causing agro a
>>>few times a week across some sites while some never have the problem at all.
>>>
>>>The one that was spotted today is quite simple. Get ID from generator,
>>>Add ticket with that ID, then process ticket.
>>>
>>>"INSERT INTO TICKET ( TICKET_ID, TICKET_REF, TICKET_NO, OFFICE, ROOM,
>>>STAFF_ID, INIT_ID, CALLER_ID, APPLET )
>>>VALUES ( $ticid, 'NOW', (SELECT COALESCE(MAX(TICKET_NO)+1, 1) FROM
>>>CURRENT_DAY
>>>WHERE OFFICE = $office AND TICKET_NO BETWEEN 0 AND 9999),
>>>$office, 0, 0, 0, 0, ' ' )";
>>>
>>>I know that the MAX(TICKET_NO)+1 is not very safe, but the script seems
>>>to be getting run TWICE as I get TWO ID's, but the same TICKET_NO with
>>>an almost identical 'NOW'.
>>>
>>>Anybody any ideas what can be happening. Is it possible that PHP is
>>>running a script twice with two different transactions, from the same
>>>browser (IE of cause) and the second run does not see the commit of the
>>>first run? If that makes sens? I can't explain these occasional
>>>duplicates any other way, as once the ticket is added the user gets a
>>>'clear ticket before adding a new one' and also the Add Ticket button is
>>>not present on the page that Add Ticket goes to!
>>
>>it sounds like 2 different insertions (via 2 different requests) where
>>the transactions are overlapping and the results of the one that started first
>>is not being seen by the one start second... I really recommend a generator
>>for the TICKET_NO.
>
>
> That would not help - I'd STILL get two records just with two different
> TICKET_NO's

right but you wouldn't have the race condition anymore with rtegard to TICKET_NO

> I dropped the generator because of problems getting it reset to '0' at
> the start of each day - it just was not happening in some offices so
> they were ringing up and saying 'We haven't started at 1, somethings not
> working!' - Things would be fine without customers :)

I can hardly imagine that could not be solved with a stored procedure or two
that the insert trigger would call... I actually have some 'sweepstake' related SQL
that automatically creates/removes generators based on records (that each represent
a 'sweepstake') being inserted/deleted with the relevant dynamically created
generator being used for tickets(entries) for each sweepstake.

one has to question why the ticket numbers must start at 1 each day - although
without konwing all the context and background it's impossible to judge the 'correctness'
of such an implementation (not to mention customers often require stuff that make your
toes curl ;-)

>