Geek Answers Handbook

Does MySQL MySQL InnoDB start the DB Triggers queue automatically?

Let's say I have received 1000 requests on our servers to update one MySQL table. In this situation, deadlock problems inevitably arise. We repeated transaction posting as recommended for deadlocks, but they are still occurring.

We are thinking of coming up with an alternative solution below.

  • Create table A, B, C.
  • Record server requests to update table D, in either B or C.
  • Create an INSERT trigger in tables A, B and C, respectively, which, in turn, will write data in table D instead of directly mapping table D to 1000 requests received by the server.

So, our question is when this happens and several rows are written to tables A, B and C, based on the triggers on tables A, B and C can simultaneously start updating table D.

The InnoDB MySQL engine automatically queues these triggers, or should we handle this in our code?

Any help is greatly appreciated.

Table D, which is now updated directly by all these queries and where the deadlock occurs, is as follows.

v_user_email    varchar(60) NO  PRI     
v_device_IMEI   varchar(40) NO  PRI     
i_adid          int(11)         NO  PRI     
i_impressions   int(4)          YES 0   
dt_pulllogdttm  datetime    NO          
c_created_by    char(15)    NO          
dt_created_on   datetime    NO          
c_modified_by   char(15)    YES         
dt_modified_on  datetime    YES

The PHP that inserts / updates the rows in this table is as follows. You will see that we are trying to send a transaction 3 times if it does not work because of a dead end, but there are transactions that do not work even then, and the log says because of a dead end.

$updateQuery = "UPDATE tb_ad_pull_log SET i_impressions = (i_impressions + 1), dt_pulllogdttm = SYSDATE(), c_modified_by = '$createdBy', dt_modified_on = SYSDATE() WHERE v_user_email = '$email' AND i_adid = $adId";
        if(ExecuteDeadLockQuery($updateQuery, "UPDATE", __LINE__) == 0) // If there is no record for this ad for the user, insert a new record
        {
            $insertQuery = "INSERT INTO tb_ad_pull_log VALUES('$email', '$device_IMEI', $adId, 1, SYSDATE(), '$createdBy', SYSDATE(), NULL, NULL)";
            ExecuteDeadLockQuery($insertQuery, "INSERT", __LINE__);
        }

The function ExecuteDeadLockQuery looks like this:

function ExecuteDeadLockQuery($query, $activity, $lineNumber)
    {
        global $errorLoggingPath;
        $maxAttempts = 3;
        $currentTry = 1;
        $noOfAffectedRows = -1;

        while($currentTry <= $maxAttempts)
        {
            $currentTry++;

            mysql_query($query);

            if( mysql_errno() <> 0 ) // If error occured
            {
                continue;
            }
            else
            {
                $noOfAffectedRows = mysql_affected_rows();
                break;
            }           
        }

        if($noOfAffectedRows == -1) // Query never executed successfully
        {
            LogError($activity . " failed in tb_ad_pull_log: " . mysql_error(), __FILE__, $lineNumber , $errorLoggingPath);
        }

        return $noOfAffectedRows;
    }

? , .

ERROR:  08-21-2011 14:09:57  UPDATE failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  83
ERROR:  08-21-2011 14:09:57  INSERT failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  86
ERROR:  08-21-2011 14:09:57  INSERT failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  86
ERROR:  08-21-2011 14:09:57  UPDATE failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction   LINE  83
ERROR:  08-21-2011 14:09:57  INSERT failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  86
ERROR:  08-21-2011 14:09:57  UPDATE failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  83
ERROR:  08-21-2011 14:09:59  UPDATE failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  83
ERROR:  08-21-2011 14:09:59  UPDATE failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  83
ERROR:  08-21-2011 14:10:01  UPDATE failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  83
ERROR:  08-21-2011 14:10:01  INSERT failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  86

83 - UPDATE PHP, 86 - INSERT. , 5-8 .

INSERT UPDATE TABLE D , TABLE X TABLE Y. , D , , ?

- , , . AFTER INSERT AFTER UPDATE TABLE D , , . , , , ,

.

    CREATE DEFINER=CURRENT_USER TRIGGER tuadmin.t_update_CPM_updateBalance
AFTER UPDATE
ON tb_ad_pull_log
FOR EACH ROW
BEGIN

    DECLARE `cpm_value` decimal(10,4);
    DECLARE `clientid` int(4);

    /* Execute the below block if the requested ad is not the default ad */
    IF NEW.i_adid <> 1 THEN

        SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
            //do updates to TABLE X and Y logic
END

, D /.

, SP PHP ?

+5
3

, ?

?

?

PostgreSQL, : ) ) ,

99,99% , VERY_SLOW_TRIGGERS, , , 8 125 , .. .

, D.

-> call modification on table D
 -> before mod trigger
 -> modification
 -> after mod trigger
-> modification complete

.. , , D , , .

:

a)

b) → , async

c) rdbms,

- "--", , TPS.

, - - ( , , ).

, :

UNLOCK TABLES , .

, /

, .

+1

mysql , , 2 2 D, 1 D . 2 . ,

+1

, MYSQL - , "", . , , 2 , VirtualBox, VirtualPC , MYSQL.

You can configure one server for writing (your updates in this case) and other servers for requests that read data. See MYSQL replication documentation here

0
source

More articles:


All Articles