Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: MySQL Transaction Atomicity Question

    Hi,

    I have the following stored procedure:

    Code:
    CREATE PROCEDURE create_order(IN inCustomerId INT, IN inProductId INT)
    BEGIN
      DECLARE orderId INT;
        
      DECLARE exit handler for sqlwarning rollback;
      DECLARE exit handler for sqlexception rollback;
    
      START TRANSACTION;
    
      -- Insert a new record into orders and obtain the new order ID
      INSERT INTO tbl_orders (order_date, customer_id) 
           VALUES (NOW(), inCustomerId);
      -- Obtain the new Order ID
      SELECT LAST_INSERT_ID() INTO orderId;
      
      -- Insert order items in order_items table
      INSERT INTO tbl_order_items (order_id, pd_id, pd_name, qty, pd_price)
      SELECT orderId
           , pd.pd_id
           , pd.pd_name
           , pd.pd_qty
           , pd.pd_price
       FROM tbl_products pd
      WHERE pd.pd_id = inProductId;
      
      -- Insert credits in to credits acct only if pd type is 1, 2 or 3
      INSERT INTO tbl_customer_credit_acct (customer_id, pd_id, qty)
           SELECT inCustomerId, pd.pd_id, pd.qty
      		   FROM tbl_products pd
            WHERE pd.pd_id = inProductId AND (pd.pd_type = 1 OR pd.pd_type = 2 OR pd.pd_type = 3);
     
       
       -- Return the Order ID
      SELECT orderId; 
      
      COMMIT;
    What I have found is that if a customer orders a product that is not of product type 1, 2, or 3 the procedure rolls back becuase the final insert produces a not found state.

    How would you add a continue handler for not found to this procedure?

    I tried adding the following but i get a sql syntax error:

    Code:
    DECLARE continue handler for not found;
    Any help would be appreciated.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    The declare CONTINUE HANDLER FOR NOT FOUND requires some expression. I suggest that you simply do the following:

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @dummy = 1;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by it-iss.com View Post
    The declare CONTINUE HANDLER FOR NOT FOUND requires some expression. I suggest that you simply do the following:

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @dummy = 1;
    I've been testing this and it seems that the transaction is throwing a warning somewhere and rolling back. If I remove the warning handler the transactions works.

    But there is no way to identify what the warning is in a sp. MySql documentation states SQLStates begining with 01 indicate a warning:
    MySQL :: MySQL 5.1 Reference Manual :: 12.7.4.2 DECLARE for Handlers

    Unfortunately they dont provide a list of all warning codes - they only provide a list of error codes.

    I tried adding the following:

    Code:
    DECLARE exit handler for SQLSTATE '01000' set @_err = '01000';
    and then doing a select @_err to see what error value is in the variable.

    I need a list of all warning codes so that I can declare them all but no where does mysql provide such a list so how on earth can anyone troubleshoot this?

  4. #4
    Join Date
    Mar 2007
    Posts
    212
    OK.. done some more testing on this. Completely removed the warning handler. Run sp in via command prompt. Transaction works :

    Code:
    Query OK, 0 rows affected, 1 warning (0.08 sec)
    
    Warning (Code 1329): No data - zero rows fetched, selected, or processed
    So why is the sqlwarning code 1329 when according to MySQL documentation warning codes begin with 01?

  5. #5
    Join Date
    Mar 2007
    Posts
    212
    It doesnt look like mysql transaction within a sp is well thought out.

    If you declare the following handers in a sp:

    Code:
      DECLARE exit handler for not found rollback;
      DECLARE exit handler for sqlwarning rollback;
      DECLARE exit handler for sqlexception rollback;
    If at any point in the transaction you want to do a check before executing the next statement and if nothing is returned for your check then the transaction rollsback i.e.

    Code:
    -- check if value exists
    Select value from tbl_test where value = 1 into check;
    
    IF check = 1 THEN
      -- execute next statement
    ELSE
      -- execute next statement
    END IF;
    I would have to remove handler for not found handler for sqlwarning before such a transaction would work. This is not ideal, there must be another solution to this.

    The only alterntive I can think of if ensuring all your check statments retunr a value i.e doing a count instead. This becomes very messy whne sometimes a count is not what you want to obtain from a check variable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •