There are situations where InnoDB deadlock would come up when you least expect it. For example, SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three past questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

• Reasons for occasionally slow queries?
• Trouble deciphering a deadlock in an innodb status log
• Will these two queries result in a deadlock if executed in sequence?

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that even SELECT queries can lock InnoDB data. Although autocommit could be enabled (rendering each query its own transaction), a single SQL statement can still be victimized by deadlocks.

You also have special cases of SELECT where you can lock specific rows on demand.

Based on InnoDB Deadlocking link, the sequences of event to cause this situation could theoretically be as follows:

• Your SQL UPDATEs a single row but generates an error
• The UPDATE causes a rollback of the one row
• The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk instead of just documenting and hopong you google for it. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

QUESTION #1

What is error in this procedure?

You seem to have some scope confusion on the variables

ANSWER TO QUESTION #1

PROBLEM : Your parameters have identical names to column names in the tables. This could produce some unpredictable results.

SOLUTION : Change the names of the parameters so that they are distinct from the column names

create procedure accountstatus

(

    IN  inmode              varchar(27),
    IN  given_AccountStatus_id    int,
    IN  given_AccountStatus       varchar(255),
    IN  given_CreatedOn           datetime,
    IN  given_CreatedBy           varchar(255),
    IN  given_UpdatedOn           datetime,
    IN  given_UpdatedBy           varchar(255),
    IN  given_is_active           bit)

    Begin

    if inmode   =   'insert'
    then
        insert into accountstatus
        (AccountStatus_id, Account_Status, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, is_active)
        values
        (given_AccountStatus_id, given_Account_Status, given_CreatedOn, given_CreatedBy, given_UpdatedOn, given_UpdatedBy, given_is_active);
    end if;

    /*update*/

    if inmode   =   'update'
    then
        update accountstatus acc
        set
--      acc.AccountStatus_id    =   given_AccountStatus_id,  <- Not Needed for UPDATE
        acc.Account_Status      =   given_Account_Status,
        acc.CreatedOn           =   given_CreatedOn,
        acc.CreatedBy           =   given_CreatedBy,
        acc.UpdatedOn           =   given_UpdatedOn,
        acc.UpdatedBy           =   given_UpdatedBy,
        acc.is_active           =   given_is_active
        where
        acc.AccountStatus_id    =   given_AccountStatus_id;

    end if;

    /*delete*/

    if inmode   =   'delete'
    then
        update  accountstatus acc
        set
--      acc.AccountStatus_id    =   given_AccountStatus_id, <- Not Needed for DELETE
        acc.is_active           =   0
        where
        acc.AccountStatus_id    =   given_AccountStatus_id;
    
    end if;

    /*select*/

    if inmode   =   'select'
    then
        select * from accountstatus acc
        where
        acc.AccountStatus_id    =   given_AccountStatus_id;
    end if;
end

CAVEAT : Please note that I commented out two lines

QUESTION #2

Is there any other ways to implement this procedure?

ANSWER TO QUESTION #2

You could use triggers

• Aug 15, 2011 : Performance of a Trigger vs Stored Procedure in MySQL
• Apr 06, 2012 : How to speed up audit trigger?

QUESTION #3

How it will effect the performance of the database?

ANSWER TO QUESTION #3

Doing bulk operations can make the MySQL server process to tedious work and bog it down. Here is other posts showing how to use SQL efficiently to replace a trigger and stored procedure, why too many triggers can be bad, and how as little code as possible

• Aug 11, 2011 : Policies RE database triggers in well-designed applications?
• Aug 12, 2011 : Performance of a Trigger vs Stored Procedure in MySQL
• Dec 23, 2011 : How to set up triggers in MySQL for copying a field

EPILOGUE

The simpler the code in the Stored Procedure or Trigger, the less impact on performance, especially on bulk INSERTs, UPDATEs, and DELETEs.

Please consider the Storage Engine and its locking characteristics (using MyISAM) when using triggers and the autocommit behavior (if using InnoDB).