enforcing database constraints: code vs sql

Question

This is a follow up to this question.

Here is my schema

CREATE TABLE A(
     id serial NOT NULL,
     date timestamp without time zone,
     type text,
     sub_type text,
     filename text,
     filepath text,
     filesize integer,
     lock_status int
 );

In this database, a user can update the type,sub-type,filename,filepath,filesize as long as 'lock_status' is not set.

So, in the web page code (php) I can check the lock_status before updating the item.

However, there could be a case where another user has updated the lock status in the time between the first user's check & update.

So, is there a way in SQL to check the lock status before the row gets updated?

• webpage code is in php
• database is PostgreSQL

edit added type,sub-type to the editable field list above

Answer 1

Sure, use UPDATE ... WHERE lock_status = 0. Alternatively, you could try using stored procedures.

Answer 2

You might want to look into row-level locking. It looks like Postgres has it.

Answer 3

I'd recommend checking and setting the lock_status bit at the same time.

Issue an UPDATE A SET lock_status = 1 WHERE id = ... AND lock_status = 0. That query is atomic without needing an explicit transaction. If that doesn't return a count of 1 object updated your lock couldn't be applied. Then you just need to confirm that your primary key still exists. You might want to consider moving this to a stored procedure if you'll be calling it from multiple places and/or on multiple tables.

pseudo-PHP:

$result = pg_query_params($conn, "UPDATE A SET lock_status = 1 WHERE id = $1 AND lock_status = 0", $id);
$tuples = pg_affected_rows($result);

if ($tuples < 1) {
    // couldn't lock
} else {
    // lock applied
}

Answer 4

The cleanest way, as Mr. Potato Head said, is to simply use a WHERE clause to only affect rows where lock_status = 0. Because that's a single SQL statement, it's guaranteed to be atomic. Then you can see if any rows were affected (for example, using @@rowcount) and react accordingly, either by trying again indefinitely, or by showing an error message, etc.

The problem with checking and updating in two steps is that unless you wrap them in an explicit transaction (e.g. "BEGIN TRANSACTION ... COMMIT TRANSACTION"), they are not guaranteed to be atomic, so you could theoretically get more than one process who think the lock is off and proceed. I say "theoretically" because with the speed that these statements are executed, it's incredibly unlikely that'd ever happen unless you have a hugely concurrent environment with a ton (thousands?) of users banging on this thing at the same time. That's why errors like this often go unnoticed, but then crop up in strange unexplained bugs later.

To learn more about this kind of issue, you might want to read a book on concurrent programming such as this one: Concurrent Programming by Gregory Andrews.

Answer 5

just a single sample procedure on the database side with a trigger and a PL/Pgsql function :

CREATE OR REPLACE FUNCTION trgfn_ensure_unlocked() RETURNS TRIGGER AS $trig$
  DECLARE
  BEGIN
    IF (OLD.lock_status <> 0) THEN
      RAISE EXCEPTION 'Row is locked';
    END IF;
    RETURN NEW;
  END;
$trig$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_unlocked BEFORE UPDATE ON table_name
      FOR EACH ROW EXECUTE PROCEDURE trgfn_ensure_unlocked();

Basically, you a plpgsql function that will check that in the old (before the update) version of the row, the lock_status is 0. if not it will throw an exception.

this function is called by the trigger that will be automatically called when doing an SQL UPDATE on this table.

Hope this helps ...