insert question

[David Eades]

Hi all

Complete newbie here, so apologies if this is the wrong forum.

I've been asked to use mysql and asp to make a simple bidding system (rather
like a simple ebay), whereby users can use a web browser to view a highest
bid and can make a bid.

My question is; how can I be sure that when a user submits a bid, that
another user isn't also currently submittimg a bid, i.e i can tell user A
that he has the highest bid but then user B who had submitted just before,
but whose insert was still being processed, is actually higher. Is there a
way to make sure all transactions have been completed before the next on is
processed?

Many thanks for any help on this topic.

Dave

[Bowen]

After a quick think I would check the price the user has submitted by
comparing it to the current bid and making sure it was higher than the
current bid in the table....

Seems like a simple solution.....but I could be wrong.
David Eades wrote:

Hi all

Complete newbie here, so apologies if this is the wrong forum.

I've been asked to use mysql and asp to make a simple bidding system (rather
like a simple ebay), whereby users can use a web browser to view a highest
bid and can make a bid.

My question is; how can I be sure that when a user submits a bid, that
another user isn't also currently submittimg a bid, i.e i can tell user A
that he has the highest bid but then user B who had submitted just before,
but whose insert was still being processed, is actually higher. Is there a
way to make sure all transactions have been completed before the next on is
processed?

Many thanks for any help on this topic.

Dave

[Dave]

That seems the obvious approach, but my problem is that I'm not sure how
mysql works and if two people both bid at the exact same time then both
could have the highest bid, and I just want to be able to deal with that

"Bowen" <si***@xiano.co.ukwrote in message
news:11**********************@75g2000cwc.googlegro ups.com...

After a quick think I would check the price the user has submitted by
comparing it to the current bid and making sure it was higher than the
current bid in the table....

Seems like a simple solution.....but I could be wrong.
David Eades wrote:

>Hi all

Complete newbie here, so apologies if this is the wrong forum.

I've been asked to use mysql and asp to make a simple bidding system
(rather
like a simple ebay), whereby users can use a web browser to view a
highest
bid and can make a bid.

My question is; how can I be sure that when a user submits a bid, that
another user isn't also currently submittimg a bid, i.e i can tell user A
that he has the highest bid but then user B who had submitted just
before,
but whose insert was still being processed, is actually higher. Is there
a
way to make sure all transactions have been completed before the next on
is
processed?

Many thanks for any help on this topic.

Dave

[Bowen]

Yeah I can see your point....I think this article might of interest.

http://www.databasejournal.com/featu...le.php/3382171
Dave wrote:

That seems the obvious approach, but my problem is that I'm not sure how
mysql works and if two people both bid at the exact same time then both
could have the highest bid, and I just want to be able to deal with that

"Bowen" <si***@xiano.co.ukwrote in message
news:11**********************@75g2000cwc.googlegro ups.com...

After a quick think I would check the price the user has submitted by
comparing it to the current bid and making sure it was higher than the
current bid in the table....

Seems like a simple solution.....but I could be wrong.
David Eades wrote:

Hi all

Complete newbie here, so apologies if this is the wrong forum.

I've been asked to use mysql and asp to make a simple bidding system
(rather
like a simple ebay), whereby users can use a web browser to view a
highest
bid and can make a bid.

My question is; how can I be sure that when a user submits a bid, that
another user isn't also currently submittimg a bid, i.e i can tell user A
that he has the highest bid but then user B who had submitted just
before,
but whose insert was still being processed, is actually higher. Is there
a
way to make sure all transactions have been completed before the next on
is
processed?

Many thanks for any help on this topic.

Dave

[Dave]

Thanks for that, that is certainly of interest
"Bowen" <si***@xiano.co.ukwrote in message
news:11**********************@s13g2000cwa.googlegr oups.com...

Yeah I can see your point....I think this article might of interest.

http://www.databasejournal.com/featu...le.php/3382171
Dave wrote:

>That seems the obvious approach, but my problem is that I'm not sure how
mysql works and if two people both bid at the exact same time then both
could have the highest bid, and I just want to be able to deal with that

"Bowen" <si***@xiano.co.ukwrote in message
news:11**********************@75g2000cwc.googlegr oups.com...

After a quick think I would check the price the user has submitted by
comparing it to the current bid and making sure it was higher than the
current bid in the table....

Seems like a simple solution.....but I could be wrong.
David Eades wrote:
Hi all

Complete newbie here, so apologies if this is the wrong forum.

I've been asked to use mysql and asp to make a simple bidding system
(rather
like a simple ebay), whereby users can use a web browser to view a
highest
bid and can make a bid.

My question is; how can I be sure that when a user submits a bid, that
another user isn't also currently submittimg a bid, i.e i can tell
user A
that he has the highest bid but then user B who had submitted just
before,
but whose insert was still being processed, is actually higher. Is
there
a
way to make sure all transactions have been completed before the next
on
is
processed?

Many thanks for any help on this topic.

Dave

[Gordon Burditt]

>Complete newbie here, so apologies if this is the wrong forum.

>
I've been asked to use mysql and asp to make a simple bidding system (rather
like a simple ebay), whereby users can use a web browser to view a highest
bid and can make a bid.

My question is; how can I be sure that when a user submits a bid, that
another user isn't also currently submittimg a bid, i.e i can tell user A
that he has the highest bid but then user B who had submitted just before,
but whose insert was still being processed, is actually higher.

I believe the way you stated it violates causality. Order the bids
(mostly by who gets to the MySQL server first). A bid has to be
higher than bids before it. How it relates to bids coming AFTER
it is not relevant, and will be taken care of when that later bid
is processed.

Use transactions. For each bid, check that the current bid is
(sufficiently - if you have minimum bid increments) higher than the
previous bid (or maximum of all bids, if you wish). If it isn't,
inform the user (and possibly roll back the transaction, if you
made any changes by this point). If it is, accept the bid, and
insert it, then commit the change. The transaction mechanism makes
sure that no other user manages to make any changes between the two
(or more) queries making up the transaction.

If the version of MySQL you have does not support transactions,
you can use table locking. Transactions are a lot nicer.

Gordon L. Burditt

[Dave]

"Gordon Burditt" <go***********@burditt.orgwrote in message
news:12*************@corp.supernews.com...

Complete newbie here, so apologies if this is the wrong forum.

I've been asked to use mysql and asp to make a simple bidding system
(rather
like a simple ebay), whereby users can use a web browser to view a highest
bid and can make a bid.

My question is; how can I be sure that when a user submits a bid, that
another user isn't also currently submittimg a bid, i.e i can tell user A
that he has the highest bid but then user B who had submitted just before,
but whose insert was still being processed, is actually higher.

I believe the way you stated it violates causality. Order the bids
(mostly by who gets to the MySQL server first). A bid has to be
higher than bids before it. How it relates to bids coming AFTER
it is not relevant, and will be taken care of when that later bid
is processed.

Use transactions. For each bid, check that the current bid is
(sufficiently - if you have minimum bid increments) higher than the
previous bid (or maximum of all bids, if you wish). If it isn't,
inform the user (and possibly roll back the transaction, if you
made any changes by this point). If it is, accept the bid, and
insert it, then commit the change. The transaction mechanism makes
sure that no other user manages to make any changes between the two
(or more) queries making up the transaction.

If the version of MySQL you have does not support transactions,
you can use table locking. Transactions are a lot nicer.

Gordon L. Burditt

Thanks for the reply Gordon, but I'm a little unsure what causality means,
could you eleborate a little for me?

Also, would you advise using one table for all bids, or a table for all bids
plus a table for highest current bid. If it were one table, could two users
be using transactions at the same time and end up both being successful? Or
does this mean that a table is locked whilst one user is checking and is
only unlocked once the COMMIT is called?

Many thanks

Dave

[Gordon Burditt]

>>>My question is; how can I be sure that when a user submits a bid, that

>>>another user isn't also currently submittimg a bid, i.e i can tell user A
that he has the highest bid but then user B who had submitted just before,
but whose insert was still being processed, is actually higher.

I believe the way you stated it violates causality. Order the bids
(mostly by who gets to the MySQL server first). A bid has to be
higher than bids before it. How it relates to bids coming AFTER
it is not relevant, and will be taken care of when that later bid
is processed.

Use transactions. For each bid, check that the current bid is
(sufficiently - if you have minimum bid increments) higher than the
previous bid (or maximum of all bids, if you wish). If it isn't,
inform the user (and possibly roll back the transaction, if you
made any changes by this point). If it is, accept the bid, and
insert it, then commit the change. The transaction mechanism makes
sure that no other user manages to make any changes between the two
(or more) queries making up the transaction.

If the version of MySQL you have does not support transactions,
you can use table locking. Transactions are a lot nicer.

>Gordon L. Burditt

Thanks for the reply Gordon, but I'm a little unsure what causality means,
could you eleborate a little for me?

Violating causality means a requirement that you tell the first
bidder that he's about to be overbid by a second bid that's been
sent but you haven't received, or haven't processed, yet. It also
includes things like informing the winner he won before bidding has
started or before the seller decides to put the item up for sale.

>Also, would you advise using one table for all bids, or a table for all bids
plus a table for highest current bid.

It isn't that difficult to select the latest bid for a particular
item out of a list of bids for a particular item. (Bids that are
not above the current bid price by the minimum bid increment aren't
valid and don't get entered at all).

If you think it's a performance issue, e.g. you get a lot of hits
for people just LOOKING at the item which has to include the current
bid, so finding the current bid has to be fast, you can have a
separate table with the current bid. If you use two tables, you
have to update both tables within the same transaction to preserve
consistency.

You *COULD* use only the second table (current highest bid and who
made it), leaving out the bid history. This is a bad idea as it
leaves no evidence around for debugging or an evidence trail in
case of lawsuits over who should have won.

Before you design this part, decide how you will deal with BID
RETRACTION. This has implications about whether you look for the
HIGHEST (valid) bid vs. the LAST (valid) bid. Someone tries to bid
$14.99 for a used DVD, but manages to leave out the decimal point.
Then you get a frantic email that he didn't really mean to bid
$1,499.00 . You will find you need the bid history table if you
allow bid retraction, to figure out who is the new current high
bidder after the retraction. How do you continue this auction?

>If it were one table, could two users
be using transactions at the same time and end up both being successful? Or

Assuming "successful" means "passing a check that the bid amount
is greater than the current bid by at least the minimum bid increment",
yes, they could (e.g. current bid $5, next guy bids $7 and someone
else almost simultaneously bids $8, never having seen the $7 bid.
The first guy won the bid and then almost instantly got overbid.
Happens all the time, and it's not a problem.), but not if they
both bid the same amount, or the second guy bids lower than the
first. In the latter case, the second guy will get a rejection
message that his bid is too low, and the new current bid.

>does this mean that a table is locked whilst one user is checking and is
only unlocked once the COMMIT is called?

Transactions do not have to lock TABLES. They can still work
(usually better, especially if you've got thousands of simultaneous
auctions going on with many bids per second) if they only lock
specific RECORDS, so 9 people bidding on 9 different items don't
have to wait for each other. The nice thing is you usually don't
have to care about details like this when writing the code.

A transaction adding a bid does not have to block a query about the
current high bid to display for a user looking at the item. The
price displayed may be instantly stale, but that's always a possibility
with auctions. A transaction entering a bid will block another
transaction entering another bid. Everything done in a transaction
LOOKS LIKE it happened at one point in time, with everything done
by any other connection happening either before or after that time.

Incidentally, transactions are often written in a style of "start
making changes now, check things later (make sure your query didn't
fail), then if something fails, roll it back". That way if your
last insert gets a duplicate key violation, you don't have to go
to the trouble of checking everything BEFORE inserting (let the
database do it on insert), nor do you have to write complex code
to undo a partially inserted transaction. ROLLBACK will do it for
you. It also deals with the client program dumping core or getting
manually killed: if the MySQL connection breaks before commit, the
transaction gets rolled back.

Gordon L. Burditt

[Dave]

"Gordon Burditt" <go***********@burditt.orgwrote in message
news:12*************@corp.supernews.com...

>>>>My question is; how can I be sure that when a user submits a bid, that
another user isn't also currently submittimg a bid, i.e i can tell user
A
that he has the highest bid but then user B who had submitted just
before,
but whose insert was still being processed, is actually higher.

I believe the way you stated it violates causality. Order the bids
(mostly by who gets to the MySQL server first). A bid has to be
higher than bids before it. How it relates to bids coming AFTER
it is not relevant, and will be taken care of when that later bid
is processed.

Use transactions. For each bid, check that the current bid is
(sufficiently - if you have minimum bid increments) higher than the
previous bid (or maximum of all bids, if you wish). If it isn't,
inform the user (and possibly roll back the transaction, if you
made any changes by this point). If it is, accept the bid, and
insert it, then commit the change. The transaction mechanism makes
sure that no other user manages to make any changes between the two
(or more) queries making up the transaction.

If the version of MySQL you have does not support transactions,
you can use table locking. Transactions are a lot nicer.

>>Gordon L. Burditt

Thanks for the reply Gordon, but I'm a little unsure what causality means,
could you eleborate a little for me?

Violating causality means a requirement that you tell the first
bidder that he's about to be overbid by a second bid that's been
sent but you haven't received, or haven't processed, yet. It also
includes things like informing the winner he won before bidding has
started or before the seller decides to put the item up for sale.

>>Also, would you advise using one table for all bids, or a table for all
bids
plus a table for highest current bid.

It isn't that difficult to select the latest bid for a particular
item out of a list of bids for a particular item. (Bids that are
not above the current bid price by the minimum bid increment aren't
valid and don't get entered at all).

If you think it's a performance issue, e.g. you get a lot of hits
for people just LOOKING at the item which has to include the current
bid, so finding the current bid has to be fast, you can have a
separate table with the current bid. If you use two tables, you
have to update both tables within the same transaction to preserve
consistency.

You *COULD* use only the second table (current highest bid and who
made it), leaving out the bid history. This is a bad idea as it
leaves no evidence around for debugging or an evidence trail in
case of lawsuits over who should have won.

Before you design this part, decide how you will deal with BID
RETRACTION. This has implications about whether you look for the
HIGHEST (valid) bid vs. the LAST (valid) bid. Someone tries to bid
$14.99 for a used DVD, but manages to leave out the decimal point.
Then you get a frantic email that he didn't really mean to bid
$1,499.00 . You will find you need the bid history table if you
allow bid retraction, to figure out who is the new current high
bidder after the retraction. How do you continue this auction?

>>If it were one table, could two users
be using transactions at the same time and end up both being successful?
Or

Assuming "successful" means "passing a check that the bid amount
is greater than the current bid by at least the minimum bid increment",
yes, they could (e.g. current bid $5, next guy bids $7 and someone
else almost simultaneously bids $8, never having seen the $7 bid.
The first guy won the bid and then almost instantly got overbid.
Happens all the time, and it's not a problem.), but not if they
both bid the same amount, or the second guy bids lower than the
first. In the latter case, the second guy will get a rejection
message that his bid is too low, and the new current bid.

>>does this mean that a table is locked whilst one user is checking and is
only unlocked once the COMMIT is called?

Transactions do not have to lock TABLES. They can still work
(usually better, especially if you've got thousands of simultaneous
auctions going on with many bids per second) if they only lock
specific RECORDS, so 9 people bidding on 9 different items don't
have to wait for each other. The nice thing is you usually don't
have to care about details like this when writing the code.

A transaction adding a bid does not have to block a query about the
current high bid to display for a user looking at the item. The
price displayed may be instantly stale, but that's always a possibility
with auctions. A transaction entering a bid will block another
transaction entering another bid. Everything done in a transaction
LOOKS LIKE it happened at one point in time, with everything done
by any other connection happening either before or after that time.

Incidentally, transactions are often written in a style of "start
making changes now, check things later (make sure your query didn't
fail), then if something fails, roll it back". That way if your
last insert gets a duplicate key violation, you don't have to go
to the trouble of checking everything BEFORE inserting (let the
database do it on insert), nor do you have to write complex code
to undo a partially inserted transaction. ROLLBACK will do it for
you. It also deals with the client program dumping core or getting
manually killed: if the MySQL connection breaks before commit, the
transaction gets rolled back.

Gordon L. Burditt

Thanks very much, Gordon, for this very detailed response, I guess the most
important point is that queries from different connections happen
sequentially and not all happen at the same time, which I think is what you
have written in your post, i.e one insert will complete before an insert
from another connection, even if both connections were made at the same
time.

Many thanks

Dave

[Michael Austin]

Dave wrote:

"Gordon Burditt" <go***********@burditt.orgwrote in message
news:12*************@corp.supernews.com...

>>>>>My question is; how can I be sure that when a user submits a bid, that
>another user isn't also currently submittimg a bid, i.e i can tell user
>A
>that he has the highest bid but then user B who had submitted just
>before,
>but whose insert was still being processed, is actually higher.

I believe the way you stated it violates causality. Order the bids
(mostly by who gets to the MySQL server first). A bid has to be
higher than bids before it. How it relates to bids coming AFTER
it is not relevant, and will be taken care of when that later bid
is processed.

Use transactions. For each bid, check that the current bid is
(sufficiently - if you have minimum bid increments) higher than the
previous bid (or maximum of all bids, if you wish). If it isn't,
inform the user (and possibly roll back the transaction, if you
made any changes by this point). If it is, accept the bid, and
insert it, then commit the change. The transaction mechanism makes
sure that no other user manages to make any changes between the two
(or more) queries making up the transaction.

If the version of MySQL you have does not support transactions,
you can use table locking. Transactions are a lot nicer.

>>>>Gordon L. Burditt

Thanks for the reply Gordon, but I'm a little unsure what causality means,
could you eleborate a little for me?

Violating causality means a requirement that you tell the first
bidder that he's about to be overbid by a second bid that's been
sent but you haven't received, or haven't processed, yet. It also
includes things like informing the winner he won before bidding has
started or before the seller decides to put the item up for sale.

>>>Also, would you advise using one table for all bids, or a table for all
bids
plus a table for highest current bid.

It isn't that difficult to select the latest bid for a particular
item out of a list of bids for a particular item. (Bids that are
not above the current bid price by the minimum bid increment aren't
valid and don't get entered at all).

If you think it's a performance issue, e.g. you get a lot of hits
for people just LOOKING at the item which has to include the current
bid, so finding the current bid has to be fast, you can have a
separate table with the current bid. If you use two tables, you
have to update both tables within the same transaction to preserve
consistency.

You *COULD* use only the second table (current highest bid and who
made it), leaving out the bid history. This is a bad idea as it
leaves no evidence around for debugging or an evidence trail in
case of lawsuits over who should have won.

Before you design this part, decide how you will deal with BID
RETRACTION. This has implications about whether you look for the
HIGHEST (valid) bid vs. the LAST (valid) bid. Someone tries to bid
$14.99 for a used DVD, but manages to leave out the decimal point.
Then you get a frantic email that he didn't really mean to bid
$1,499.00 . You will find you need the bid history table if you
allow bid retraction, to figure out who is the new current high
bidder after the retraction. How do you continue this auction?

>>>If it were one table, could two users
be using transactions at the same time and end up both being successful?
Or

Assuming "successful" means "passing a check that the bid amount
is greater than the current bid by at least the minimum bid increment",
yes, they could (e.g. current bid $5, next guy bids $7 and someone
else almost simultaneously bids $8, never having seen the $7 bid.
The first guy won the bid and then almost instantly got overbid.
Happens all the time, and it's not a problem.), but not if they
both bid the same amount, or the second guy bids lower than the
first. In the latter case, the second guy will get a rejection
message that his bid is too low, and the new current bid.

>>>does this mean that a table is locked whilst one user is checking and is
only unlocked once the COMMIT is called?

Transactions do not have to lock TABLES. They can still work
(usually better, especially if you've got thousands of simultaneous
auctions going on with many bids per second) if they only lock
specific RECORDS, so 9 people bidding on 9 different items don't
have to wait for each other. The nice thing is you usually don't
have to care about details like this when writing the code.

A transaction adding a bid does not have to block a query about the
current high bid to display for a user looking at the item. The
price displayed may be instantly stale, but that's always a possibility
with auctions. A transaction entering a bid will block another
transaction entering another bid. Everything done in a transaction
LOOKS LIKE it happened at one point in time, with everything done
by any other connection happening either before or after that time.

Incidentally, transactions are often written in a style of "start
making changes now, check things later (make sure your query didn't
fail), then if something fails, roll it back". That way if your
last insert gets a duplicate key violation, you don't have to go
to the trouble of checking everything BEFORE inserting (let the
database do it on insert), nor do you have to write complex code
to undo a partially inserted transaction. ROLLBACK will do it for
you. It also deals with the client program dumping core or getting
manually killed: if the MySQL connection breaks before commit, the
transaction gets rolled back.

Gordon L. Burditt

Thanks very much, Gordon, for this very detailed response, I guess the most
important point is that queries from different connections happen
sequentially and not all happen at the same time, which I think is what you
have written in your post, i.e one insert will complete before an insert
from another connection, even if both connections were made at the same
time.

Many thanks

Dave

The most simple method is to use a data/timestamp that has microsecond precision
and the "winner" is highest bid+latest timestamp.

--
Michael Austin.
DBA Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)