Consistent data

SynSQL spreads a table over many data brokers. This page lists what you can count on, and what you must organise yourself.

What SynSQL guarantees

• One row, one place. A row goes to one partition, decided by its hash key, and to every copy of that partition.
• Failed copies are set aside. If a write works on one copy and fails on another, the failed segment is marked out of date and is not read until it is repaired (see Replication).
• No half-deployed table is used. A table whose deployment is incomplete is locked.

What you must organise

• Joins and sub-queries are local. They only see the rows of the partition they run on. Give the tables of a join the same hash key and the same number of partitions (see A star model).
• Aggregates are per partition. count(), sum() and avg() return one row for each partition. Add the results in your program.
• There is no transaction across statements. Each statement is executed on its own. A statement that changes data is applied partition by partition: if the connection breaks in the middle, the client reports that it may or may not have been executed (exit code 3 in batch mode) and does not send it again.
• A unique key is local to a segment, unless it is the hash key.

Getting a consistent view

When you need data that does not change while you work (a backup, a series of selects that must agree), lock the table:

lock table orders;
-- ... work ...
unlock table orders;

A locked table refuses insert, update, delete and truncate. Reading stays possible. A backup (dump table) needs the table, or the whole cluster, to be locked.

lock cluster; does the same for the dictionary: while it is locked, nobody can create, alter or drop a table or a user.

After an incident

describe tells whether a table is locked. After a crash, repair table brings the copies back to the same content.