Understanding Redshift Table Lock: 3 Comprehensive Aspects

Businesses today deal in huge quantities of data every day. Data plays a key part in their Decision Making, Marketing, Sales, and other major activities. Therefore, companies look for cloud-based solutions for storing and processing this vast sea of data. Amazon Redshift Data Warehouse is one such solution that allows businesses to store petabytes of data seamlessly.

This article will introduce you to Amazon Redshift and will explain the Redshift Table Lock function in detail. It will also discuss, how the Redshift Table Lock operates as a valuable procedure for data security. These Locks are a security feature that limits the number of concurrent sessions that can access a Reshift Table. It also decides which operations should be performed during such sessions. Read along to understand the 3 critical aspects of these locks and implement them fr your business!

Introduction to Amazon Redshift

Amazon Redshift is a Data Warehouse product that is part of the broader cloud computing platform Amazon Web Services, with the color red being a reference to Oracle, whose corporate color is red and is referred to as Big Red informally. It’s based on technology from ParAccel (later bought by Actian), massive parallel processing (MPP) data warehouse firm that can manage big data volumes and database migrations.

Amazon Redshift is the most commonly used cloud data warehouse, with tens of thousands of users using it to analyze exabytes of data and conduct complicated analytical queries. Without having to manage your data warehouse architecture, you can run and scale analytics on all of your data in seconds. Amazon Redshift employs SQL to analyze structured and semi-structured data in data warehouses, operational databases, and data lakes, utilizing AWS-designed technologies and machine learning to give the best pricing performance at any scale.

Introduction to Redshift Table Lock

You’ll be working with several tables in a huge business data warehouse, and those tables will be shared across multiple data marts within your application. When refreshing or modifying data, users may choose to explicitly lock access to the table.

You can use the LOCK command to explicitly lock a table while performing any update on it using the syntax listed below.

LOCK [ TABLE ] table_name [, ...]

//Use lock, for example, within transaction blocks.
begin;
lock event, sales;

Working of Redshift Table Lock

To understand Redshift Table Lock, you need to learn its following 3 aspects:

• Redshift Table Lock: Example
• Redshift Table Lock: Usage
• Redshift Table Lock: Queries

Redshift Table Lock: Example

As previously indicated, you can utilize the STV LOCKS Table to check for any locks on the table you’re querying. Tables are locked in Amazon Redshift to prevent several users from altering the same table at the same time. The STV LOCKS table can be used to see if any tables in the database have been updated recently.

The example below, for example, shows current database table modifications.

stv locks: select table id, last update, lock owner, lock owner pid;

The following is an example of the output from this query, which shows three locks that are now active:

Redshift Table Lock: Usage

To lock one or more tables, table partitions, or table subpartitions in a certain mode, use the LOCK TABLE statement. This lock disables automatic locking and allows or disallows other users access to a table or view for the duration of your action.

Some types of locks can be used simultaneously on the same table. Other locks only enable a single lock per table. A locked table remains locked until you commit or rollback your transaction, either completely or to a savepoint before locking it.

A lock on a table never prevents other users from querying it. A table is never locked by a query. Readers do not obstruct authors, and writers do not obstruct readers.

Redshift Table Lock: Queries

There are 3 lock modes in Amazon Redshift:

• AccesExclusive Locks: ALTER TABLE, DROP, or TRUNCATE are examples of DDL actions that acquire AccessExclusiveLock. All other locking attempts are blocked by AccessExclusiveLock.
• AccessShare Locks: During UNLOAD, SELECT, UPDATE, or DELETE actions, AccessShareLock is acquired. Only AccessExclusiveLock attempts are blocked by AccessShareLock. Other sessions attempting to read or write on the table are not blocked by AccessShareLock.
• ShareRowExclusive Locks: COPY, INSERT, UPDATE, or DELETE actions acquire ShareRowExclusiveLock. AccessExclusiveLock and other ShareRowExclusiveLock attempts are blocked by ShareRowExclusiveLock, while AccessShareLock attempts are not.

When a query or transaction obtains a table lock, it remains in place for the duration of the query or transaction. Other queries or transactions that are waiting for the same lock to be acquired are halted.

Identify the session (PID) that is holding the lock and then end the session to solve a locking problem. Reboot your cluster if the session does not end.

Resolution of Redshift Table Lock

To find sessions that are holding locks, use the following query:

select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as table_id,nvl(trim(c."name"),d.relname) as tablename, a.granted,b.pid as blocking_pid ,datediff(s,a.txn_start,getdate())/86400||' days '||datediff(s,a.txn_start,getdate())%86400/3600||' hrs '||datediff(s,a.txn_start,getdate())%3600/60||' mins '||datediff(s,a.txn_start,getdate())%60||' secs' as txn_duration

from svv_transactions a
left join (select pid,relation,granted from pg_locks group by 1,2,3) b 
 on a.relation=b.relation and a.granted='f' and b.granted='t' 
left join (select * from stv_tbl_perm where slice=0) c 
on a.relation=c.id 
left join pg_class d on a.relation=d.oid
where  a.relation is not null;

The result is as follows:

If the result in the granted column is f (false), it signifies that the lock is being held by a transaction in another session. The blocking pid column displays the process ID of the lock-holding session. PID 19813 is holding the lock in this case.

Wait for the transaction that is holding the lock to complete before releasing it. You can also manually end the session with the command:

pg terminate backend(PID); select pg terminate backend(PID);

When a PID is terminated, all active transactions are rolled back and all session locks are released. Other transactions that are waiting to obtain the lock subsequently claim the locks.

When PG TERMINATE BACKEND(PID) returns “1,” it usually means the PID termination request was successful. This does not, however, guarantee that the PID was actually terminated. Because of its internal state, the PID in some situations cannot be established. As a result, it’s a good idea to double-check STC SESSIONS (and other relevant system tables) to see if the PID was indeed terminated.

If the process was not successfully terminated by PG TERMINATE BACKEND(PID), reboot the cluster.

Conclusion

This article might help you with all the information about Redshift Table Lock. The syntax, usage, and queries are explained in easy terms. Using the commands mentioned in this article you can easily resolve table lock problems or use table locks as they are useful tools while updating the data. 

Visit our Website to Explore Hevo

Now, to Data Analytics on your huge datasets, you first need to export this data to your Amazon Redshift Data Warehouse. This will require you to custom code complex scripts to develop the ETL processes. Hevo Data can automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 150+ multiple sources to Cloud-based Data Warehouses like Amazon Redshift, Snowflake, Google BigQuery, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your understanding of Redshift Table Lock in the comments below!