CODEWITHSUNDEEP
mysqlsqlstored-procedures
Don Rhummy
Don Rhummy

Reputation: 25860

How do I handle errors so a locked table will unlock in a procedure?

I want to have a stored procedure that does the following:

  1. Locks a table

  2. Checks for a value in it

  3. Updates same table based on that value

  4. Unlocks the table

If an error occurs between 1 and 4, will the table be unlocked? Or do I need to capture the error somehow? (how?)

Is there a better way to do this?

Upvotes: 3

Views: 1000

Answers (2)

Michael - sqlbot
Michael - sqlbot

Reputation: 179194

You can't lock a table within a stored procedure in MySQL.

SQL Statements Not Permitted in Stored Routines

Stored routines cannot contain arbitrary SQL statements. The following statements are not permitted:

The locking statements LOCK TABLES and UNLOCK TABLES.

— http://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html

If you are using InnoDB, then you can accomplish your purpose by locking the rows of interest using locking reads with SELECT ... FOR UPDATE. When you hit an error and roll back the transaction, the rows are unlocked automatically.

I wrote about this in detail in this recent answer where the question involved avoiding conflicting inserts but the underlying concept is the same whether you know the row you want already exists, or whether it might or might not exist.

Upvotes: 2

rory.ap
rory.ap

Reputation: 35318

Have you considered using transactions with a try-catch block? See this:

BEGIN TRAN
SAVE TRAN S1 -- Savepoint so any rollbacks will only affect this transaction

BEGIN TRY
    /* Do your work in here */
END TRY
BEGIN CATCH
    ROLLBACK TRAN S1 -- rollback just this transaction
    SET @ErrorMessage = ERROR_MESSAGE()
    SET @Severity = ERROR_SEVERITY()
    SET @State = ERROR_STATE()
    RAISERROR(@ErrorMessage, @Severity, @State) -- re-throw error if needed
END CATCH

Upvotes: 1

Related Questions