MySQL Forums
Forum List  »  MySQL Administrator

Insert/Update queries getting locked
Posted by: Ganesh Bansal
Date: October 07, 2009 05:59AM

Hi All

I am facing locking problems from past few days. Simple UPDATE query which is updating a few columns in my table on basis of primary key column gets locked. Same is with my INSERT queries. Kindly note that no SELECT query got locked till now.

Below is o/p of following commands:
SHOW ENGINE INNODB STATUS \G
SHOW CREATE TABLE PersonNews \G
SHOW TABLE STATUS LIKE 'PersonNews' \G
SHOW CREATE TABLE Cities \G
SHOW TABLE STATUS LIKE 'Cities' \G
SHOW VARIABLES LIKE '%buffer%';

Any suggestion/help will be highly appreciated. Let me know if I need to send more details about my problem.

Regards

Ganesh
=================================================================================
mysql> SHOW ENGINE INNODB STATUS \G
*************************** 1. row ***************************
Status:
=====================================
091007 6:42:10 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 12104, signal count 12083
Mutex spin waits 0, rounds 64284, OS waits 417
RW-shared spins 24086, OS waits 11407; RW-excl spins 261, OS waits 103
------------------------
LATEST FOREIGN KEY ERROR
------------------------
091007 6:29:35 Transaction:
TRANSACTION 0 51198677, ACTIVE 0 sec, process no 7125, OS thread id 1160321360 updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
12 lock struct(s), heap size 1216, undo log entries 1
MySQL thread id 6922, query id 157895 localhost 127.0.0.1 root updating
DELETE FROM `FundPlan`.`OperationDayWorkingCapitalPlans` WHERE Id = 90
Foreign key constraint fails for table `fundplan/transactions`:
,
CONSTRAINT `fk_1235981` FOREIGN KEY (`ODWCPId`) REFERENCES `operationdayworkingcapitalplans` (`Id`)
Trying to delete or update in parent table, in index `PRIMARY` tuple:
DATA TUPLE: 28 fields;
0: len 8; hex 800000000000005a; asc Z;; 1: len 6; hex 0000030d3ad5; asc : ;; 2: len 7; hex 00000173db2fbc; asc s / ;; 3: len 13; hex 446166666f64696c4f74686572; asc DaffodilOther;; 4: SQL NULL; 5: len 8; hex 80000000000001b3; asc ;; 6: len 8; hex 80001245cd099280; asc E ;; 7: len 8; hex 0000000000000000; asc ;; 8: SQL NULL; 9: SQL NULL; 10: len 8; hex 8000000000000012; asc ;; 11: len 8; hex 0000000000000000; asc ;; 12: SQL NULL; 13: SQL NULL; 14: SQL NULL; 15: SQL NULL; 16: len 8; hex 0000000000000000; asc ;; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: SQL NULL; 22: SQL NULL; 23: SQL NULL; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27: len 8; hex 8000000000000005; asc ;;

But in child table `fundplan/transactions`, in index `fk_1235981`, there is a record:
PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 800000000000005a; asc Z;; 1: len 8; hex 800000000000001c; asc ;;

------------
TRANSACTIONS
------------
Trx id counter 0 51201977
Purge done for trx's n:o < 0 51201927 undo n:o < 0 0
History list length 14
Total number of lock structs in row lock hash table 35
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 7125, OS thread id 1164847440
MySQL thread id 8415, query id 189783 localhost root
---TRANSACTION 0 0, not started, process no 7125, OS thread id 1164581200
MySQL thread id 8096, query id 210882 59.144.174.74 gaurav.garg
---TRANSACTION 0 51201968, not started, process no 7125, OS thread id 1161918800
MySQL thread id 8112, query id 210867 210.7.76.130 gaurav.garg
---TRANSACTION 0 0, not started, process no 7125, OS thread id 1158457680
MySQL thread id 1, query id 210882 210.7.76.130 gaurav.garg
---TRANSACTION 0 0, not started, process no 7125, OS thread id 1160587600
MySQL thread id 18, query id 210983 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 0, not started, process no 7125, OS thread id 1159256400
MySQL thread id 5, query id 210882 124.30.27.18 nitin.goel
---TRANSACTION 0 51201729, ACTIVE 65 sec, process no 7125, OS thread id 1164314960
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1216
MySQL thread id 9418, query id 208088 localhost 127.0.0.1 root Table lock
UPDATE `V4Core`.`Cities` AS V4Core_Cities SET `StateId` = null WHERE CityId = 19394
---TRANSACTION 0 51200228, ACTIVE 350 sec, process no 7125, OS thread id 1163250000
MySQL thread id 8379, query id 210662 localhost 127.0.0.1 root
---TRANSACTION 0 51198878, ACTIVE 676 sec, process no 7125, OS thread id 1164048720
mysql tables in use 1, locked 1
MySQL thread id 7114, query id 161516 localhost 127.0.0.1 root Table lock
UPDATE `V4Core`.`PersonNews` AS V4Core_PersonNews SET `Image` = null, `groupId` = 'testsc' WHERE PersonNewId = 22783
---TRANSACTION 0 51198356, ACTIVE 815 sec, process no 7125, OS thread id 1161652560
mysql tables in use 1, locked 1
MySQL thread id 6612, query id 151839 localhost 127.0.0.1 root Table lock
UPDATE `V4Core`.`PersonNews` AS V4Core_PersonNews SET `Image` = null, `groupId` = 'testsc' WHERE PersonNewId = 22783
---TRANSACTION 0 51193823, ACTIVE 2078 sec, process no 7125, OS thread id 1160055120 inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 368, undo log entries 1
MySQL thread id 232, query id 63926 124.30.27.18 nitin.goel update
INSERT INTO `CITIES`( `CityId`, `CityName`, `CityCode`, `StateId`, `DistrictId`, `TargetDataPorted`, `SourceCityId`, `DClientLocationId`, `IsApproved`, `FromPinCode`, `ToPinCode`, `IsKeyCity` ) VALUES ( 22827 , 'Test City' , 'test' , 19 , 616 , null , 2028166963 , null , null , null , null , null )
------- TRX HAS BEEN WAITING 2078 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 245778 n bits 144 index `PRIMARY` of table `v4core/states` trx id 0 51193823 lock mode S locks rec but not gap waiting
Record lock, heap no 20
------------------
---TRANSACTION 0 51193621, ACTIVE 2158 sec, process no 7125, OS thread id 1160853840
mysql tables in use 1, locked 1
42 lock struct(s), heap size 6752, undo log entries 452
MySQL thread id 2048, query id 63966 localhost 127.0.0.1 root Table lock
UPDATE `V4Core`.`PersonNews` AS V4Core_PersonNews SET `Name` = 'Jack Zeo', `groupId` = 'Test_1', `groupId1` = 'Test' WHERE PersonNewId = 21095
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
173991 OS file reads, 8274 OS file writes, 3259 OS fsyncs
32.06 reads/s, 19034 avg bytes/read, 7.37 writes/s, 2.69 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
3327 inserts, 3327 merged recs, 1333 merges
Hash table size 17393, used cells 11534, node heap has 29 buffer(s)
442.16 hash searches/s, 374.85 non-hash searches/s
---
LOG
---
Log sequence number 13 4217492506
Log flushed up to 13 4217492506
Last checkpoint at 13 4217492506
0 pending log writes, 0 pending chkp writes
2524 log i/o's done, 2.31 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 50069952; in additional pool allocated 1048576
Buffer pool size 512
Free buffers 0
Database pages 483
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 273868, created 48, written 5785
37.25 reads/s, 0.06 creates/s, 5.31 writes/s
Buffer pool hit rate 985 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 7125, id 1157658960, state: sleeping
Number of rows inserted 3679, updated 1297, deleted 88, read 25031128
1.87 inserts/s, 0.12 updates/s, 0.00 deletes/s, 608.02 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

mysql>


==================================================================================
mysql> SHOW CREATE TABLE PersonNews \G
*************************** 1. row ***************************
Table: PersonNews
Create Table: CREATE TABLE `personnews` (
`groupId` varchar(45) default NULL,
`PersonNewId` bigint(20) NOT NULL auto_increment,
`LName` varchar(254) default NULL,
`MName` varchar(254) default NULL,
`FName` varchar(254) default NULL,
`FatherName` varchar(254) default NULL,
`MotherName` varchar(254) default NULL,
`DateOfBirth` datetime default NULL,
`EmailId` varchar(254) default NULL,
`IdentityMark` varchar(254) default NULL,
`Name` varchar(254) default NULL,
`ShortName` varchar(70) default NULL,
`PANNumber` varchar(70) default NULL,
`MaritalStatusId` bigint(20) default NULL,
`BloodGroupId` bigint(20) default NULL,
`LanguageId` bigint(20) default NULL,
`ReligionId` bigint(20) default NULL,
`CasteId` bigint(20) default NULL,
`CityOfBirth` bigint(20) default NULL,
`CountryOfBirth` int(11) default NULL,
`GenderId` bigint(20) default NULL,
`NationaityId` bigint(20) default NULL,
`TargetDataPorted` tinyint(1) default NULL,
`SourcePersonId` bigint(20) default NULL,
`AddressLine1` varchar(500) default NULL,
`AddressLine2` varchar(500) default NULL,
`PhoneNo` varchar(255) default NULL,
`WeddingDate` datetime default NULL,
`CorrespondanceAddressLine1` varchar(255) default NULL,
`CorrespondanceAddressLine2` varchar(255) default NULL,
`CorrespondanceAddressContactNo` varchar(255) default NULL,
`CorrespondanceAddressPinCode` varchar(255) default NULL,
`PermanentAddressLine1` varchar(255) default NULL,
`PermanentAddressLine2` varchar(255) default NULL,
`PermanentAddressContactNo` varchar(255) default NULL,
`PermanentAddressPinCode` varchar(255) default NULL,
`CorrespondanceAddressCity` bigint(20) default NULL,
`PermanentAddressCity` bigint(20) default NULL,
`Salutation` bigint(20) default NULL,
`Fax` varchar(255) default NULL,
`ContactNo` varchar(255) default NULL,
`MobileNo` varchar(255) default NULL,
`Image` varchar(1024) default NULL,
`groupId1` varchar(45) default NULL,
`SameAsCorrespondenceAddressSameAsCorrespondenceAddress` tinyint(1) default NULL,
`CorrespondanceFAXNo` varchar(50) default NULL,
`PermanentFAXNo` varchar(50) default NULL,
`AreaOfInterest` varchar(100) default NULL,
`StateOfBirth` int(11) default NULL,
PRIMARY KEY (`PersonNewId`),
KEY `fk_35505` (`LanguageId`),
KEY `fk_35506` (`ReligionId`),
KEY `fk_35507` (`CasteId`),
KEY `fk_35503` (`MaritalStatusId`),
KEY `fk_35504` (`BloodGroupId`),
KEY `fk_36852` (`CountryOfBirth`),
KEY `fk_36851` (`CityOfBirth`),
KEY `fk_37066` (`NationaityId`),
KEY `fk_37065` (`GenderId`),
KEY `fk_1213456` (`CorrespondanceAddressCity`),
KEY `fk_1213457` (`PermanentAddressCity`),
KEY `fk_1213563` (`Salutation`),
KEY `fk_1225000` (`StateOfBirth`),
CONSTRAINT `fk_1213456` FOREIGN KEY (`CorrespondanceAddressCity`) REFERENCES `cities` (`CityId`),
CONSTRAINT `fk_1213457` FOREIGN KEY (`PermanentAddressCity`) REFERENCES `cities` (`CityId`),
CONSTRAINT `fk_1213563` FOREIGN KEY (`Salutation`) REFERENCES `salutations` (`Id`),
CONSTRAINT `fk_1225000` FOREIGN KEY (`StateOfBirth`) REFERENCES `states` (`StateId`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_35503` FOREIGN KEY (`MaritalStatusId`) REFERENCES `maritalstatuses` (`MaritalStatusId`),
CONSTRAINT `fk_35504` FOREIGN KEY (`BloodGroupId`) REFERENCES `bloodgroups` (`BloodGroupId`),
CONSTRAINT `fk_35505` FOREIGN KEY (`LanguageId`) REFERENCES `languages` (`LanguageId`),
CONSTRAINT `fk_35506` FOREIGN KEY (`ReligionId`) REFERENCES `religions` (`ReligionId`),
CONSTRAINT `fk_35507` FOREIGN KEY (`CasteId`) REFERENCES `castes` (`CasteId`),
CONSTRAINT `fk_36851` FOREIGN KEY (`CityOfBirth`) REFERENCES `cities` (`CityId`),
CONSTRAINT `fk_36852` FOREIGN KEY (`CountryOfBirth`) REFERENCES `countries` (`CountryId`),
CONSTRAINT `fk_37065` FOREIGN KEY (`GenderId`) REFERENCES `genders` (`Id`),
CONSTRAINT `fk_37066` FOREIGN KEY (`NationaityId`) REFERENCES `nationalities` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=24559 DEFAULT CHARSET=latin1
1 row in set (0.89 sec)

mysql> SHOW TABLE STATUS LIKE 'PersonNews' \G
*************************** 1. row ***************************
Name: personnews
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 11266
Avg_row_length: 234
Data_length: 2637824
Max_data_length: 0
Index_length: 3375104
Data_free: 0
Auto_increment: 24559
Create_time: 2009-04-14 03:32:49
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 893952 kB; (`CorrespondanceAddressCity`) REFER `v4core/cities`(`Cit
1 row in set (0.44 sec)

mysql> SHOW CREATE TABLE Cities \G
*************************** 1. row ***************************
Table: Cities
Create Table: CREATE TABLE `cities` (
`CityId` bigint(20) NOT NULL auto_increment,
`CityName` varchar(150) NOT NULL,
`CityCode` varchar(150) default NULL,
`StateId` int(11) NOT NULL,
`DistrictId` bigint(20) default NULL,
`TargetDataPorted` tinyint(1) default NULL,
`SourceCityId` bigint(20) default NULL,
`DClientLocationId` bigint(20) default NULL,
`IsApproved` tinyint(1) default NULL,
`FromPinCode` varchar(255) default NULL,
`ToPinCode` varchar(255) default NULL,
`IsKeyCity` tinyint(1) default NULL,
PRIMARY KEY (`CityId`),
KEY `fk_34630` (`StateId`),
KEY `fk_34631` (`DistrictId`),
CONSTRAINT `fk_34630` FOREIGN KEY (`StateId`) REFERENCES `states` (`StateId`),
CONSTRAINT `fk_34631` FOREIGN KEY (`DistrictId`) REFERENCES `districts` (`DistrictId`)
) ENGINE=InnoDB AUTO_INCREMENT=23773 DEFAULT CHARSET=latin1
1 row in set (0.44 sec)

mysql> SHOW TABLE STATUS LIKE 'Cities' \G
*************************** 1. row ***************************
Name: cities
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 16550
Avg_row_length: 96
Data_length: 1589248
Max_data_length: 0
Index_length: 2048000
Data_free: 0
Auto_increment: 23773
Create_time: 2009-05-09 02:52:09
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 893952 kB; (`StateId`) REFER `v4core/states`(`StateId`); (`District
1 row in set (0.45 sec)

mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------+---------+
| Variable_name | Value |
+-------------------------------+---------+
| bdb_log_buffer_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 8388600 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
+-------------------------------+---------+
13 rows in set (0.42 sec)

mysql>

Options: ReplyQuote


Subject
Written By
Posted
Insert/Update queries getting locked
October 07, 2009 05:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.