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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> To: MySQL List <mysql@lists.mysql.com> 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/[EMAIL PROTECTED]
-- echo "Hello World :)" -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
