Race condition in AutoIncrementTracker can cause two transactions to get assigned the same auto-incrementing ID.

[nicktobey]

AUTO INCREMENT is supposed to be unaffected by transactions: not-yet-committed transactions can still advance the counter, and rolling back a transaction does not reset the counter. This allows two concurrent transactions to both insert into the same table with an AUTO INCREMENT column without causing a transaction conflict.

Unfortunately, it appears that in rare cases we can generate the same ID for two concurrent transactions, causing a conflict for whichever transaction commits second.

I was only able to get this to reproduce when the insertion was happening as the result of a trigger.

Steps to reproduce:

dolt sql <<SQL
CREATE TABLE test1 (
  pk int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  c0 int,
  index t1_c_index (c0)
);
CREATE TABLE test2 (
  pk int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  c0 int,
  index t2_c_index (c0)
);
CREATE TABLE timestamps (
  pk int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  t int
);

delimiter |
CREATE TRIGGER t1 AFTER INSERT ON test1
  FOR EACH ROW
  BEGIN
    INSERT INTO timestamps VALUES (0, 1);
  END|

CREATE TRIGGER t2 AFTER INSERT ON test2
  FOR EACH ROW
  BEGIN
    INSERT INTO timestamps VALUES (0, 2);
  END|
delimiter ;

CREATE VIEW bin AS SELECT 0 AS v UNION ALL SELECT 1;
CREATE VIEW sequence5bit AS SELECT b1.v + 2*b2.v + 4*b3.v + 8*b4.v + 16*b5.v AS v from bin b1, bin b2, bin b3, bin b4, bin b5;
CREATE VIEW sequence10bit AS SELECT b1.v + 32*b2.v AS v from sequence5bit b1, sequence5bit b2;
SQL

dolt sql-server &
server_pid = $!
dolt sql -q "INSERT INTO test1 (c0) select v from sequence10bit; SELECT * from timestamps; COMMIT;" &
dolt sql -q "INSERT INTO test2 (c0) select v from sequence10bit; SELECT * from timestamps; COMMIT;"
wait $!
kill $server_pid

There is a high likelihood that one of the transactions will fail. By comparing both transactions versions of the timestamps table, we can see that a small fraction of the primary keys appear in both tables. (Out of the 2048 inserts that this script makes, there are usually anywhere in the range of 1-7 duplicates.)