CODEWITHSUNDEEP
sql-serverlockingisolation-levelread-uncommitted
Kenny
Kenny

Reputation: 1982

SQL Server Isolation level real world example

Let's say we need to develop a bidding application such as one in eBay. We don't want one user's bidding to block another user's bidding, which will result in slow response. Also, when I place a bid based on the highest price I see, I don't want to arrive at the end with the app saying that sorry, the highest price was no longer the same; while I am placing the bid someone has pushed the price higher.

Which isolation level should we use? I was thinking of Read Uncommitted for dirty read and no locking but not sure.

I would like to hear more real-world examples/use-cases for isolation level in SQL Server (or in general, any other database software, if there are similarities between them regarding isolation levels). For me I find it not very efficient just looking at definitions.

Thank you.

Upvotes: 0

Views: 849

Answers (1)

underscore_d
underscore_d

Reputation: 6791

READ UNCOMMITTED sounds about right, in context of the scenario you envision... but that scenario sounds pretty bad!

when I place a bid based on the highest price I see, I don't want to arrive at the end with the app saying that sorry, the highest price was no longer the same; while I am placing the bid someone has pushed the price higher.

Maybe I'm misunderstanding, but do you mean if someone else bid while you were preparing your bid? If so, how else is it meant to work? Will you win, even though you bid lower, simply because the time of starting your bid was earlier? That won't work. This has to be decided at the time your bid is submitted.

Really, this should run with READ COMMITTED, so that the user's bid is affected by all the previous ones - at the point that is it submitted. That is a requirement for a sensible auction. Bids should run in temporal sequence, wrapped by TRANSACTIONs, and always be subject to the latest maximum at the time that their transaction began, i.e. when the user hit the Submit button, they should be subject to the sum of all bids submitted before theirs.

Upvotes: 1

Related Questions