Triynko

Thanks, will do. I just thought it would be more useful as a comment closely associated with the answer than a separate answer. I appreciate all the links to policies, etc.

I know you were the author, I wasn't posting it for you, because it wasn't posted in a chat with you. It was posted as a comment on an answer, to add to it, because it adds useful information about how an update statement locks. In particular, the read/lock phase does not overlap with the update phase. The comments were deleted from the post, and re-created here.

Wow, some other commenter from Microsoft was really sour when I informed it its answer was wrong. I explained that 'use sp_getapplock', which I'm quite familiar with, is not a solution, because it does nothing to avoid the page lock issue. Unless it was suggesting I serialize all reservations by having them all take literally the same lock, which is nuts because it would kill scalability of the app. Reservations for disjoint sets of objects ids should run concurrently and should not interfere with one another at all. That must be why it down-voted this and deleted its answer. Too bad.

It is trivial, and I already supplied what you asked for, in a single line, in the comment before you questioned whether it was trivial. BEGIN TRAN; update GlobalInventory set [Count] = [Count] + @QuantityToReserve where ObjectID = @object_id. That's the query to update global inventory. If it doesn't use row locks, it could inadvertently lock pages with other ObjectIDs, invalidating the algorithm, because lock order couldn't be guaranteed. So, row locks are required. It seems like the people trying to answer this so far are not understanding the question or don't have the expertise.

Suppose... is a synonym for 'given that'. There's nothing precluding a concrete example following such a statement. You're literally trying to start an argument by making such obtuse claims. Suppose I'm running SQL Server 2016 and I execute a 'select' statement on a table with a single column... that's pretty concrete, because I can easily do that. Claiming it's not concrete is playing dumb.

It's a terrible answer. It suggests a slower mechanism that's not comparable to row-locks. It also contradicts an established premise, so it's dead wrong. It's also makes a bogus assertion about performance of an update statement that wasn't even relevant to the question. This requires an expert in SQL Server locking behavior. All others should walk away.

This isn't a hypothetical. The information provided is very specific, and describes the exact operations involved. It describes the precise structure of the two sole tables involves and the sole indexes and their keys involved. 1. Start a transaction. Update the target rows in one table. 2. Update the target row in the second table. Finally, commit the transaction. Exclusive row locks are used for the updates. Anyone who can't understand that probably isn't capable of answering the question.

Meanwhile, row locks on different existing rows never conflict. I've also seen replication interfere with this a really nasty way, where this kind of very specific locking causing 'versioned ghost records' to pile up on a table, but was never able to completely understand why or whether it would resolve itself eventually.

FWIW, I have seen deadlock occur, and I have seen the expected row locks not taken out properly as a result of, for example, having more than one index on the table. I also mentioned that selecting a non-existing row with an exclusive row locks consumes such a slot exclusively, such that if one transaction takes out an exclusive row lock on non-existing row 'A', another transaction will not only NOT be able to take out a lock on row 'A', but it won't be able to take out a lock on ANY other non-existing row. It's like locking a non-existing row is some kind of singleton lock bucket.

This is a question that requires expertise in SQL Server locking. It has a definite answer, so it's not relevant whether I am or am not experiencing deadlock. Based on the information provided, the question is whether deadlock is possible without forcing the use of row locks. Logically, deadlock would be possible without guaranteeing row-level locks, so this is a question about SQL server specifically and how row-level locking functions. SQL Server 2019 is simply the latest version of SQL Server that we'll be using.

Then again, another comment there says lock escalation can be disabled by using: "ALTER TABLE tableName SET (LOCK_ESCALATION=DISABLE).

This does not give me hope: stackoverflow.com/questions/3114826/… Although, part of what I mentioned is that it's important that there's is only a single index on the table, because it may take out locks on 'index keys' rather than 'rows'. This is a non-issue when an index key and a row are equivalent as a result of only having a single primary key index. Another interesting facet of this I found from experiments is when taking an exclusive row lock on a non-existent row; only one such lock can be held, regardless of the key used.

What are you voting to close?

A side effect of everyone generating EM waves would be that corps couldn't use them to transmit info reliably, and a side effect of everyone copying information is businesses trying to impose artificial scarcity on virtual resources will fail to make money.

@Chris S: Do you think you can stop people from copying music and software? Of course not, so making a law against it is strange and pointless. Giving someone ownership over something that's not real... an interpreted, imagined pattern, like information, makes no sense, and if people were as apt to generate EM fields as they are to copy data, the same problem would arise.

@Chris: We are an EM field.

Well, that's debatable. I'm sure there are some people who could figure out how to generate EM waves without knowing how far they'd reach or what barriers they may or may not pass though.

If I could control them... wouldn't it be strange to outlaw a thought?

My brain puts off EM waves...

@Chris S: The wind determines the direction of the smoke, not the person. Similarly, physics determines that an EM pulse travels outward in all directions... not the person making the pulse.

Exactly. Rules are strange.

and it travels into your house. Can u outlaw that? Did your neighbor decide the direction of the wind that day?

It's like... if your neighbor is smoking and has a window open...

@Chris S: What gives anyone the right to dictate how the EM field should look, when it's something that penetrates virtually everything. If I want to disrupt the field in my house; I cannot help that the waves will travel outward and into someone else's house, and may cause a pattern of fluctuation that they cannot interpret as they'd like.

It's interpreted from an EM fluctuation, the same way an image is interpretted from a JPG bit stream.

There's no such thing as an EM frequency though.

Even though, with two very different computational transformations may turn either stream of bits into a very similar image.

Ok, consider two images. One compressed with JPEG, and one as BMP. The sequence of bits is entirely different.

It's like copyright. Pointless.

An EM frequency is no more a limited resource, than is a zero or a one.

It's strange like... outlawing clouds that happen to arrange themselves into the shape of a corporate logo.

It's useful to have a rule that says "don't emit EM waves at this frequency, it will interfere with the stuff we're doing", but it's strange because of the nature of the medium. It's not a lone frequency, it's a detected pattern, and therefore not real.

It's strange because.... at any given point in space, there's this pattern of EM fluctuation... and it's a superimposed results of fluctionations everywhere, and it's interpreted by achieving resonance at a particular frequency. I just think it's strange because there's all these different frequencies present, like a sound wave.

It's not a location, it's a energy fluctuation rate.

Reminds me of what I say about DRM... wait til the day when you buy a pair of shoes and license them for walking, and have to buy an additional license to run in them. If they detect that you're running, they'll self-destruct or play an irritating noise, haha.

It's strange, because it's a behavior... not a location. It's like telling people they can walk at speeds up to 1.6MPH, and above 4mph, but they'll be fined if they walk between 1.6 and 4mph, lol.

@Chris S. atac.com.tw/index.php/product/8-video/115-dvnet (Send computer display over wifi/ethernet to box... with DVI output.

You can vibrate at this frequency... but not this one

It's also funny that certain frequencies in the electro-mag spectrum are reserved, lol.

That's an awesome question. I hate that large-range communication infrastructure is controlled by large corporations. Is there a technology that could it back in our hands?

Proliferation of guns have nothing to do with gun crime rates, except to create a theoretical maximum based on number of guns/ammo available, firing rate, population density, shooter mobility, number of shooters, number of potential victims, etc.

There are more atoms per cubic millimeter in a neutron star than there are in our sun.

I'd never turn off relational integrity, especially on a system like this that's constantly adding/removing stuff.

~13 million votes, nice.

Yeah, I saw the schema, but was interested in how the tables are related.

Nice. I love SQL Server 2008 :)

@Zypher and @TiZon: Do you know where I could find a database diagram or what database engine these sites are based on so I could (possibly) extract the relations myself?

Scaling and architecture are codependent. Some architectures scale well, and depending on your scaling needs, that can dictate your architecture.

@Chris S: It's worked out great so far. For example, I've auto-generated MSIL Regex constraint functions to the SQL CLR, and it's capable of validating 1.5 million rows of nvarchar(4000) data in under 15 seconds! Depending on the scale, if it was any different that what I'm working with.. it may be completely inappropriate.