This is more a business process / UI race. It is often solved by deprecating / hiding values for a period before deleting and/or not accepting them as valid anymore.
One approach would be to add a DateDeprecated column to toys. Update it to the current date when you check and nobody has this toy as a favorite. Clear the field if/when the toy is selected as a ...
Well, I don't know if this would help but using negative logic like "NOT EXISTS" means that every row has to be examined (locked) to determine if it meets the criteria. If you can find a way to make it positive logic like "EXISTS", it should only evaluate the rows that qualify both speeding up the query and reducing the locks. Just a ...
Since the row is not moved around in the CLUSTERED index, because the primary key does not change, why is it read a second time?
Actually it can be moved even if you didn't update a clustered key. Update of non-key column value or update/insert of other rows on the same page may cause a page split and half of the rows will be moved to the new page.
You are correct in your initial assumptions here. If the row don't move (including due to a page split), then a scan/seek won't read it twice. But, as you say, perhaps the query is done through an NC index for which the row index was moved, hence the double read.
Regarding terminology, there are some standard phenomena defined in ANSI SQL, which are then ...
SELECT ... FOR UPDATE will not work in this case. It still requires SERIALIZABLE isolation level because what you need to do is to prevent other sessions from inserting rows with the same groupId. And to do this you need to lock a key (what actually SERIALIZABLE level does). What you can try to do?:
check and adjust your indexes (to not lock more rows than ...
... make sure that each user request will be handled by a single thread of my servers.
Why do you feel the need to do this?
Channelling all the processing of a [clustered] application into a bottleneck where only one process can work with the database at any one time is completely alien to the way that way that modern, Relational Databases work.
I would ...
// Positions table - 3 spots for `xx-xx-xx` job, only 1 spot is free
position_id | job_id | user_id
aa-aa-aa | xx-xx-xx | ee-ee-ee -- used spot
bb-bb-bb | xx-xx-xx | ff-ff-ff -- used spot
cc-cc-cc | xx-xx-xx | NULL -- free spot
The amount of spot rows per a job is equal to jobs....
Does Microsoft have any sort of documentation on this?
Yes, lots. One particularly good reference is the Data Loading Performance Guide.
I am trying to understand what is happening under the hood on the SQL Server side. Is there a queue for all of the bulk inserts, and each bulk insert is executed one by one, in the order the came in? Or are all the ...