Practical guide to

Database Transactions

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.

Atomic operations

It's a single operation which can be reliably executed in concurrent environment.
By default MySql Innodb engine supports these atomic operations:

						INSERT --...;
						DELETE --...;
						UPDATE --...;
					

Multiple Atomic operations are not concurrently safe

							CREATE TABLE dogs (
								id INT PRIMARY KEY
							);
							INSERT INTO dogs (id) VALUES(1), (2);
							SELECT count(*) FROM dogs;
							--> 2
							SELECT count(*) FROM dogs;
							--> 0, wait, whaaat?
					

Transactions - Basics

								CREATE TABLE dogs (
									id INT PRIMARY KEY
								);
								INSERT INTO dogs (id) VALUES(1), (2);
								SELECT count(*) FROM dogs;
								--> 2
								START TRANSACTION;
								DELETE FROM dogs;
								SELECT count(*) FROM dogs;
								--> 0
							

								--; wait a bit
								--;
								--;
								--;
								--;
								--;
								--;
								--;
								SELECT count(*) FROM dogs;
								--> ?
							

Transactions - Locking

Innodb mysql engine supports row level locking.
This means that every row can be executed concurrently without blocking as long as each row modification is contained on the same row or rows.

Transactions - Locking example

									CREATE TABLE cats (
										id INT PRIMARY KEY,
										name varchar(200)
									);
									INSERT INTO cats (id, name) VALUES (1, 'Murkis');
						

								START TRANSACTION;
								UPDATE cats set name = 'Gile' where id = 1;
								--> 1
								--
								SELECT * from cats;
								--> ?
							

								--
								--
								SELECT * from cats;
								--> ?
								UPDATE cats set name = 'Ponis' where id = 1;
								--> blocked
								SELECT * from cats;
								--> ?
							

Transactions - No Locking example

							DELETE FROM cats;
							INSERT INTO cats (id, name) VALUES (1, 'Murkis'), (2, 'Leopoldas');
						

								START TRANSACTION;
								UPDATE cats set name='Murkis2' where id = 1;
								--> 1
								--
								SELECT * from cats;
								--> ?
							

								--
								--
								SELECT * from cats;
								--> ?
								UPDATE cats set name='Leopoldas2' where id=2;
								--> 1
								SELECT * from cats;
								--> ?
							

Workshop Incoming!

First, prepare!

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

Task #1

We will be creating a bank!

						Table `accounts`:
						| id | name               | balance  |
						| 1  | Eisius             | 100      |
						| 2  | Rapolas            | 100      |
					

Requirements

Create a route

					> POST /add?amount=10&name=rapolas
					

Which could add or subtract some balance

					| id | name               | balance   |
					| 2  | rapolas            | 110       |
					

Validation

Run the following command to validate if task is complete

						php artisan test
						php artisan concurrency:test
					

Thank You!

Questions?

Get slides from klimas.lt/slides