MYSQL If statement in transaction causing error

Question

I have the following MYSQL query:

START TRANSACTION;

SELECT sport_id INTO @a FROM sports WHERE sport_id = 2 FOR UPDATE;

UPDATE sports SET sport_name = 'Table Tennis' WHERE sport_id = @a;

if (@a > 1) then
    COMMIT;
ELSE
    ROLLBACK;
END IF;

The problem is that it returns an error at the if statement:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if (@a > 1) then COMMIT' at line 1

I've looked on stack overflow and there is an answer showing a similar query, written in pretty much the same way, but they are using the variable without an @ symbol. Removing the @ for my query does not resolve the issue.

This is just a test query to try out some transactions using MYSQL, hence why the query seems a little pointless. I'm a little stuck.

Answer 1

MySQL doesn't recognize a statement beginning with the keyword IF as as a valid SQL statement.

The IF statement works only in the context of a compound statement (that is, a block of statements enclosed between BEGIN and END. Currently, the compound statement is only supported in the context of a stored program (stored procedure, function or trigger.)

http://dev.mysql.com/doc/refman/5.5/en/begin-end.html

For testing, try...

DELIMITER //

CREATE PROCEDURE usp_test_transaction()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
  START TRANSACTION;

  -- whatever DML operations and SELECT statements you want to perform go here

  IF (1=1) THEN
    COMMIT;
  ELSE
    ROLLBACK;
  END IF;
END//

DELIMITER ;

CALL usp_test_transaction;

(NOTE: I am not advocating here that transactions be handled inside of stored procedure. My personal preference is to NOT do this, and instead handle transactions at a higher level. But the example above should work; I believe MySQL does support calling START TRANSACTION, COMMIT and ROLLBACK within the context of a stored procedure.)