What is the question here? Row level locks get escalated to Page or Table level locks depending on how many rows need to be locked. So it is possible for unrelated rows to be locked, under certain circumstances. — J.D.7 hours ago
@J.D. Row level locks never escalate to a page lock. If you'd written your answer in an answer, you could have edited it.
The question here was in the sentence ending in the question mark and you answered it despite your claim you didn't know what it was. This lock escalation to unrelated rows is unfortunate, but thanks for the answer. If you post that as an answer, I'll accept it. — John5 hours ago
And the OP wouldn't need to write all that.
And I wouldn't have anything to clean up.
For the absolute love of small fluffy bunnies, please write answers as answers. Even if you're not sure it's the answer. Use comments for asking for clarifications by all means. Do not attach an answer to such comments. I am pleading with you now.
@a_horse_with_no_name Ок, если по простому, мне нужно понимать, какой именно запрос потребляет память. Т.е. в момент выполнения тяжелого запроса PLE упадёт или сильно просядет. Собственно для такого анализа мне и нужен PLE. — aqis16 mins ago
Is that supposed to be more efficient in some way? There can't actually be ties for that ORDER BY, right?
In that particular case, there can be ties
OK, read whole article. That design should not allow duplicate _ValidFromDateTime for a particular customer because that looks like a validity period - where you wouldn't want them to overlap. So that query is accounting for something that probablyshouldn't be allowed in the first place.
I cannot recall ever having a case where I wanted to use WITH TIES, and certainly not in that kind of scenario - I've always found something to disambiguate the rows or decide it was a sign of a larger problem.
It'd be more efficient at this point to start shooting people using that instead of trying to explain everything
This Microsoft example code appears to indicate that writers won't block other writers under SNAPSHOT isolation, if implemented correctly. Are you certain every connection is setting the transaction isolation level to snapshot? — Hannah Vernon ♦3 hours ago
Indeed, snapshot isolation produces write conflict errors, not blocking
Neither take locks when reading. SI detects write conflicts
It's very hard to summarise all the behaviours succinctly.
RCSI is a versioning-based implementation of read committed.
SI is a new isolation level.
> When a data row meets the update criteria, the snapshot transaction verifies that the data row has not been modified by a concurrent transaction that committed after the snapshot transaction began. If the data row has been modified outside of the snapshot transaction, an update conflict occurs and the snapshot transaction is terminated. The update conflict is handled by the SQL Server Database Engine and there is no way to disable the update conflict detection.
In case you weren't confused enough, SI transactions do still take exclusive locks:
> Transactions running under snapshot isolation take an optimistic approach to data modification by acquiring locks on data before performing the modification only to enforce constraints. Otherwise, locks are not acquired on data until the data is to be modified.
The other important difference being RCSI generally uses a snapshot as of the start of each statement; SI uses the same snapshot as of the start of the transaction
I say "generally" because non-inline functions start a new snapshot too
So, a single statement under RCSI can access data from n different 'points in time', if the statement contains n-1 functions
Anyway, Erik tells me RCSI and SI are much simpler than the other isolation levels
So how would be possible for a writer to block another writer in SI? From the docs, I'd assume that the writer that tries to write in pararllel with another one, will either succeed or get a write conflict.
> However, in practice, when one person halts a transaction while debugging, all other DB users attempting to access the same rows are blocked despite using snapshot isolation level.
I don't much like the example because it sort of implies you have to read the row before later trying to modify it to get a write conflict error
You just need to have started the SI transaction by reading something in the database to set the reference row version. Any attempt to modify a row with a later version than that will produce a write conflict error.
In other words, just issuing BEGIN TRAN doesn't set the reference point. You have to do something.
Is there any analog of MS SQL Server PLE (Page Life Expectancy) metric in Postgres?
I know only about cache hit ratio, which request look like:
select sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;
However, I need to understand which request is consuming ...
@mustaccio Ironically, it was your comment that convinced me it needed clarification
This is a great example of an X-Y Problem -- instead of asking how to achieve your goal you suggest an incorrect approach to achieving it and ask why it doesn't work. So, what is it that you are trying to achieve? — mustaccio5 hours ago
@PaulWhite so there are three start times for a tran. The BEGIN TRAN time, the first statement (SI start point) and the first write statement (logging start). ...
Well, versions aren't times, but GETDATE() is a runtime constant set when the containing statement starts. It would be different for each statement (non-inline functions aside)
OTOH if you have multiple GETDATE()s in a single query you can get different constants
> During World War II, the United States began to provide significant military supplies and other assistance to the Allies in September 1940, even though the United States did not enter the war until December 1941.
Other places believe WW2 started on 1 September 1939
I like the scalar subquery as it's obvious to both humans and the Query Optimizer that the subquery must return at most one row. — David Browne - Microsoft2 hours ago
@JoshDarnell Ah I remember that question from this morning. Nice answer. 👍 Not sure why the scalar subquery throws off the engine so much other than the normal reasoning of the more complexWHERE clause being harder for it to estimate accurately, I guess.
Is there any analog of MS SQL Server PLE (Page Life Expectancy) metric in Postgres?
I know only about cache hit ratio, which request look like:
select sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;
However, I need to understand which request is consuming ...
