Concurrent transactions on the same table are aborted

[poojanilangekar]

When we run concurrent transactions on the same table, with one transaction updating it and the other reading from it, the transaction performing the read gets aborted.

Here are the steps to reproduce the bug:

default_database=# create table foo (id int primary key, year int);
CREATE TABLE
default_database=# insert into foo values(1,1000);
INSERT 0 1
default_database=# insert into foo values(2,2000);
INSERT 0 1
default_database=# insert into foo values(3,3000);
INSERT 0 1
default_database=# select * from foo;
 id | year 
----+------
  1 | 1000
  3 | 3000
  2 | 2000
(3 rows)

Now on one connection carry out the following steps:

default_database=# begin;
BEGIN
default_database=# update foo set year = 200 where id = 2;
UPDATE 1
default_database=# select * from foo;
 id | year 
----+------
  1 | 1000
  3 | 3000
  2 |  200
(3 rows)

On another connection, try any queries on the same table:

begin;
BEGIN
default_database=# select * from foo;
SELECT 0
default_database=# select * from foo; // Attempting a select again. 
current transaction is aborted, commands ignored until end of transaction block

[gvos94]

This is actually the expected behaviour. Since we don't do speculative reads, MVTO aborts a transaction that attempts to read or update a version whose write lock is held by another transaction. So unless a multi-statement Txn is explicitly made read-only, the Txn performing the read will get aborted in the presence of concurrent updates.

[tomasic]

Does this fact explain our low TPC performance?

I guess we should augment the TPC numbers with the abort rate - or has someone looked at this? If its high, that would be the place to look instead of looking at cycles spent in each function.

On Wed, Jun 13, 2018 at 11:57 AM Gandeevan Raghuraman < notifications@github.com> wrote:

This is actually the expected behaviour. Since we don't do speculative reads, MVTO aborts a transaction that attempts to read or update a version whose write lock is held by another transaction. So unless a multi-statement Txn is explicitly made read-only, the Txn performing the read will get aborted in the presence of concurrent updates.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/cmu-db/peloton/issues/1314#issuecomment-397010603, or mute the thread https://github.com/notifications/unsubscribe-auth/ABS8HLsxOby1FMTTx1l5OFZejgAa60_1ks5t8URxgaJpZM4TcaDp .

-- Anthony Tomasic Language Technologies Institute Carnegie Mellon University http://www.tiramisutransit.com http://mcds.cs.cmu.edu http://mcds.cs.cmu.edu http://www.cs.cmu.edu/~tomasic