SQL-like query language for csv

Transaction Management

A transaction is a single logical unit of work. A transaction is an atomic unit, so you get the result of either all the changes are performed or none of them is performed.

Usage Flow in a Procedure

Start Transaction

A transaction is started automatically when the procedure execution is started, or after a commit or rollback statement is executed.

Terminate Transaction

A transaction is terminated when a commit or rollback statement is executed.

When the procedure is normally terminated, then commit all the changes automatically.

When some errors occurred in the procedure, then roll all the changes back automatically.

When the procedure is exited by EXIT statement, then roll all of the changes back automatically.

Usage Flow in the Interactive Shell

Start Transaction

A transaction is started automatically when the interactive shell is launched, or after a commit or rollback statement is executed.

Terminate Transaction

A transaction is terminated when a commit or rollback statement is executed.

When the interactive shell is terminated, then roll all the changes back automatically.

File Locking

In a transaction, created files and updated files are locked by using lock files, so these files are protected from other csvq processes.

This locking does not guarantee that these files are protected from other applications. System-provided file locking to protect them from other applications are used only on the systems supported by the package github.com/mithrandie/go-file.

SELECT queries use shared locks. INSERT, UPDATE, DELETE, CREATE and ALTER TABLE queries use exclusive locks to update files. Shared locks are unlocked immediately after reading, and exclusive locks remain until the termination of the transaction.

Once you load files, that data is cached until the termination of the transaction, so in a transaction, that data is basically unaffected by the other transactions. However, as an exception, when trying to update a file that has been loaded by a SELECT query, the file will be reloaded. In that case, there is a probability the data is changed in tha same transaction. You can use FOR UPDATE keywords in SELECT queries to use exclusive locks and prevent the probability.

Recover file locking

Program panics and unterminated transactions remain lock files. In that case, you must manually remove following hidden files created by csvq.

Commit Statement

A commit statement writes all the changes to files.

COMMIT;

Rollback Statement

A rollback statement discards all the changes.

ROLLBACK;