How to solve the concurrency conflict in ado.net

It sounds like you only want to insert into Down when there are less than 20 rows. If so: make that a single operation:

insert dbo.Down (UserName)select @UserNamewhere (select count(1) from dbo.Down) < 20select @@rowount -- 1 if we inserted, 0 otherwise

Alternatively, if you *need*you will need to use a transaction, ideally "Serializable", so that you get a key-range-lock - perhaps even adding (UPDLOCK) to the initial count, to ensure it takes an eager write lock (or blocks, rather than deadlocks). But: the single TSQL operation (as already illustrated is preferable. You could even make that more paranoid (although I'm not sure it needs it):

declare @count intbegin traninsert dbo.Down (UserName)select @UserNamewhere (select count(1) from dbo.Down (UPDLOCK)) < 20set @count = @@rowountcommit transelect @count -- 1 if we inserted, 0 otherwise

Consider doing this in reverse and it will probably be much simpler. For example:

• Create a table with an auto-incrementing index. Call it "Up", or "RequestOrdering", or whatever.
• Get client requests in any order. For each request:
  • Insert a new row into Up.
  • Get the last inserted row ID.
  • If the last insert ID <= 20, do a real insert.
• Throw away your Up table when you're done with it.

If your database supports multi-column primary keys with one of them auto-incrementing (IIRC, MyISAM tables do), you could re-use the "Up" table across multiple product specials.

An even easier implementation would be insert 20 rows into "Down" and delete one per request. Check the number of affected rows (should be 1) to see if the user was successful. This plays nicely with multi-product specials. For example:

delete * from Down where down_special_id = Xxx limit 1

Probably easiest, and to also keep track of who "won", create one row per product and have each user update one (and only one) row. Again, check the number of affected rows to see if they were successful:

update Upset  user_name = @user_namewhere  user_name is null  and product_id = @product_idlimit 1