# Understanding PostgreSQL locks

I recently transitioned from being a full time windows client developer to a full time web developer writing ruby. I spent most of my development time using a file based database called SQLite, an ORM called entity framework, and WPF for desktop clients using C#. So, on this new team we are using ruby on rails with Postgresql as out database. On a project we were working on, we had to create new database tables that included migrations in ruby. Those migrations were to create the table with necessary columns, add foreign keys, and add indexes. It was suprising to me that many of the other developers were raising concerns that adding foreign keys and indexes could fail during the migration and result in a failed production deploy thus these types of migrations should be done after hours. Me being a developer that loves continuous deploy and I refuse to have to run an after hours deploy unless it's absolutely required, so I figured I should replicate these migrations locally in order to better understand Postgresql locks.

On this new team, we were creating new app models that required a migration to create the specific tables for our postgresql persistence. During the process of adding these migrations, some of the migrations were adding foreign keys and indexes During this transition we were spinning up new API models that involved creating a new schema with Running migrations at scale requires intimate knowledge about the activity of your tables in order to perform safe migrations. This post looks to explore postgresql locks, what lock_timeout means, and what locks are acquired for a set of SQL statements.

## Contents

* Understand Postgresql locks and lock_timeout.
* Understand how to monitor postgres locks.
* Understand locks for SELECT statements.
* Understand locks for ALTER ... ADD FOREIGN KEY.

### Postgresql locks

Postgresql has the concept of table level locks to control concurrent access to the database. By nature of these locks In postgresql each SQL statement requires a lock and those locks can conflict with other concurrent database transactions meaning that one or more of those transactions will have to wait in line until the conflicting lock is released.

### Postgresql lock_timeout

The lock_timeout in Postgresql is the amount of time that your database transaction waits to acquire a lock. So, if you're performing an ALTER TABLE statement then that statement will look to acquire a specific lock depending on the action you're trying to perform.

Understanding the types of lock each action acquires and the type of activity for your specific table or tables is vital to understanding whether your migration has higher chance of succeeding.

## Visualizing postgres locks

You'll need 2 separate terminal windows open. One window will be used to run the migration and the other will be used to run a watch command.

Using a PostgreSQL terminal you can use `\watch` that will execute a query every second. In our case, we want to visualize the `pg_locks` table to see what lock is acquired on the table that is getting altered.

SELECT locktype, relation::regclass, mode, transactionid AS tid, virtualtransaction AS vtid, pid, granted FROM pg_locks; \watch 0.5

## Renaming a table with no activity

When you're initially designing a schema there is a good chance that your table names won't reflect your model names because the business requirements will always change. So, in this case I want to rename the `sent_resources` table name to `psp_sent_messages`.

I will use this 1 liner to open a transaction using the `BEGIN;` and then use an ALTER TABLE SQL statement to rename the table. We're not commiting the transaction because we want to visualize what lock will.

NOTE: This is under the assumption that

BEGIN; ALTER TABLE sent_resources RENAME TO psp_sent_messages;