I'm working with modify application which uses mysql, so that is really the
only thing that has with mysql to do. It's just that it feels like I have run
into a wall and hope that there would be someone who could give some new ideas
to try.


I have a couple of tables, which could hastly be described like

table customer, primary key customerid

table paylogfile, primary key id (inkludes columns customer_id and reference_id)

table logfile, primary key reference_id

All primary keys are auto_increment


The customer table will be always filled with a new row,
the paylogfile and logfile will only be filled if a purchase has been successful.

The problem is that the payment is done on an external system and I need to
send a reference number, which should be the same as the reference_id. If the
payment fails, then the reference number isn't registered on the external system.

So I would need to get the next reference_id from logfile table

SELECT reference_id+1 FROM logfile ORDER BY reference_id DESC LIMIT 1

but this has the disadvantage if you have more than one customer at the same
time, then you would suddenly have two customers with the same reference_id,
which can cause trouble.

I could of course LOCK tables, but it seems I won't get that option.

In the logfile table I have to have only valid purchases only and the
reference_id has to follow n+1 all the time.

I don't see any secure way to make this to work without LOCK, but do someone
have a nice idea to test, increasing the number of tables is all ok.

--

//Aho

On Nov 2, 5:30 pm, "J.O. Aho" <u...@example.net> wrote:
> I'm working with modify application which uses mysql, so that is really the
> only thing that has with mysql to do. It's just that it feels like I have run
> into a wall and hope that there would be someone who could give some new ideas
> to try.
>
> I have a couple of tables, which could hastly be described like
>
> table customer, primary key customerid
>
> table paylogfile, primary key id (inkludes columns customer_id and reference_id)
>
> table logfile, primary key reference_id
>
> All primary keys are auto_increment
>
> The customer table will be always filled with a new row,
> the paylogfile and logfile will only be filled if a purchase has been successful.
>
> The problem is that the payment is done on an external system and I need to
> send a reference number, which should be the same as the reference_id. If the
> payment fails, then the reference number isn't registered on the external system.
>
> So I would need to get the next reference_id from logfile table
>
> SELECT reference_id+1 FROM logfile ORDER BY reference_id DESC LIMIT 1
>
> but this has the disadvantage if you have more than one customer at the same
> time, then you would suddenly have two customers with the same reference_id,
> which can cause trouble.
>
> I could of course LOCK tables, but it seems I won't get that option.
>
> In the logfile table I have to have only valid purchases only and the
> reference_id has to follow n+1 all the time.
>
> I don't see any secure way to make this to work without LOCK, but do someone
> have a nice idea to test, increasing the number of tables is all ok.
>
> --
>
> //Aho

Why not insert into logfile, send the reference number (last inserted
id), and, if the payment fails, delete the row with that reference
number? The reference number won't be re-used so that shouldn't cause
any problems.

ZeldorBlat wrote:
> On Nov 2, 5:30 pm, "J.O. Aho" <u...@example.net> wrote:
>> I'm working with modify application which uses mysql, so that is really the
>> only thing that has with mysql to do. It's just that it feels like I have run
>> into a wall and hope that there would be someone who could give some new ideas
>> to try.
>>
>> I have a couple of tables, which could hastly be described like
>>
>> table customer, primary key customerid
>>
>> table paylogfile, primary key id (inkludes columns customer_id and reference_id)
>>
>> table logfile, primary key reference_id
>>
>> All primary keys are auto_increment
>>
>> The customer table will be always filled with a new row,
>> the paylogfile and logfile will only be filled if a purchase has been successful.
>>
>> The problem is that the payment is done on an external system and I need to
>> send a reference number, which should be the same as the reference_id. If the
>> payment fails, then the reference number isn't registered on the external system.
>>
>> So I would need to get the next reference_id from logfile table
>>
>> SELECT reference_id+1 FROM logfile ORDER BY reference_id DESC LIMIT 1
>>
>> but this has the disadvantage if you have more than one customer at the same
>> time, then you would suddenly have two customers with the same reference_id,
>> which can cause trouble.
>>
>> I could of course LOCK tables, but it seems I won't get that option.
>>
>> In the logfile table I have to have only valid purchases only and the
>> reference_id has to follow n+1 all the time.
>>
>> I don't see any secure way to make this to work without LOCK, but do someone
>> have a nice idea to test, increasing the number of tables is all ok.
>
> Why not insert into logfile, send the reference number (last inserted
> id), and, if the payment fails, delete the row with that reference
> number? The reference number won't be re-used so that shouldn't cause
> any problems.

As that way the n+1 for reference_id won't be true, you will get holes in the
numbering and all the purchases in that table has to be in the order that the
pay has been accepted.

The whole thing had been a lot easier if I could have used another
reference_code between the external system and the paylogfile table.



--

//Aho