Auction and table locking

Question

Auction and table locking

kangarolf

Hall of Fame

Points: 3432
More actions
May 21, 2003 at 12:06 pm

#81624

Hi all,
We are running a custom built auction site on one of our shared servers here. I would never have agreed to write the auction site of I had known how many pitfalls there were going to be!
Ebay seems to only work out the highest bid as people veiw items, or maybe even periodically. This means they dont actually have a single bid on any item.
We on the other hand decided we wanted to create the highest bid live, so when a person makes a maximum bid it is checked against the current bid and the current proxy bids etc. Once the bid row is opened it is locked till the current bid is entered preventing any bid overlap. If the table is locked when another person tried to bid they are informed that a bid is in progress.
My question relates to performance and how much of a bottleneck we can expect from locking rows in a table....Is this a workable strategy do you think or should we also go down the road of allowing all bids that exceed the current highest and then working out proxy winners and losers when an item is viewed or periodically...?
hope this makes sense...
Thanks in advance for any input..
Rolf

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

Huge issue. DO NOT LOCK the table/row/record/whatever. The thing to do if you want it to work like this is add a flag to the table. When someone needs to "lock" the row, set the flag and be sure everyone else's page/form/whatever respects this lock. Then update their bid and unlock the flag. It's basically the equivalent of raising a semaphore.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

http://www.dkranch.net

kangarolf Hall of Fame Points: 3432 More actions · Answer 2

Thought there might be some issues with locking...in what instances would locking be suitable then..?

Thanks again

Rolf

duncan410y Valued Member Points: 73 More actions · Answer 3

would

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

work?

kangarolf Hall of Fame Points: 3432 More actions · Answer 4

isnt that just a more precise way of locking tables...?

Rolf

kangarolf Hall of Fame Points: 3432 More actions · Answer 5

Thikning about the flagging method; wont that be just as suceptible to concurrent attempts...? Two users could be setting the flag to locked at the same time, they would both read the flag as being zeroed, set it then try to update. Basically between checking and writing the flag someone else could also check the flag...? If I use a IF clause before I update the flag would that eliminate the problem..?

Thanks again I have never written an application where non concurrent updates are so disastrous.

kangarolf Hall of Fame Points: 3432 More actions · Answer 6

Anyone else got any views on this concurrency problem or why I cant get my head round the conception of flagging being any more robust...?

Rolf

dj_meier SSCrazy Points: 2732 More actions · Answer 7

quote:
Two users could be setting the flag to locked at the same time, they would both read the flag as being zeroed, set it then try to update

You can update/attempt to update the row in a single statement, then check the result:



update bid_table

    set lock_bit = 1,

        lock_user = @Current_User,

        lock_time = GETDATE()

  where key_field = @Key_Value

        and lock_bit = 0

IF @@ROWCOUNT = 1

  --....Successful

ELSE

  --....Unable to update row (either missing or locked)

-Dan

kangarolf Hall of Fame Points: 3432 More actions · Answer 8

That last suggestion is neat but unfortunately I have to check first that the bid value has not changed since the initial point that the user started the bid. Tried a datetime column..ie retrieving the datetime of the last update on the row then doing all the checks then doing exactly the type of update suggested but good old ADO does not return the milliseconds of the datetime field! So it wasnt accurate.

I think if I use the NOLOCK hint when selecting single rows from the table for viewing on the web pages and use row locks when updating the pages I should be able to avoid too many deadlocks and lock escalations. Dirty reads are not a problem as the bid could change at any time anyway.

Any other suggestions ideas..?

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 9

Convert the datetime/ms to a string with CONVERT and return string to ADO back to app which can convert it back to the full monty of date/time/ms.

mworku Ten Centuries Points: 1311 More actions · Answer 10

It is better to let SQL handle resource locking for you since the it takes many

factors into account. Resolve resource contention issues at application

level. You can use a table to flag current bid activity and control updates based on the flag. The downside is that you my need to increase query timeout to accomodate high user

volume.

MW

kangarolf Hall of Fame Points: 3432 More actions · Answer 11

Thanks for all the replies.

So the general opinion is to not force SQL to lock a row but use a flag and attempt to write that flag as;

Update table where GUID = xx AND Flag = 0

Return the number of rows updated then if it is 1 then the flag locking is a success...go onto make the bid then set the flag to 0 once more.

I have never written a stored procedure but this seems to be an ideal candidate. Is it possible to write the procedure to check the @@ROWCONT and then return a value to the ASP application indicating success or failure..?

Thank you all

Rolf