We have a case of slow transactions which could be explained by the following example:
-- create a table create table test(id int primary key); -- run this in the first SQL console: begin transaction; insert into test(id) values(0); -- run this in the second SQL console, will be blocked until first transaction resolves: begin transaction; select * from test where id = 0;
In the scenario above, the second transaction will be blocked until the first one resolves (commits or aborts). We have a similar case when we have a “read-only” transaction doing a read from “the end” of the table (table is ordered by timestamps and we read the tail of that table) and another transaction is doing a write at the end of that table (write transaction is slow for some unidentified reason – we are still investigating – could be explained by contention with other write transactions).
The way it manifests is that our “read-only” transactions (they consist of few SELECT statements) take too much time.
Is there any way to let those read-only transactions to proceed without waiting for write transactions to resolve?
Here are the options we tried:
- Marking transaction as “read only”, with the theory that read-only operations should not be blocked in a MVCC database – no effect. Also, a simple “select * from test” statement is also blocked.
- Snapshot isolation is no longer supported – this would have solved it as well, I think?
- Doing “AS OF SYSTEM TIME ‘-1 usec’”. Going back some amount of seconds (2 seconds on my laptop) solves the issue, but not clear which value should we use here. 2 seconds of “staleness” for these particular types of read-only transactions is acceptable for us, but something like 30 seconds is, perhaps, too much.
- Setting priority to HIGH for these “read-only” transactions seems to do the trick, but looks like a very crude hammer to use (and we only have 3 priorities, so if we ever would need to use them for something else, one priority would be “used” already…).
Any other suggestions?