Stored Procedure ahowing 'lock wait timeout exceeded try restarting transaction in mysql stored procedure' error?How to handel it?

Question

Hello guys i am trying to validate otp verification manually.for that i have written this stored procedure .When i started this code was working fine but after some time its start giving error.It was taking too long time to execute approx 50sec and after that it was giving the error lock wait timeout.So can anyone tell me why its giving such error and how to resolve it?

CREATE DEFINER=`xxxxx`@`xxxx` PROCEDURE `new_mobile_authentication`(
    IN in_macID VARCHAR(500),IN in_otp INT(5),OUT in_msg VARCHAR(100))
    BEGIN
    DECLARE userCount INT(10);
    DECLARE emailID VARCHAR(100);
    DECLARE mobileNumber BIGINT(11);
    DECLARE checkmatched INT(5);

    DELETE FROM mob_user WHERE NOW()>end_time;
    SELECT COUNT(*),email,mobile,otp into userCount,emailID,mobileNumber,checkmatched FROM  mob_user WHERE  mac_id=in_macID ;
    SET @checkEmailPresent=(SELECT COUNT(*) FROM table A WHERE email_id=emailID);

    IF(userCount!=0 AND @checkEmailPresent!=0)THEN
      IF(checkmatched=in_otp)THEN

        UPDATE table A SET auth='YES',mac_id=in_macID,mobile_num=mobileNumber WHERE email=emailID;
        SET @affRow=(SELECT ROW_COUNT());
        DELETE FROM mob_user WHERE mac_id=in_macID;
        SELECT @affRow AS affRow,email FROM table A WHERE mac_id=in_macID;
      ELSE
        SELECT 'invalid otp' INTO in_msg;
      END IF;

    ELSEIF(userCount!=0 AND @checkEmailPresent=0)THEN
      IF(checkmatched=in_otp)THEN

        INSERT INTO table A(email,mobile_num,mac_id) VALUE (emailID,mobileNumber,in_macID,); 
        SET @affRow=(SELECT ROW_COUNT());
        DELETE FROM mob_user WHERE mac_id=in_macID;
        SELECT  @affRow AS affRow,email FROM table A WHERE mac_id=in_macID;
      ELSE
        SELECT 'invalid otp' INTO in_msg;
      END IF;
    ELSE
      SELECT 'session expired' INTO in_msg;
    END IF;
    END

Answer 1

Fix timeouts by adding indexes and/or reformulating queries.

mob_user needs INDEX(end_time) and INDEX(mac_id).

SELECT COUNT(*), this, that ... without a GROUP BY does not makes sense. Nor will it work right with a GROUP BY. What were you expecting??

SET @checkEmailPresent=(SELECT COUNT(*) FROM table A WHERE email_id=emailID) can be rewritten SELECT @checkEmailPresent := COUNT(*) FROM table A WHERE email_id=emailID). Note the :=. That table needs INDEX(email_id).

IF(checkmatched=in_otp)THEN does not make sense since checkmatched is nowhere set. No that SELECT does not set it.

What is ROW_COUNT()? I don't think it is a MySQL function.