[YSQL] serializable isolation SELECT from table should conflict with DDL on table

[jaki]

Jira Link: DB-2293
Serializability is violated for certain cases where SELECTs on tables should have noticed conflicts with DDLs on the same tables.

Here is example 1:

session A	session B	output (PG)	output (YB)
CREATE TABLE a (i int)
CREATE TABLE b (i int)
BEGIN ISOLATION LEVEL SERIALIZABLE
SELECT * FROM a		0 rows	0 rows
	DROP TABLE a	(waits until commit)	(succeeds immediately)
	INSERT INTO b VALUES (1)
SELECT * FROM b		N/A	1 row
COMMIT

It is not serializable, yet it commits successfully for YB. The txn is not serializable because

• it could not have happened before INSERT INTO b since SELECT * FROM b returns the row
• it could not have happened after INSERT INTO b since SELECT * FROM a does not error when it should have if we executed it after the DROP TABLE a

Maybe we can lump this under transactional DDL issues, but there's a second more concerning example 2:

session A	session B	output (PG)	output (YB)
CREATE TABLE a (i int)
CREATE INDEX ON a (i ASC)
CREATE TABLE b (i int)
BEGIN ISOLATION LEVEL SERIALIZABLE
SELECT * FROM a ORDER BY i		0 rows	0 rows
	DROP INDEX a_i_idx	(waits until commit)	(succeeds immediately)
	INSERT INTO a VALUES (1)
	INSERT INTO b VALUES (1)
SELECT * FROM b		N/A	1 row
COMMIT

It is not serializable, yet it commits successfully for YB. The txn is not serializable because

• it could not have happened before INSERT INTO b since SELECT * FROM b returns the row
• it could not have happened after INSERT INTO b since SELECT * FROM a returns no rows when it should have returned the row from INSERT INTO a

These issues seem related to not locking tables properly. A similar example is easy to come up with for TRUNCATE. I believe we do write weak intents for the table when we SELECT from the table in the txn, but we might not be finding the conflicts properly. Maybe these issues are all just part of transactional DDL (#3109).

[jaki]

These issues have been fixed by causing catalog version mismatch on SELECT * FROM b. However, we will now go back to the old way when D9983 lands (and related D10666). This issue will likely resurface.

[tverona1]

Resolving this as fix: In both scenarios above, the drop table DDL aborts the DML transaction.