Empty table, serializable transactions issue
Hey all,
I ran into an issue with Vertica when testing a repeated transaction that I wrote. The transaction is marked with isolation level serializable. In the transaction is an UPDATE, then an INSERT when the row doesn't exist. The idea is that I'm trying to simulate an upsert.
START TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE;
UPDATE testTable SET pivot = <number>,value = value + 1 WHERE (pivot = <number>);
INSERT INTO testTable (pivot,value) SELECT <number>,1 WHERE NOT EXISTS (SELECT pivot,value FROM testTable WHERE (pivot = <number>));
COMMIT TRANSACTION;
To test, I spin up 10 threads, each performing 20 upserts. Each thread performs 2 of these upserts on each row all at the same time. At the end of the test, the 'value' of each row should be 20.
When the testTable has a row in it already, this test works as expected. Each row has a value of 20. When the testTable doesn't have anything in it though, the first set of transactions from each of the 10 threads (where pivot = 1) seem to not run correctly. They all seem to do the insert operation, making the value of the row at the end of the test 11 instead of 20.
My understanding is that marking the transaction as serializable will give the effect of each transaction running in serial, as they retrieve locks on all read and write operations. Is there a bug here, or is there a potential issue with the way I'm creating tables?