1

Citus does not support multi-statement transactions for data modification commands, making it impossible to use the LOCK command for preventing concurrent access during data migrations.

Is there any way to lock access to a table during an initial data migration?

jasonmp85
  • 6,749
  • 2
  • 25
  • 41
Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158

1 Answers1

1

Though Citus does prevent multi-statement transactions which contain modifications, this check apparently does not apply to the new COPY support. So if you are actually doing a COPY and not INSERT, UPDATE, or DELETE, you can use BEGIN and LOCK to get the single-write semantics you desire:

BEGIN;
LOCK target_table IN ACCESS EXCLUSIVE MODE;

COPY target_table FROM '/file/on/server';
\copy target_table from '~/local/file'

COMMIT;

If a concurrent session attempts an INSERT, UPDATE, or DELETE against this table, it will block until the above transaction block commits.

jasonmp85
  • 6,749
  • 2
  • 25
  • 41
  • When I try to do this I fall into [this](https://github.com/citusdata/citus/issues/543) problem – Eugen Konkov May 26 '16 at 06:28
  • That only a check and `COPY` still does not use transactions. So if I decide `ROLLBACK` at your example partially migrated data will not rolled back. Does I understand right? – Eugen Konkov May 26 '16 at 12:22
  • I'm not 100% sure, but I believe that description is accurate. The `COPY` itself can be atomic, but we don't (presently) propagate the surrounding transaction commands to the workers. – jasonmp85 May 31 '16 at 18:43