Practical guide to

Database Locking

Created by Aidas Klimas

Disclaimer!

Please be careful while testing SQL queries.
Everything you see here should be executed on LOCAL environment.
Please double check if your .env file and SQL client is not connected to production.

Agenda

  1. Locking Types
  2. Row Locking & Dirty Reads
  3. Gap Locking & Inserts
  4. Handling Deadlocks
  5. Workshop!

Locking Types


						SELECT * FROM dogs FOR UPDATE;
						-- Entire table is locked

						SELECT * FROM dogs WHERE id = 2 FOR UPDATE;
						-- Only one row is locked

						SELECT * FROM dogs WHERE id > 2 FOR UPDATE;
						-- Range is locked + Gap locking

Row locking & Dirty Reads

SQL connection session 1 

								CREATE TABLE dogs (
									id INT PRIMARY KEY, name varchar(255)
								);
								INSERT INTO dogs (id, name) VALUES
									(1, 'Gile'), (2, 'Pukis');
								--;
								START TRANSACTION;
								SELECT * FROM dogs FOR UPDATE;
								UPDATE dogs set name='Pukis';
								--;
								--;
								--;
								--;
								--;
								--;
SQL connection session 2 

								--;
								--;
								--;
								--;
								--;
								--;
								--;
								--;
								--;
								SELECT * FROM dogs;
								--> ?

								START TRANSACTION;
								SELECT * FROM dogs;
								--> ?

Gap locking & Inserts

SQL connection session 1 

								CREATE TABLE dogs (
									id INT PRIMARY KEY, name varchar(255)
								);
								INSERT INTO dogs (id, name) VALUES
									(1, 'Gile'), (2, 'Pukis');
								--;
								START TRANSACTION;
								SELECT * FROM dogs WHERE id > 2
									FOR UPDATE;
SQL connection session 2 

								--;
								--;
								--;
								--;
								--;
								--;
								--;
								--;
								--;
								INSERT INTO dogs (id, name) VALUES
									(3, 'Apelsinas');
								--> ?

Unintentional Gap locking

SQL connection session 1 

								CREATE TABLE dogs (
									id INT PRIMARY KEY, name varchar(255)
								);
								INSERT INTO dogs (id, name) VALUES
									(1, 'Gile'), (2, 'Pukis');
								--;
								START TRANSACTION;
								SELECT * FROM dogs WHERE name = 'Pukis'
									FOR UPDATE;
SQL connection session 2 

								--;
								--;
								--;
								--;
								--;
								--;
								--;
								--;
								--;
								INSERT INTO dogs (id, name) VALUES
									(3, 'Apelsinas');
								--> ?

Fixing Gap locking

SQL connection session 1 

								DROP TABLE dogs;
								CREATE TABLE dogs (
									id INT PRIMARY KEY, name varchar(255),
									INDEX(name)
								);
								INSERT INTO dogs (id, name) VALUES
									(1, 'Gile'), (2, 'Pukis');
								--;
								START TRANSACTION;
								SELECT * FROM dogs WHERE name = 'Pukis'
									FOR UPDATE;
SQL connection session 2 

								--;
								--;
								--;
								--;
								--;
								--;
								--;
								--;
								--;
								--;
								--;
								INSERT INTO dogs (id, name) VALUES
									(3, 'Apelsinas');
								--> ?

The Rule of Thumb!

Always make sure that at least one field is using an index then locking.

Deadlocks

SQL connection session 1 

							START TRANSACTION;

							SELECT * FROM dogs WHERE id = 1
									FOR UPDATE;
							SELECT * FROM dogs WHERE id = 2
									FOR UPDATE;
							--> ?
SQL connection session 2 

							START TRANSACTION;

							SELECT * FROM dogs WHERE id = 2
									FOR UPDATE;
							SELECT * FROM dogs WHERE id = 1
									FOR UPDATE;
							--> ?

Workshop Incoming!

First, prepare!

Go to
https://gitlab.com/ekomlita/workshop/database-transactions/-/tree/day-2-locks
clone and follow readme

Thank You!

Questions?

Get slides from klimas.lt/slides