List:General Discussion« Previous MessageNext Message »
From:Ahmad Al-Twaijiry Date:November 13 2006 3:39pm
Subject:Re: Deadlock
View as plain text  
Sorry

I have $dblink->commit(); right after $dblink->exec($sql); but I
forgot to write it here (my mistake, sorry )

also I want to mention that I have 3 primary key in my table:

ShopID
CustomerID
OrderID

Could this be the problem ?


On 11/13/06, Rolando Edwards <redwards@stripped> wrote:
> You should play it safe and add $dblink->commit(); right after
> $dblink->exec($sql);
>
> The reason for this is from Page 419 of the
> MySQL 5.0 Certification Study Guide bullet point #3:
>
> During the course of a transaction, InnoDB may acquire row locks
> AS IT DISCOVERS THEM TO BE NECESSARY.
>
> I don't like the sound of that statament, because it is assumed
> that PDO will attempt an autocommit with each SQL statment.
> That is not so with a transaction in PDO. Note the explanation
> for PDO::commit (http://us3.php.net/manual/en/function.pdo-commit.php):
>
> bool PDO::commit ( void )
>
> Commits a transaction, returning the database connection to autocommit mode until the
> next call to PDO::beginTransaction() starts a new transaction.
>
> That means that autocommit is disabled when you issue
> $dblink->beginTransaction();
>
> ----- Original Message -----
> From: Ahmad Al-Twaijiry <ahmadt@stripped>
> To: MySQL List <mysql@stripped>
> Sent: Monday, November 13, 2006 9:34:37 AM GMT-0500 US/Eastern
> Subject: Deadlock
>
> Hi everyone,
>
> Everyday I got around 10 Deadlock errors in my database :
>
> SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when
> trying to get lock; try restarting transaction
>
> SQL=UPDATE Shop SET Total=Total-125 WHERE CustomerID=1697 AND OrderID=105
>
>
> I'm using Innodb engine type for my tables and I already checked
> http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html
>
> I know this is not dangerous but is there anyway to prevent it ? I
> recheck my script and I couldn't find any problem on it
>
> my question is this deadlock because there is another transaction that
> lock  the row or the table ? I want to know if this error because row
> locking or table locking
>
> my code is something like this (PHP5) :
>
> <?php
> try{
>                     $dblink->beginTransaction();
>                     $sql="UPDATE Shop SET Total=Total-$q WHERE
> CustomerID=" . $CustomerID. " AND OrderID=" . $OrderID;
>                     $dblink->exec($sql);
> }catch (PDOException $e) {
>                     // deadlock , report it
>                     $dblink->rollBack();
> }
> ?>
>
>
> I also have this code in another script (with another mysql username)
> that will go over all record in Shop table and do some update in the
> records.
>
>
> Here is what I got when I execute "SHOW ENGINE INNODB STATUS"
>
>
> 061104 13:53:02
> *** (1) TRANSACTION:
> TRANSACTION 0 3020198, ACTIVE 0 sec, process no 12031, OS thread id
> 2584136624 fetching rows
> mysql tables in use 1, locked 1
> LOCK WAIT 42 lock struct(s), heap size 5504
> MySQL thread id 110727, query id 3714030 localhost user2 Updating
> UPDATE Shop SET Total=Total-125 WHERE CustomerID=1697 AND OrderID=105
> *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
> RECORD LOCKS space id 0 page no 35335 n bits 400 index `PRIMARY` of
> table `dbname/Shop` trx id 0 3020198 lock_mode X waiting
> Record lock, heap no 292 PHYSICAL RECORD: n_fields 8; compact format;
> info bits 0
>   0: len 4; hex 00003471; asc   4q;; 1: len 4; hex 000006cc; asc
> ;; 2: len 4; hex 00000060; asc    `;; 3: len 6; hex 0000002e1597; asc
>   .  ;; 4: len 7; hex 000000801026f1; asc      & ;; 5: len 4; hex
> 00000c61; asc    a;; 6: len 4; hex d723a142; asc  # B;; 7: len 8; hex
> 8000123ed6edf88a; asc    >    ;;
>
> *** (2) TRANSACTION:
> TRANSACTION 0 3020183, ACTIVE 1 sec, process no 12031, OS thread id
> 2588175280 starting index read, thread declared inside InnoDB 500
> mysql tables in use 1, locked 1
> 30 lock struct(s), heap size 2496, undo log entries 26
> MySQL thread id 110721, query id 3714111 localhost user2 Updating
> UPDATE Shop SET Total=Total+1,Price=27.67 WHERE ShopID=9268
> *** (2) HOLDS THE LOCK(S):
> RECORD LOCKS space id 0 page no 35335 n bits 400 index `PRIMARY` of
> table `dbname/Shop` trx id 0 3020183 lock_mode X
> Record lock, heap no 292 PHYSICAL RECORD: n_fields 8; compact format;
> info bits 0
>   0: len 4; hex 00003471; asc   4q;; 1: len 4; hex 000006cc; asc
> ;; 2: len 4; hex 00000060; asc    `;; 3: len 6; hex 0000002e1597; asc
>   .  ;; 4: len 7; hex 000000801026f1; asc      & ;; 5: len 4; hex
> 00000c61; asc    a;; 6: len 4; hex d723a142; asc  # B;; 7: len 8; hex
> 8000123ed6edf88a; asc    >    ;;
>
> *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
> RECORD LOCKS space id 0 page no 13428 n bits 400 index `PRIMARY` of
> table `dbname/Shop` trx id 0 3020183 lock_mode X waiting
> Record lock, heap no 59 PHYSICAL RECORD: n_fields 8; compact format;
> info bits 0 0: len 4; hex 00002434; asc   $4;; 1: len 4; hex 0000029c;
> asc     ;; 2: len 4; hex 0000005d; asc    ];; 3: len 6; hex
> 0000002e0aa8; asc    .  ;; 4: len 7; hex 000000800f27f5; asc      ' ;;
> 5: len 4; hex 0000160d; asc     ;; 6: len 4; hex 295cdd41; asc )\ A;;
> 7: len 8; hex 8000123ed6cf3331; asc    >  31;;
>
>
>
> --
> echo "Hello World :)"
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
>


-- 
echo "Hello World :)"
Thread
DeadlockAhmad Al-Twaijiry13 Nov
  • Re: DeadlockRolando Edwards13 Nov
    • Re: DeadlockAhmad Al-Twaijiry13 Nov
      • Re: DeadlockRolando Edwards13 Nov
      • Re: DeadlockAsif Lodhi16 Nov
        • Re: DeadlockAhmad Al-Twaijiry19 Nov