18
votes
Accepted
Does SQL Server use multiversion concurrency control (MVCC)?
Does SQL Server really implement MVCC anywhere
Yes, since SQL Server 2005.
The SQL Server terminology is "row-versioning isolation levels". See the product documentation tree starting at Locking and ...
- 90.3k
17
votes
Accepted
What is "special" about PostgreSQL update vs delete+insert
Yes, there is a difference between UPDATE and DELETE + INSERT.
Let's use the pageinspect extension to look at the tuples and the tuple headers.
If you want to repeat my experiment, you have to drop ...
- 56.4k
16
votes
Accepted
Why is ACCESS EXCLUSIVE LOCK necessary in PostgreSQL?
If you have a look at the documentation, you see the list of operations that obtains this type of lock:
Acquired by the DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL,
and REFRESH ...
- 31.6k
5
votes
Is `count(*)` ever guaranteed to return the same result in a transaction at any transaction level?
My ten cents, and based on the PostgreSQL documentation:
Assumption: your own transaction does not change any relevant value from table foo.
Having the same count(*) in both your queries means:
You ...
- 13.6k
5
votes
Accepted
How do writers not block reads in PostgreSql
That's the whole idea of MVCC - Multi-Version-Concurrency-Control.
Whenever data is being modified while there are other active transactions reading it, or have read it previously (depending on the ...
- 4,098
4
votes
Why readers (SELECT) can be blocked by writers (DML) in SQL Server?
By default SQL Server uses the READ COMMITTED isolation level. How this behaves depends on if you have the database setting READ_COMMITTED_SNAPSHOT enabled or not. From the documentation:
READ ...
- 293
4
votes
How do writers not block reads in PostgreSql
Now what I find difficult is, say while the actual commit process
occurs for T1, say T2 reads (while the write by T1 has started but not
ended). How is this case handled?
It doesn't have to be ...
- 32.3k
3
votes
Separate small column updated a lot from large jsonb columns to optimize performance?
Like Laurenz advised, column values that are stored out of line in a TOAST table and are not touched in the UPDATE can stay as they are, saving work and storage. (Old and new row version of the main ...
- 182k
3
votes
Accepted
Separate small column updated a lot from large jsonb columns to optimize performance?
No, that is not necessary. The large JSON values seem to be stored out of line in a TOAST table. Then the update of d only has to write a new row to the main table, but the TOAST values stay the same.
...
- 56.4k
3
votes
Accepted
How does MVCC work under read committed isolation level in Postgres
why it can see data was committed by the transaction whose XID is 742
Because that is what READ COMMITTED means. In addition to the transaction having a txid (if it has ever needed one), each ...
- 41.3k
2
votes
InnoDB undo logs vs history list
I'll take a swing at this :) My colleagues and I are pouring over how to make a succinct but accurate answer for this;
on what undo logs are, and how they are different from the history list,
Undo ...
- 1,031
2
votes
How to configure InnoDB/MariaDB to not fill ibdata1 with (rollback/undo?) data that is useless in an in-memory database?
Doing smaller transactions and keeping connections not open (Open Transactions) for long time will probably do the job.
And:
The undo log is usually part of the physical system tablespace, but from ...
- 59
2
votes
Is it possible to have xid before and after wrap around with the same timestamp?
Assuming updatedAt stores the transaction timestamp as reported by now() or CURRENT_TIMESTAMP. See:
Difference between now() and current_timestamp
Then yes, xmin behaves like a simple sequence, ...
- 182k
1
vote
Why MySQL repeatable read doesn't raise serialization error
This is due to the way InnoDB implements MVCC.
https://dev.mysql.com/doc/refman/8.4/en/innodb-locking-reads.html says:
(Old versions of a record cannot be locked; they are reconstructed by applying ...
- 16k
1
vote
Accepted
Impact of Adding a New Column on Table Bloat in PostgreSQL
If the new values are a constant literal, you could add the column already populated without creating any dead tuples:
BEGIN;
ALTER TABLE example_table ADD COLUMN new_column VARCHAR(255) default 'some ...
- 41.3k
1
vote
Are snapshots generated for each sub query inside a Postgres READ_COMMITTED transaction?
The snapshot 'created' is per statement regardless of whether that statement contains subqueries or not. From the documentation:
... This level is different from Read Committed in that a query in a ...
- 46
1
vote
Accepted
MVCC in PostgreSQL: visible tuple with xmin > current txid
This depends on the isolation level. The default level for postgresql is "Read Committed" at which level this can happen. In postgresql it won't happen at "Repeatable Read" or "Serializable".
This ...
- 1,008
1
vote
Does Amazon Redshift support MVCC?
I found an answer in Redshift documentation soon after asking the question. The chapter is called Managing Concurrent Write Operations:
Amazon Redshift allows tables to be read while they are being ...
- 111
1
vote
Postgres 40001 Exception Conditional Insert
Please see PostgreSQL Documentation, Chapter 13. Concurrency Control, 13.2. Transaction Isolation for both Repeatable Read and Serializable isolation level descriptions:
Applications using this ...
- 529
1
vote
Is `count(*)` ever guaranteed to return the same result in a transaction at any transaction level?
guaranteed to remain the same "in the transaction"
That has two meanings, and that's the problem. You have what's happening outside of the transaction and what's happening inside with the "snapshot". ...
- 64.7k
Only top scored, non community-wiki answers of a minimum length are eligible