cockroachdb / cockroachdb/activerecord-cockroachdb-adapter

activerecord: can produce a dirty read

Open
#237 44 comments 1 reaction 0 assignees View on GitHub
Dominant language
Ruby
Stars
105
Forks
56
Avg merge
9h 5m
Merged PRs (30d)
3

Description

Refs: cockroachdb/cockroach#68363 (`ConcurrentTransactionTest#test_transaction_isolation__read_committed`, first hit Aug 12)

Setup a table:

```
CREATE TABLE "developers" ("id" bigserial primary key, "name" character varying, "first_name" character varying, "salary" int DEFAULT '70000', "firm_id" bigint, "mentor_id" int, "legacy_created_at" timestamp, "legacy_updated_at" timestamp, "legacy_created_on" timestamp, "legacy_updated_on" timestamp);
insert into developers (id) values (1);
```

In shell 1, type:

```
BEGIN;
SELECT "developers"."id", "developers"."name", "developers"."salary", "developers"."firm_id", "developers"."mentor_id", "developers"."legacy_created_at", "developers"."legacy_updated_at", "developers"."legacy_created_on", "developers"."legacy_updated_on" FROM "developers" WHERE "developers"."id" = 1 LIMIT 1;
UPDATE "developers" SET "salary" = 200000 WHERE "developers"."id" = 1;

```

In shell 2, run:

```
BEGIN;

SELECT "developers"."id", "developers"."name", "developers"."salary", "developers"."firm_id", "developers"."mentor_id", "developers"."legacy_created_at", "developers"."legacy_updated_at", "developers"."legacy_created_on", "developers"."legacy_updated_on" FROM "developers" WHERE "developers"."id" = 1 LIMIT 1;
```

Back to shell 1:

```
SELECT "developers"."id", "developers"."name", "developers"."salary", "developers"."firm_id", "developers"."mentor_id", "developers"."legacy_created_at", "developers"."legacy_updated_at", "developers"."legacy_created_on", "developers"."legacy_updated_on" FROM "developers" WHERE "developers"."id" = 1 LIMIT 1;

UPDATE "developers" SET "salary" = 80000 WHERE "developers"."id" = 1;

SELECT "developers"."id", "developers"."name", "developers"."salary", "developers"."firm_id", "developers"."mentor_id", "developers"."legacy_created_at", "developers"."legacy_updated_at", "developers"."legacy_created_on", "developers"."legacy_updated_on" FROM "developers" WHERE "developers"."id" = 1 LIMIT 1;

COMMIT;
```

Now in shell 2, we see a salary 200000, which is a dirty read (we never committed a salary of 200000):
```
SELECT "developers"."id", "developers"."name", "developers"."salary", "developers"."firm_id", "developers"."mentor_id", "developers"."legacy_created_at", "developers"."legacy_updated_at", "developers"."legacy_created_on", "developers"."legacy_updated_on" FROM "developers" WHERE "developers"."id" = 1 LIMIT 1;
;
id | name | salary | firm_id | mentor_id | legacy_created_at | legacy_updated_at | legacy_created_on | legacy_updated_on
-----+------+--------+---------+-----------+-------------------+-------------------+-------------------+--------------------
1 | NULL | 200000 | NULL | NULL | NULL | NULL | NULL | NULL
(1 row)
```

This can take a bit of time to reproduce (i've had to do this a few times each reproduction), but an external ORM, the contributor to the ORM and I have hit it multiple times, so it is not a fluke.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.