2

I am currently looking into how I can manage a high number of bids on my auction site project. As it is quite possible that some people may send bids at exactly the same time it has become apparent that I need to ensure that there are locks to prevent any data corruption.

I have come down to using SELECT LOCK IN SHARE MODE which states that If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.

http://dev.mysql.com/doc/refman/5.1/en/innodb-locking-reads.html

This suggests to me that the bids will enter a queue where each bid is dealt with and checked to ensure that the bid is higher than the current bid and if there are changes since an insert is put in this queue then the latest bid amount is used.

However, I have read that there can be damaging deadlock issues where two users try to place bids at the same time and no query can maintain a lock. Therefore I have also considered using SELECT FOR UPDATE but this will then also disable any reads which i am quite unsure about.

If anybody could shed any light on this issue that would be appreciated, if you could suggest any other database like NoSQL which would be more suitable then that would be very helpful!!!

EDIT: This is essentially a concurrency problem where i don't want to be checking the current bid with incorrect/old data which would therefore produce a 'lost update' on certain bids.

Daniel West
  • 1,808
  • 2
  • 24
  • 34

1 Answers1

3

By itself, two simultaneous updates will not cause a deadlock, just transient blocking. Let's call them Bid A and Bid B.

Although we're considering them simultaneous, one will acquire a lock first. We'll say that A gets there 1 ms faster.

A acquires a lock on the row in question. B has it's lock request go in queue and must wait for the lock belonging to A to be released. As soon as lock A is released, B acquires it's lock.

There may be more to your code but from your question, and as I've described it, there is no deadlock scenario. In order to deadlock, A must be waiting for B to release it's lock on another resource but B will not release it's lock until it acquires a lock on A's resource.

If you need to validate the bid in real time you can either:

A. Use the appropriate transaction isolation level (repeatable read, probably, which is the default in InnoDB) and perform both your select and update in an explicit transaction. 

BEGIN TRAN
    SELECT ... FOR UPDATE
    IF ...
    UPDATE ...
COMMIT

B. Perform your check logic in your Update statement itself. In other words, construct your UPDATE query so that it will only affect rows when the current bid is less than the new bid. If no records were affected, the bid was too low. This is a possible approach and reduces work on the DB but has it's own considerations. 

UPDATE ...
WHERE currentBid < newBid

Personally my vote would be to opt for A because I don't know how complex your logic is.

A repeatable read isolation level will ensure that a every time you read a given record in a transaction, the value is guaranteed to be the same. It does this by holding a lock on the row which prevents others from updating the given row until your transaction either commits or rolls back. One connection cannot update your table until the last has completed it's transaction.

The bottom line is your select/update will be atomic in your DB so you don't have to worry about lost updates.

Regarding concurrency, the key there is to keep your transactions as short as possible. Get in, get out. By default you can't read a record that is being updated because it is in an indeterminate state. These updates and reads should be taking small fractions of a second.

Code Magician
  • 23,217
  • 7
  • 60
  • 77
  • 1
    For an application like this you want `serializable`, at least for the actual "check status, place bid" part. – Ben Jan 26 '12 at 17:24
  • @M_M Thanks! Just what I needed. Just needed it clarified! – Daniel West Jan 26 '12 at 17:29
  • @Ben Using `serializable` won't make any difference if I am using transactions surely? – Daniel West Jan 26 '12 at 17:33
  • 1
    A good read on isolation levels in InnoDB http://www.ovaistariq.net/597/understanding-innodb-transaction-isolation-levels/ – Code Magician Jan 26 '12 at 17:39
  • @Ben In what way? From what I can gather `SERIALIZABLE` will completely lock the row from other transactions but if there is already a transaction in progress, surely this will stop it? Or can uncommitted changes still be made to the row under `REPEATABLE READ`? – Daniel West Jan 26 '12 at 17:54
  • 1
    @DanielWest, see the answer to this question for a good explanation of the difference: http://stackoverflow.com/questions/4034976/difference-between-read-commit-and-repeatable-read. – Ben Jan 26 '12 at 18:20
  • 1
    A `SELECT ... FOR UPDATE` under the default `repeatable read` will give you the same benefit as a `SERIALIZABLE` transaction. – Code Magician Jan 26 '12 at 18:55