We have read only scale out with one root node and two children. I'me inserting rows with following statement into table customer.
and I discovered unexpected behavior. If I'm inserting rows in existing table customer they are not available before commit at child ROSO node(from other transaction). So I'm getting behavior like snapshot read-only isolation level. After commit data are persisted and visible.
I created copy of customer table named customer1 and If I'm inserting rows in table customer1 they are available before commit at child ROSO node(from other transaction). So I'm getting behavior like dirty read isolation level.
Maybe I'm missing something. What could cause this behavior ?
asked 07 Nov '16, 03:55
I got it ! :)
That DB is migrated to SA16 from SA11 before there has been replications and existing table is in publications. If table IS in publication, rows are not sent to children and I'm getting like 'snapshot read-only isolation level'. If table IS NOT in publication rows are sent and visible(dirty reads as expected). For now I considered it as bug. I don't see reasons why there should be relation between publication and read only scale out.
Should I tried to open bug ticket for it ?
answered 07 Nov '16, 07:42
Did you check the UncommitOp connection property of your insert session to verify that the changes really were pending?
What's the isolation level of your ROSO session?
answered 07 Nov '16, 06:44