How to do Force serialization in AWS Redshift table by locking all tables?

AWS Redshift @ Freshers.in

You can force serialization by locking all tables in each session. The LOCK command blocks operations that would result in serializable isolation errors. The LOCK command restricts access to a database table. This command is only meaningful when it is run inside a transaction block. The LOCK command obtains a table-level lock in “ACCESS EXCLUSIVE” mode, waiting if necessary for any conflicting locks to be released. This means explicitly locking a table in this way causes reads and writes on the table to wait when they are attempted from other transactions or sessions. The explicit table lock created by one user temporarily prevents another user from selecting data from that table or loading data into it. The lock is released when the transaction that contains the LOCK command completes.

When using the LOCK command make sure that the bellow is done.

  1. Lock all tables affected by the transaction, including those affected by read-only SELECT statements inside the transaction.
  2. Lock tables in the same order regardless of the order that operations are performed in.
  3. Lock all tables at the beginning of the transaction, before performing any operations.

Syntax

LOCK [ TABLE ] table_name [, …]

Author: user

Leave a Reply