# Transaction Overlap Strategies
Added a `transactions` table `key` and `timestamp` columns. Every tuple write will "touch" a per-permission-system entry in this table.
What's the best way to "touch"?
# Efficacy
## Select
```go
if err := tx.QueryRow(ctx, "SELECT key FROM transactions WHERE key = $1", "key").Scan(nil); err == pgx.ErrNoRows {
if _, err := tx.Exec(ctx, "insert into transactions(key) values ($1) on conflict (key) do nothing", "key"); err != nil {
return err
}
} else if err != nil {
return err
}
```
Verdict: [**Vulnerable**](https://github.com/authzed/spicedb/runs/3640727381?check_suite_focus=true)
## Select For Update
```go
if err := tx.QueryRow(ctx, "SELECT key FROM transactions WHERE key = $1 FOR UPDATE", "key").Scan(nil); err == pgx.ErrNoRows {
if _, err := tx.Exec(ctx, "insert into transactions(key) values ($1) on conflict (key) do nothing", "key"); err != nil {
return err
}
} else if err != nil {
return err
}
```
Verdict: [**Vulnerable**](https://github.com/authzed/spicedb/runs/3640912777?check_suite_focus=true)
## Upsert
```go
if _, err := tx.Exec(ctx, "UPSERT INTO transactions(key) VALUES ($1);", "key"); err != nil {
return err
}
```
Verdict: **[Vulnerable](https://github.com/authzed/spicedb/runs/3640786675?check_suite_focus=true)**
## Insert (on conflict do nothing)
```go
if _, err := tx.Exec(ctx, "insert into transactions(key) values ($1) on conflict (key) do nothing", "key"); err != nil {
return err
}
```
Verdict: [**Vulnerable**](https://github.com/authzed/spicedb/runs/3640818572?check_suite_focus=true)
## Insert (on conflict update timestamp)
```go
if _, err := tx.Exec(ctx, "insert into transactions(key) values ($1) on conflict (key) DO UPDATE SET timestamp = now()", "key"); err != nil {
return err
}
```
Verdict: [**Works**](https://github.com/authzed/spicedb/runs/3640869337?check_suite_focus=true)
## Insert (on conflict update pk)
```go
if _, err := tx.Exec(ctx, "insert into transactions(key) values ($1) on conflict (key) DO UPDATE SET key = $2", "key", "key"); err != nil {
return err
}
```
Verdict: [**Works**](https://github.com/authzed/spicedb/runs/3641030329?check_suite_focus=true)
# Performance
The benchmarks are mediocre, and running locally (arm). There is a definite hit to write performance, but they're "only" about 2-3 stddevs above the baseline mean.
## Baseline (no protection)
```
BenchmarkBatchWrites-8 61 18260553 ns/op
BenchmarkConflictingTupleWrites-8 1 11844384250 ns/op
---
BenchmarkBatchWrites-8 26 95319970 ns/op
BenchmarkConflictingTupleWrites-8 1 14327687583 ns/op
---
BenchmarkBatchWrites-8 55 20028459 ns/op
BenchmarkConflictingTupleWrites-8 1 13492916083 ns/op
```
```
BatchWrites mean: 44536327 ns/op
BatchWrites StdDev: 43988807 ns/op
ConflictingTupleWrites mean: 13221662638 ns/op
ConflictingTupleWrites StdDev: 1263678236 ns/op
```
## Insert (on conflict update timestamp)
~1-2.4 stdv batch
~2.5-3.5 stdev conflict
```go
if _, err := tx.Exec(ctx, "insert into transactions(key) values ($1) on conflict (key) DO UPDATE SET timestamp = now()", "key"); err != nil {
return err
}
```
```
BenchmarkBatchWrites-8 12 85609788 ns/op
BenchmarkConflictingTupleWrites-8 1 17377917292 ns/op
---
BenchmarkBatchWrites-8 18 145425956 ns/op
BenchmarkConflictingTupleWrites-8 1 16600091750 ns/op
```
## Insert (on conflict update pk)
~22 stddv batch write (!!)
~1 stddev conflict
```go
if _, err := tx.Exec(ctx, "insert into transactions(key) values ($1) on conflict (key) DO UPDATE SET key = $2", "key", "key"); err != nil {
return err
}
```
```
BenchmarkBatchWrites-8 1 1147922000 ns/op
BenchmarkConflictingTupleWrites-8 1 14411499042 ns/op
---
BenchmarkBatchWrites-8 1 1032066083 ns/op
BenchmarkConflictingTupleWrites-8 1 15116998958 ns/op
---
BenchmarkBatchWrites-8 1 1299405458 ns/op
BenchmarkConflictingTupleWrites-8 1 16752419291 ns/op
```
### Implementation
Options were hardcoded and switched before each run, nothing special:
```go
strategy := "inserttimestamp"
switch strategy {
case "none":
return nil
case "insert":
if _, err := tx.Exec(ctx, "insert into transactions(key) values ($1) on conflict (key) do nothing", "key"); err != nil {
return err
}
case "upsert":
if _, err := tx.Exec(ctx, "UPSERT INTO transactions(key) VALUES ($1);", "key"); err != nil {
return err
}
case "inserttimestamp":
// works
if _, err := tx.Exec(ctx, "insert into transactions(key) values ($1) on conflict (key) DO UPDATE SET timestamp = now()", "key"); err != nil {
return err
}
case "insertpk":
// works
if _, err := tx.Exec(ctx, "insert into transactions(key) values ($1) on conflict (key) DO UPDATE SET key = 'key'", "key"); err != nil {
return err
}
case "selectforupdate":
if err := tx.QueryRow(ctx, "SELECT key FROM transactions WHERE key = $1 FOR UPDATE", "key").Scan(nil); err == pgx.ErrNoRows {
if _, err := tx.Exec(ctx, "insert into transactions(key) values ($1) on conflict (key) do nothing", "key"); err != nil {
return err
}
} else if err != nil {
return err
}
case "select":
if err := tx.QueryRow(ctx, "SELECT key FROM transactions WHERE key = $1", "key").Scan(nil); err == pgx.ErrNoRows {
if _, err := tx.Exec(ctx, "insert into transactions(key) values ($1) on conflict (key) do nothing", "key"); err != nil {
return err
}
} else if err != nil {
return err
}
}
```
Google Drive
Gist
Clipboard
Sign in with Wallet
