{question}
Why do I get this error message?
ERROR 1749 (HY000): Feature 'SELECT that reads from a reference table on a leaf inside a multi-statement transaction' is not supported by MemSQL Distributed.
What can I do to refrain from getting it?
{question}
{answer}
ERROR 1749 (HY000): Feature 'SELECT that reads from a reference table on a leaf inside a multi-statement transaction' is not supported by MemSQL Distributed.
Reason for the error:
Any modification on a reference table is propagated to the leaf nodes through replication after the transaction with the modification is committed. Before the transaction commits, only the master aggregator has the uncommitted data modifications available to read. If a later SELECT within the multi-statement transaction tries to read the modified reference table from a leaf, there's no way to perform the read correctly to see the uncommitted data modifications (since they are only available on the master aggregator). An error is generated in this case to prevent inconsistent reads.
Example of the error:
create table sharded_tab(a int);
create reference table reference_tab(a int, primary key(a));
insert into sharded_tab values (1);
begin;
insert into reference_tab values (1);
-- This select would hit error.
select count(*) from sharded_tab s join reference_tab r on s.a = r.a;
rollback;
Possible workarounds:
1. Changing the reference table into a sharded table. In general, reference tables work best on a small dataset of mostly static data, and it's not advised to do frequent modifications on them.
2. Sometimes, it is possible to rewrite the transaction to switch the order of statements so that the read happens before the reference table modifications.
3. Sometimes, it is possible to run the statements either as separate transactions or outside a multi-statement transaction.
{answer}