Questions tagged [mvcc]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
88 views

Why readers (SELECT) can be blocked by writers (DML) in SQL Server?

Could anyone explain to me why in SQL Server SELECT can be blocked by UPDATE statement? I thought, that MVCC (implemented in SQL Server by copying modified data to the tempdb) prevent readers to be ...
user avatar
1 vote
1 answer
52 views

Impact of Adding a New Column on Table Bloat in PostgreSQL

I'm working with PostgreSQL and have a concern regarding table bloat when adding a new column to an existing table. I understand that PostgreSQL uses Multi-Version Concurrency Control (MVCC) and ...
pramod's user avatar
  • 25
1 vote
2 answers
168 views

Separate small column updated a lot from large jsonb columns to optimize performance?

A table has the following structure: a: id b: large jsonb c: large jsonb d: timestamp Looking at stats in 2 weeks, see many updates: 90k requests updates the d field alone (total time: 25min ...
Slim's user avatar
  • 237
0 votes
1 answer
176 views

How does MVCC work under read committed isolation level in Postgres

Let's say I have two transactions A and B. Transaction A starts first. then transaction B starts, inserts some data, and commits. So from transaction A (not committed yet), I can see data were ...
Quốc Khánh Bùi's user avatar
0 votes
0 answers
271 views

Unknown column od.id_warehouse in where clause

SELECT LEFT(invoice_date, 10) as fix_date, COUNT(*) as countOrders, SUM((SELECT SUM(od.product_quantity) FROM ps_order_detail od WHERE o.id_order = od.id_order)) as countProducts, ...
Ahmed Ibrahim's user avatar
0 votes
1 answer
441 views

How to configure InnoDB/MariaDB to not fill ibdata1 with (rollback/undo?) data that is useless in an in-memory database? [closed]

I'm trying to run a tiny (~100-200 MB) database in-memory, but ibdata1 grows out of control. Situation: MariaDB is running on a resource constrained HW (Raspberry, 1GB RAM), storing non-critical ...
lmagyar's user avatar
  • 111
15 votes
1 answer
7k views

What is "special" about PostgreSQL update vs delete+insert

My understanding is that an update locks a tuple, marks it as deleted, and then adds a new tuple. In other words, update = delete + insert. Or so I had believe. But it appears there something ...
Paul Draper's user avatar
1 vote
1 answer
337 views

Are snapshots generated for each sub query inside a Postgres READ_COMMITTED transaction?

From what I understand - in a READ_COMMITTED Postgres transaction “the transaction obtains a snapshot whenever an SQL command is executed” source. Does this mean a snapshot will be obtained for each ...
Jordan's user avatar
  • 141
3 votes
0 answers
311 views

Is there a reliable way to determine the order in which Postgres transactions were committed without logical decoding?

Is there a reliable way to determine the serial order in which Postgres transactions were committed without logical decoding? From what I understand pg_xact_commit_timestamp can’t necessarily be ...
Jordan's user avatar
  • 141
1 vote
1 answer
151 views

Is it possible to have xid before and after wrap around with the same timestamp?

I want to create sync API in which users can get the changes since their last sync request, the API exposes cursor-based pagination (sync token) A possible cursor may be to use updatedAt column along ...
Michael's user avatar
  • 123
0 votes
0 answers
53 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 ...
Aarati K's user avatar
0 votes
2 answers
2k 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 ...
paiakshay998's user avatar
2 votes
1 answer
1k 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. ...
giogio's user avatar
  • 23
2 votes
1 answer
183 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 ...
Luca Looz's user avatar
8 votes
1 answer
8k 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 ...
Zach Smith's user avatar
  • 2,360
0 votes
1 answer
1k 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 ...
Mike's user avatar
  • 111
-1 votes
2 answers
1k 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 ...
cplusplusrat's user avatar
2 votes
0 answers
860 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 ...
Basil Bourque's user avatar
4 votes
2 answers
2k 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(*) ...
Evan Carroll's user avatar
  • 62.7k
3 votes
2 answers
1k 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 ...
Dave Stibrany's user avatar
11 votes
1 answer
10k 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 ...
Matt Li's user avatar
  • 113
1 vote
1 answer
392 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 ...
foobar0100's user avatar
2 votes
1 answer
4k 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. ...
TheOddGuy's user avatar
  • 183
10 votes
2 answers
1k 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 ( ...
András Váczi's user avatar