SQL-complete-tutorial

1
/* 
2
Database concurrency is the ability of a database to allow multiple 
3
users to affect multiple transactions. This is one of the main properties
4
that separates a database from other forms of data storage, like spreadsheets.
5

6
Concurrency problems:
7
1. lost updates
8
2. dirty reads
9
3. non-repeating reads
10
4. phantom reads
11
*/
12

13
SHOW VARIABLES LIKE 'transaction_isolation';
14
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- only for the current session;
15
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- set it globally;
16

17
-- ISOLATION LEVELS --
18
-- 1. READ UNCOMMITTED - the lowest isolation level with all concurrency problems;
19
-- 2. READ COMMITTED - here we don't have dirty reads, but do have unrepeatable or inconsistent reads.
20
-- 3. REPEATABLE READ - this is the DEFAULT isolation level in mysql, which solves most of the concurrency problems (not phantom reads);
21
-- 4. SERIALIZABLE - solves all concurrency problems, but has more locks, recourses which can hurt performances and reliability.
22