github.com/mithrandie/csvq@v1.18.1/docs/_posts/2006-01-02-transaction.md (about)

     1  ---
     2  layout: default
     3  title: Transaction Management - Reference Manual - csvq
     4  category: reference
     5  ---
     6  
     7  # Transaction Management
     8  
     9  A transaction is a single logical unit of work.
    10  A transaction is an atomic unit, so you get the result of either all the changes are performed or none of them is performed.
    11  
    12  * [Usage Flow in a Procedure](#usage_flow_in_prodecure)
    13  * [Usage Flow in the Interactive Shell](#usage_flow_in_shell)
    14  * [File Locking](#file_locking)
    15  * [Commit Statement](#commit)
    16  * [Rollback Statement](#rollback)
    17  
    18  ## Usage Flow in a Procedure
    19  {: #usage_flow_in_prodecure}
    20  
    21  ### Start Transaction
    22  
    23  A transaction is started automatically when the procedure execution is started, or after a commit or rollback statement is executed.
    24  
    25  ### Terminate Transaction
    26  
    27  A transaction is terminated when a commit or rollback statement is executed.
    28  
    29  When the procedure is normally terminated, then commit all the changes automatically.
    30  
    31  When some errors occurred in the procedure, then roll all the changes back automatically.
    32  
    33  When the procedure is exited by [EXIT statement]({{ '/reference/control-flow.html#exit' | relative_url }}), then roll all of the changes back automatically.
    34  
    35  ## Usage Flow in the Interactive Shell
    36  {: #usage_flow_in_shell}
    37  
    38  ### Start Transaction
    39  
    40  A transaction is started automatically when the interactive shell is launched, or after a commit or rollback statement is executed.
    41  
    42  ### Terminate Transaction
    43  
    44  A transaction is terminated when a commit or rollback statement is executed.
    45  
    46  When the interactive shell is terminated, then roll all the changes back automatically.
    47  
    48  
    49  ## File Locking
    50  {: #file_locking}
    51  
    52  In a transaction, created files and updated files are locked by using lock files, so these files are protected from other csvq processes.
    53  
    54  This locking does not guarantee that these files are protected from other applications.
    55  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](https://github.com/mithrandie/go-file).
    56  
    57  SELECT queries use shared locks. INSERT, UPDATE, DELETE, CREATE and ALTER TABLE queries use exclusive locks to update files.
    58  Shared locks are unlocked immediately after reading, and exclusive locks remain until the termination of the transaction.
    59  
    60  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.
    61  However, as an exception, when trying to update a file that has been loaded by a SELECT query, the file will be reloaded.
    62  In that case, there is a probability the data is changed in tha same transaction.
    63  You can use [FOR UPDATE]({{ '/reference/select-query.html' | relative_url }}) keywords in SELECT queries to use exclusive locks and prevent the probability. 
    64  
    65  ### Recover file locking
    66  
    67  Program panics and unterminated transactions remain lock files.
    68  In that case, you must manually remove following hidden files created by csvq.
    69  
    70  - ._FILE_NAME_.[0-9a-zA-Z]{12}.rlock 
    71  - ._FILE_NAME_.lock 
    72  - ._FILE_NAME_.temp
    73  
    74  
    75  ## Commit Statement
    76  {: #commit}
    77  
    78  A commit statement writes all the changes to files.
    79  
    80  ```sql
    81  COMMIT;
    82  ```
    83  
    84  ## Rollback Statement
    85  {: #rollback}
    86  
    87  A rollback statement discards all the changes.
    88  
    89  ```sql
    90  ROLLBACK;
    91  ```
    92