Consecutive SQL statements with state

Question

I'm writing a simple messaging program, where there is a table of messages, which can be claimed by users and have stuff done to them by that user. It isn't predestined which user will claim a given message and so I want a query to select the first of all the available messages, which I have, and then one to mark that message as take, which I also have. The problem is, that I don't want two users using it at the same time to claim the same message and so I want to run the two statements consecutively, without having to go back to the program to find out what to run next in between statements. I believe I can run two consecutive statements by separating them with semi-colons but I want to use data returned in the first query as part of the second. Variables would be perfect, but as far as I'm aware they don't exist in SQL. Is there any way I can preserve state between queries?

Answer 1

Is there any way I can preserve state between queries?

No. SQL is not a procedural language. You can rewrite your two queries as a single query (not always possible, often not worth it even if it is possible), or glue them together with a procedural language. Many SQL servers provide a built-in language for this ("stored procedures"), or you can do it in your application.

The problem is, that I don't want two users using it at the same time to claim the same message

Use locks. I don't know what SQL server you are using, but using SELECT ... FOR UPDATE sounds like it would be just what you want, if it is available.

Answer 2

This is what BEGIN TRAN and COMMIT TRAN are for. Place the statements you want to protect within a transaction.

Answer 3

Transactions is a good way to go, like le dorfier says, but there are alernatives:

You could do the update first, i.e. tagging a message with the user id or similar. You dont mention which sql flavour youre using, but in mysql, i think it'd look something like this:

UPDATE message
SET    user_id = ...
WHERE  user_id = 0   -- Ensures no two users gets the same message
LIMIT 1

In ms sql, it'd be something along the lines of:

WITH q AS (
  SELECT TOP 1
  FROM message m
  WHERE user_id = 0
) 
UPDATE q
SET    user_id = 1

/B

Answer 4

You could use a temporary table perhaps.

Answer 5

SQL itself doesn't have variables, but (almost?) all RDBMS SQL extensions do. But, I'm not really sure how that alone would solve your problem.

As mentioned, a transaction will do the trick - effectively grouping your 2 unrelated statements together. However, the default transaction level will not work. (Most?) RDBMS server's default transaction level is READ COMMITTED. That doesn't prevent User 2 from reading the same row that User 1 read. For that, you'd need to use REPEATABLE READ or SERIALIZABLE.

This is a classic concurrency issue. Generally, the 2 ways of handling it are pessimistic locking or optimistic checking. A REPEATABLE READ transaction would be pessimistic (incurring the locking expense whether or not it was needed), and checking @@ROWCOUNT is optimistic (assuming it'll work, but doing something sensible when @@ROWCOUNT = 0).

Usually, we use optimistic (locking is expensive), and either use a timestamp or combination of fields read to ensure that we're changing the data we thought. So, my suggestion is to include a rowversion or timestamp field, and pass that back in to your UPDATE statement. Then, check @@ROWCOUNT to see if you updated any records. If you didn't, then go back and pick another message. In pseudo-code:

int messageId, byte[] rowVersion = DB.Select(
  "SELECT TOP 1 
      MessageId, RowVersion 
   FROM Messages 
   WHERE 
      User IS NULL";

int rowsAffected = DB.Update(
   "UPDATE Messages SET 
       User = @myUserId 
    WHERE 
       MessageId = @messageId 
       AND RowVersion = @rowVersion", 
    myUserId, messageId, rowVersion
);
if (rowsAffected = 0) 
   throw new ConcurrencyException("The message was taken by someone else");

Depending on your particular statements, you may be able to get away with just repeating the "UserId IS NULL" WHERE clause in your UPDATE statement. That's similar to Brimstedt's solution - but you still must check @@ROWCOUNT to see if rows were actually updated.