It should not be a problem if you are updating only one row at a time. If an UPDATE query is updating more tahn one row, then MySQL may try to acquire all the row-level locks first.
For example, in the query you gave UPDATE Shop SET Total=Total-125 WHERE CustomerID=1697 AND OrderID=105 How many rows does this update ??? If Customer 1697 has OrderID=105 from shop 210, does this occur in shop 210 only or multiple shops in the case of parts coming from other shops should shop 210 not have every part needed for order 105 ??? This is only a hypothetical question. ----- Original Message ----- From: Ahmad Al-Twaijiry <[EMAIL PROTECTED]> To: Rolando Edwards <[EMAIL PROTECTED]> Cc: MySQL List <mysql@lists.mysql.com> Sent: Monday, November 13, 2006 10:39:02 AM GMT-0500 US/Eastern Subject: Re: Deadlock 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]
