Dead lock issue with stored procedure

Question

I am calling the stored procedure from a PHP page. It works fine normally. But I have used a testing tool and tried to emulate multiple requests. Then sometimes I am getting a response like this:

"Deadlock found when trying to get lock; try restarting transaction"

Here is the stored procedure:

DELIMITER $$

USE `mydb`$$

DROP PROCEDURE IF EXISTS `UserRegistration`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `UserRegistration`(IN `sfname` VARCHAR(200), IN `slname` VARCHAR(200), IN `semail` VARCHAR(200), IN `saddress` TEXT, IN `scity` VARCHAR(200), IN `sstate` VARCHAR(200), IN `szipcode` VARCHAR(20), IN `scountry` VARCHAR(200), IN `spin` VARCHAR(255), IN `sactivation_code` TEXT, IN `slcard_no` VARCHAR(20), IN `sgcm_regid` TEXT, OUT `rflag` INT)
BEGIN
    DECLARE user_id INT DEFAULT 0;
    SET rflag=0;

    DELETE FROM user_device_details WHERE udd_user_id_fk IN (SELECT upd_user_id_pk FROM user_personal_details WHERE upd_email_id=semail);
    DELETE FROM user_credit_card_details WHERE uccd_user_id_fk IN (SELECT upd_user_id_pk FROM user_personal_details WHERE upd_email_id=semail);
    DELETE FROM user_photo_details WHERE upd_user_id_fk IN (SELECT upd_user_id_pk FROM user_personal_details WHERE upd_email_id=semail);

    DELETE FROM transaction_history_details WHERE thd_user_id_fk IN (SELECT upd_user_id_pk FROM user_personal_details WHERE upd_email_id=semail);
    DELETE FROM user_loyalty_card_details WHERE ulcd_user_id_fk IN (SELECT upd_user_id_pk FROM user_personal_details WHERE upd_email_id=semail);


    DELETE FROM user_device_gcm_details WHERE udgd_user_id_fk IN (SELECT upd_user_id_pk FROM user_personal_details WHERE upd_email_id=semail);
    DELETE FROM user_secure_details WHERE usd_user_id_fk IN (SELECT upd_user_id_pk FROM user_personal_details WHERE upd_email_id=semail);
    DELETE FROM user_personal_details WHERE upd_email_id=semail;

    SELECT upd_user_id_pk INTO user_id FROM user_personal_details WHERE upd_email_id=semail;
    INSERT INTO user_personal_details(upd_first_name,upd_last_name,upd_email_id,upd_address,upd_city,upd_state,upd_zipcode,upd_country)VALUES(sfname,slname,semail,saddress,scity,sstate,szipcode,scountry);
    SELECT LAST_INSERT_ID() INTO user_id;
    INSERT INTO user_secure_details(usd_user_id_fk,usd_activation_code,usd_pin)VALUES(user_id,sactivation_code,spin);
    INSERT INTO user_device_gcm_details(udgd_user_id_fk, udgd_gcm_regid)VALUES(user_id,sgcm_regid);
    INSERT INTO user_loyalty_card_details(ulcd_card_no, ulcd_user_id_fk,ulcd_points)VALUES(slcard_no,user_id,'0.00');
    SET rflag=user_id;  
 END$$

DELIMITER ;

Suggest me a solution to overcome this issue.

Not sure but it might happen that database is trying to delete records from the database and at the same time another insertion statement is getting executed which is waiting for database to release the lock on the table. — ATR, Apr 26 '13 at 05:43
http://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans seems to be useful. — ATR, Apr 26 '13 at 05:48

score 0 · Answer 1 · answered Apr 26 '13 at 06:26

0

You should use WITH(NOLOCK) in your SELECT statements.

answered Apr 26 '13 at 06:26

Can YILDIZ

414
3
16

Dead lock issue with stored procedure

1 Answers1