Sometimes, you need more control over table updates than is normally allowed in a database transaction update. In my case, I needed to check the values of some columns in different tables prior to doing updates/commit or rollback. For the following scenario, I need to check the status of an ambulance unit, it's current run assignment, and if the unit is in an 'available status.' To guarantee data integrity, we can use a 'select' statement with 'FOR UPDATE,' which will lock the row. Then, I need to make sure the 'call_type' of the ambulance call is what we refer to as 'waiting to be assigned.' In other words, not already assigned to a different ambulance unit. We also use 'FOR UPDATE' to lock the row to ensure data integrity.
Code: Select all
postgres=# BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN
postgres=# SELECT ambs_units.ambs_status, ambs_units.run_number, ambs_status.available_status FROM ambs_units INNER JOIN ambs_status
postgres-# ON ambs_units.ambs_status = ambs_status.ambulance_status WHERE ambs_units.ambs_unit = '702'
postgres-# FOR UPDATE;
+-------------+------------+------------------+
| ambs_status | run_number | available_status |
+-------------+------------+------------------+
| 6 | | t |
+-------------+------------+------------------+
(1 row)
postgres=# SELECT call_type FROM ambulance_calls WHERE run_number = '20160006081602' FOR UPDATE;
+-----------+
| call_type |
+-----------+
| 0 |
+-----------+
(1 row)
postgres=# ROLLBACK;
ROLLBACK
I have created this example from the Postgres SQL console, but the program would do much the same thing. Send the 'BEGIN ISOLATION LEVEL SERIALIZABLE;' statement, get the response. Send the first select, and make sure the values returned satisfy your requirements. If the do, send your next select, and interrogate the values returned. If everything is ok, send your updates, then commit. In the example above, the 'call_type' needed to be 2 or higher, but the select query returned 0. Since this did not satisfy the requirements to assign the unit to the call, we simply send 'rollback;' which rolls back any updates, and release the locks set.
