Administration

Introduction to Locks

0

While lock events aren’t common, there can be times where a lock can block another transaction causing it to potentially fail. Blocking typically occurs when a transaction such as INSERT, UPDATE, or DELETE does not get committed within a transaction. This post will cover the different types of locks, show an illustration of a READ COMMITTED isolation level, and tips for monitoring.

Lock Types

There are different lock types or modes that can be acquired by a transaction. These are1:

  • S – Share lock needed for select operations. Select operations in READ COMMITTED transaction mode do not require share (S) table locks. See Transactions in the Concepts Guide.
  • I – Insert lock needed for insert operations.
  • SI – SharedInsert lock needed for operations that read and query the table. Distinguished from X because SI mode disallows delete/update operations. SI is also the result of lock promotion.
  • X – Exclusive lock is always needed for delete operations. X lock is also the result of lock promotion.
  • T – Tuple Mover lock used by the Tuple Mover and also used for COPY into pre-join projections
  • U – Usage lock needed for moveout and mergeout operations in the first phase; they then upgrade their U lock to a T lock for the second phase. U locks conflicts with no other locks but O.
  • O – Owner lock needed for DROP_PARTITION, TRUNCATE TABLE, and ADD COLUMN. O locks conflict with all locks. O locks never promote.

Isolation Level

Vertica uses the READ COMMITTED isolation level by default. This means that a SELECT query will only see results that have been committed at the beginning of the transaction or results of updates within its transaction, even if they have not been committed2. While SELECT statements don’t acquire locks, all other DML statements acquire write locks to prevent other READ COMMITTED transactions from modifying the same data.

Illustration

In this illustration, two sessions will attempt various requests on public.customer_dimension. First, session A will acquire an X lock for an UPDATE (without COMMIT). Next, session B will attempt to request data. Lastly, session B will attempt to perform a DELETE.

First, an UPDATE statement is sent from session A:

Without committing the transaction, an X lock can be seen as acquired for session A in v_monitor.locks:

Next, a SELECT in session B on the table will return results before the commit of session A.

However, if session B attempts to perform a DELETE, it will become blocked by session A:

The scope of the lock for session B appears as REQUESTED, while an X lock is held by the uncommitted session A. If session A does not COMMIT or ROLLBACK, session B will not be able to obtain a lock and will be rejected at the end of the lock timeout period (default timeout for locks is 300 seconds):

Monitoring

The v_monitor.locks system table provides active lock grants and requests for all nodes. Locks that have been active for more than a reasonable period of time should be monitored.

Historical lock information can be found in v_internal.dc_lock_attempts. The availability of historical data will depend on the retention period specified for this data collector. When looking at this data collection table, monitor for a result of timeout:

References

About the author / 

Norbert Krupa

Norbert is the founder of vertica.tips and a Solutions Engineer at Talend. He is an HP Accredited Solutions Expert for Vertica Big Data Solutions. He has written the Vertica Diagnostic Queries which aim to cover monitoring, diagnostics and performance tuning. The views, opinions, and thoughts expressed here do not represent those of the user's employer.

Leave a Reply

Upcoming Events

  • No upcoming events
AEC v1.0.4

Subscribe to Blog via Email

Enter your email address to subscribe and receive notifications of new posts by email.

Read more use cases here.

Notice

This site is not affiliated, endorsed or associated with HPE Vertica. This site makes no claims on ownership of trademark rights. The author contributions on this site are licensed under CC BY-SA 3.0 with attribution required.
%d bloggers like this: