0

With a database structure sort of like this:

create table objects {
  uuid id;
  string type;
}

create table object_properties {
  uuid id;
  uuid source_id; // the object which has this property
  string name; // the property name
  uuid value_id; // the property value object
}


// ...and tables for each primitive data type
create table string_properties {
  uuid id;
  uuid source_id; // the object which has this property
  string name; // the property name
  string value; // the property value string
}

where objects have properties (which can be linked to other objects)...

What happens when we create a transaction which inserts records into all 3 tables? What happens in terms of reads and writes of other processes if the transaction is in the SERIALIZABLE transaction level (CockroachDB and/or Postgres).

SERIALIZABLE isolation guarantees that even though transactions may execute in parallel, the result is the same as if they had executed one at a time, without any concurrency.

I don't quite see the practical implications of that.

Does it block reads until the table writes are COMMITed? What happens if there are multiple calls to such a transaction in parallel? What all happens during such an "all-table-spanning" sequence of writes transaction?

Lance Pollard
  • 221
  • 2
  • 14

1 Answers1

1

In regards to CockroachDB, there are some helpful docs on the transactional layer and the processes behind it here: Transaction Layer

When there are multiple tables, the same transaction processes apply. During a transaction, all tables with a write intent will block other transactions with reads on the rows that are being affected. This is done through locks for transaction writes and maintaining a transaction record for the current state. Under the hood, these are stored in an internal table called crdb_internal.cluster_locks.

In the case of an "all-table-spanning" transaction, the same principles apply. Rows across N tables are in an uncommitted state until the entire transaction is completed. This prevents reads of stale data across the object, object_properties, and string_properties tables you named if you were to read from them early.

For a simpler example, let's say you had two tables that both had writes committed to them during a transaction.

root@localhost:26257/defaultdb> create table t(x int primary key, y int);
CREATE TABLE

Time: 129ms total (execution 127ms / network 1ms)


root@localhost:26257/defaultdb> create table s(x int primary key, y int);
CREATE TABLE


Time: 105ms total (execution 105ms / network 0ms)


root@localhost:26257/defaultdb> insert into t values (1, 1), (2, 2), (3, 3);
INSERT 3


Time: 123ms total (execution 123ms / network 0ms)


root@localhost:26257/defaultdb> insert into s values (1, 1), (2, 2), (3, 3);
INSERT 3


Time: 87ms total (execution 87ms / network 0ms)


root@localhost:26257/defaultdb> BEGIN;
BEGIN


root@localhost:26257/defaultdb  OPEN> insert into t values (4, 4);
INSERT 1


Time: 1ms total (execution 1ms / network 0ms)


root@localhost:26257/defaultdb  OPEN> insert into s values (4, 4);
INSERT 1

If you tried to read the the rows that were not being inserted on either table, the statement would execute successfully. If you were reading the rows that were locked (x = 4 for both tables t and s), the query wouldn't return the rows until the original transaction is committed.

root@localhost:26257/defaultdb> select y from t where x = 3;
  y
-----
  3
(1 row)

Time: 2ms total (execution 1ms / network 1ms)


root@localhost:26257/defaultdb> select * from t;


-- hanging

root@localhost:26257/defaultdb> select y from s where x = 3;
  y
-----
  3
(1 row)


Time: 1ms total (execution 1ms / network 0ms)

root@localhost:26257/defaultdb> select * from s;

-- hanging
Lasse Nordahl
  • 26
  • 2