MVCC & Locking Notes

Back to Geoffrey's PostgreSQL Notes

Demonstration of MVCC

The following demonstrates the MVCC features of PostgreSQL. You'll need three psql shells open at once, and the following tables:

CREATE SEQUENCE school_id_seq;
CREATE TABLE school (
  school_id   INT NOT NULL PRIMARY KEY DEFAULT nextval('school_id_seq'),
  school_name VARCHAR(80) NOT NULL
);

CREATE SEQUENCE student_id_seq;
CREATE TABLE student (
  student_id   INT NOT NULL PRIMARY KEY DEFAULT nextval('student_id_seq'),
  student_name VARCHAR(80) NOT NULL,
  school_id    INT NOT NULL
);

INSERT INTO school (school_name) VALUES ('Alice''s School of Truck Driving');
INSERT INTO school (school_name) VALUES ('Bob''s School of Underwater Knitting');

INSERT INTO student (student_name, school_id) VALUES ('Charlie', 1);
INSERT INTO student (student_name, school_id) VALUES ('Doug', 1);
INSERT INTO student (student_name, school_id) VALUES ('Ernie', 2);
Client 1 Client 2 Client 3 Notes
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM school ORDER BY school_id; 		Client 1 starts reading school data from the database
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM student ORDER BY student_id;
UPDATE student SET student_name = 'Alice' WHERE student_id = 1; 		Client 2 starts updating student data in the database
SELECT * FROM student ORDER BY student_id; Client 3 can't see the changes made by client 2 because client 2 hasn't committed the changes yet.
COMMIT; Client 2 commits its changes to the student data.
SELECT * FROM student ORDER BY student_id; Client 3 can now see the changes because they have been committed.
SELECT * FROM student ORDER BY student_id; Client 1 can't see the changes because Postgres is presenting it with the database state as it was when it started the transaction (or more precisely, as it was when we did the first SELECT). Note that this is the first time this client has read the student table.

Cleaning up

If you ran all of the above and want to clean up now, the following may be handy: 
DROP SEQUENCE school_id_seq;
DROP TABLE school;
DROP SEQUENCE student_id_seq;
DROP TABLE student;

Locking a Whole Table

Sometimes you may want to have read & write locks at the table level. For example, this gives you the guarantee that the table you are reading will not be written to for the duration of your transaction (as opposed to the previous example where changes may be made but you won't see them until the end of your transaction).

To get a read lock on a table:

LOCK TABLE table IN SHARE MODE;

And a write lock:

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

For this demonstration, you'll need three clients again and any old table; we'll call ours student.

Client 1 Client 2 Client 3 Notes
BEGIN;
LOCK TABLE student IN SHARE MODE; 		BEGIN;
LOCK TABLE student IN SHARE MODE; 		Clients 1 and 2 can simultaneously obtain a read lock on the student table.
BEGIN;
LOCK TABLE student IN SHARE ROW EXCLUSIVE MODE; 		Client 3 tries to obtain a write lock. Note how it blocks.
COMMIT; Client 1 releases its read lock. Note that client 3 is still blocked.
COMMIT; Client 2 releases its read lock. Note that client 3 now gets its write lock.
BEGIN;
LOCK TABLE student IN SHARE MODE; 		BEGIN;
LOCK TABLE student IN SHARE MODE; 		Clients 1 and 2 try to get a read lock. Note that they block waiting for client 3.
COMMIT; Client 3 releases its write lock. Note that clients 1 and 2 now get their read locks.

Back to Geoffrey's PostgreSQL Notes