Questions tagged [mvcc]
The mvcc tag has no usage guidance.
14
questions
0
votes
0answers
23 views
Benchmarking MariaDB/InnoDB using TATP - Writers slow down readers?
I'm benchmarking MariaDB 10.4 with TATP. The InnoDB config file has the following parameters set:
[mysqld]
innodb_data_home_dir=/usr/local/mysql/data
innodb_undo_directory=/usr/local/mysql/data
...
-2
votes
2answers
55 views
How do writers not block reads in PostgreSql
Since readers do not block writers and writers do not block readers is what MVCC is all about, how does postgresql prevent writers who are in the process of committing their changes to blocks/pages ...
1
vote
1answer
185 views
InnoDB undo logs vs history list
I was reading about InnoDB undo logs, and in the process, the articles i was reading ended up confusing me on what undo logs are, and how they are different from the history list, or rather the same.
...
1
vote
1answer
116 views
Flags UPDATE optimization for MVCC
We have a table with 200k rows where we change some flags multiple times per day.
If i have understood correctly an UPDATE on postgres is a DELETE+INSERT on disk.
I'm wondering if this is highly ...
5
votes
1answer
3k views
Does SQL Server use multiversion concurrency control (MVCC)?
Reading an older overview of CouchDB published on the IBM site HERE, I was surprised to find the following:
Most modern databases have started to move from locking mechanisms to
MVCC, including ...
0
votes
1answer
570 views
Does Amazon Redshift support MVCC?
According to documentation, Amazon Redshift is derived from PostgreSQL 8.
PosgreSQL 8 supports Multiversion Concurrency Control (MVCC) which is in my understanding achieved on physical level by ...
0
votes
2answers
195 views
Postgres 40001 Exception Conditional Insert
I have 3 tables, A,B and C. A function actOnABC(i) which is a read only operation on the tables:
IF actOnABC(i) = 0 THEN
RAISE EXCEPTION 'Message';
ELSE
INSERT INTO TABLE B VALUES(...);
END ...
2
votes
0answers
463 views
Does H2 Database support pessimistic locking?
Does the H2 Database support pessimistic locking?
If so, how does its use interact with H2’s MVCC implementation?
I'm not looking for advice on whether or not to use pessimistic locking. I have a ...
3
votes
2answers
541 views
Is `count(*)` ever guaranteed to return the same result in a transaction at any transaction level?
If I create a table.
CREATE TABLE foo AS
SELECT CASE WHEN random() > 0.5 THEN x END AS x
FROM generate_series(1,10) AS x;
And, then I run the following in a transaction
BEGIN;
SELECT count(*)
...
2
votes
2answers
412 views
MVCC in PostgreSQL: visible tuple with xmin > current txid
I'm trying to better understand MVCC and am confused by the following (contrived) scenario. I was under the impression that for a row to be visible to a transaction, the transaction's id must be ...
6
votes
1answer
2k views
Why is ACCESS EXCLUSIVE LOCK necessary in PostgreSQL?
I know PostgreSQL provides multiple lock modes for concurrency control. When I read through their documents, I cannot see why the ACCESS EXCLUSIVE lock is necessary in any case. The only difference ...
0
votes
1answer
53 views
What is the impact of DROP SCHEMA on concurrent read only workloads in PostgreSQL?
I have an ELT process that deals with a pretty small amount of data. I would like to have 1 schema called prod with the results of the previous ELT run and 1 schema called dev where I will perform the ...
2
votes
1answer
3k views
InnoDB MVCC vs Locking
From what I understand you InnoDB supports
Row Level Locking
MVCC (Multiversion Concurrency Control)
Row Level Locking
Locking is for when multiple writers are trying to update the same
rows. ...
10
votes
2answers
749 views
How exactly the row visibility is determined?
In the simplest case, when we insert a new row into a table (and the transaction commits), it will be visible to all subsequent transactions. See xmax being 0 in this example:
CREATE TABLE vis (
...
