Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2012
    Posts
    1

    Unanswered: Getting error while inserting multiple data

    Hi,
    I am getting deadlock/stale error while inserting multiple data. I try to get the latest detected deadlock from show innodb status.I get the following message.
    121208 19:04:32
    *** (1) TRANSACTION:
    TRANSACTION 0 168599, ACTIVE 0 sec, process no 7909, OS thread id 140350248711936 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 5 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
    MySQL thread id 66, query id 3923 192.168.2.20 root Updating
    update `tap` set `name`='CALLPSTN01', `type`=1, `dateCreated`='2012-11-27 08:13:43', `dateUpdated`='2012-12-08 19:04:32', `intercept`=4 where id=5
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 223 n bits 88 index `PRIMARY` of table `intellego`.`tap` trx id 0 168599 lock_mode X locks rec but not gap waiting
    Record lock, heap no 6 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
    0: len 8; hex 8000000000000005; asc ;; 1: len 6; hex 000000029288; asc ;; 2: len 7; hex 00000001321c76; asc 2 v;; 3: len 10; hex 43414c4c5053544e3031; asc CALLPSTN01;; 4: len 1; hex 81; asc ;; 5: len 8; hex 8000124cd0a1d57f; asc L ;; 6: len 8; hex 8000124cd57775e0; asc L wu ;; 7: len 8; hex 8000000000000004; asc ;;

    *** (2) TRANSACTION:
    TRANSACTION 0 168598, ACTIVE 0 sec, process no 7909, OS thread id 140350244185856 starting index read, thread declared inside InnoDB 500
    mysql tables in use 1, locked 1
    5 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
    MySQL thread id 83, query id 3942 192.168.2.20 root Updating
    update `tap` set `name`='CALLPSTN01', `type`=1, `dateCreated`='2012-11-27 08:13:43', `dateUpdated`='2012-12-08 19:04:32', `intercept`=4 where id=5
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 0 page no 223 n bits 88 index `PRIMARY` of table `intellego`.`tap` trx id 0 168598 lock mode S locks rec but not gap
    Record lock, heap no 6 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
    0: len 8; hex 8000000000000005; asc ;; 1: len 6; hex 000000029288; asc ;; 2: len 7; hex 00000001321c76; asc 2 v;; 3: len 10; hex 43414c4c5053544e3031; asc CALLPSTN01;; 4: len 1; hex 81; asc ;; 5: len 8; hex 8000124cd0a1d57f; asc L ;; 6: len 8; hex 8000124cd57775e0; asc L wu ;; 7: len 8; hex 8000000000000004; asc ;;

    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 223 n bits 88 index `PRIMARY` of table `intellego`.`tap` trx id 0 168598 lock_mode X locks rec but not gap waiting
    Record lock, heap no 6 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
    0: len 8; hex 8000000000000005; asc ;; 1: len 6; hex 000000029288; asc ;; 2: len 7; hex 00000001321c76; asc 2 v;; 3: len 10; hex 43414c4c5053544e3031; asc CALLPSTN01;; 4: len 1; hex 81; asc ;; 5: len 8; hex 8000124cd0a1d57f; asc L ;; 6: len 8; hex 8000124cd57775e0; asc L wu ;; 7: len 8; hex 8000000000000004; asc ;;


    I checked all the tables and indexes.But I didn't get any solution.Please anyone suggest a solution for this.

    Thanks

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    A few questions:

    1. Why are you updating the same table and same row with the same information in 2 separate transactions?
    2. I am assuming that you are using InnoDB tables. Do you commit each transaction? This is quite important as process 1 will keep all locks on the tables until they are released through a ROLLBACK or a COMMIT.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •