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?

0

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.