MySQL Forums
Forum List  »  InnoDB

locks in table
Posted by: Дмитрий ККК
Date: August 25, 2015 03:52AM

Have table:
CREATE TABLE `map_data2` (
`vehicleID` int(11) NOT NULL,
`eventDate` int(11) unsigned NOT NULL,
`recordType` tinyint(1) NOT NULL DEFAULT '0',
`latitude` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`longitude` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`refEventDate` int(11) unsigned NOT NULL,
`speed` decimal(10,3) NOT NULL DEFAULT '0.000',
`direction` smallint(3) DEFAULT NULL,
`isSpeeding` tinyint(1) unsigned NOT NULL DEFAULT '0',
`isIgnitionOn` tinyint(1) unsigned NOT NULL DEFAULT '0',
`isGSMOn` tinyint(1) unsigned NOT NULL DEFAULT '0',
`isRoamingOn` tinyint(1) unsigned NOT NULL DEFAULT '0',
`isGPSDataValid` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`vehicleID`,`eventDate`,`recordType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE COLUMNS(eventDate)
(PARTITION 2015_JUL VALUES LESS THAN (204940800) ENGINE = InnoDB,
PARTITION 2015_AUG VALUES LESS THAN (207619200) ENGINE = InnoDB,
PARTITION 2015_SEP VALUES LESS THAN (210297600) ENGINE = InnoDB,
PARTITION 2015_OCT VALUES LESS THAN (212889600) ENGINE = InnoDB,
PARTITION PMAX VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */ |

there is about 1 bln records there for now.

additionally: the "recordType" can have three values [-1,0,1] and has no need to be in key, cause the pair "vehicleID + eventdDate" is unique itself. but due to records count now it is problem to change primary key.

So problem is: sometimes we get locks in db while there are procedures of insert/delete executed:
mysql> select * from information_schema.innodb_locks;
+---------------------------+-------------+-----------+-----------+-------------------------------------------------------------+------------+------------+-----------+----------+-------------------------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------------------+-------------+-----------+-----------+-------------------------------------------------------------+------------+------------+-----------+----------+-------------------------+
| 14826966148:1759:270471:2 | 14826966148 | X | RECORD | `fas_map_processing`.`map_data2` /* Partition `2015_SEP` */ | PRIMARY | 1759 | 270471 | 2 | 203800031, 207856044, 1 |
| 14817536341:1759:270471:2 | 14817536341 | S | RECORD | `fas_map_processing`.`map_data2` /* Partition `2015_SEP` */ | PRIMARY | 1759 | 270471 | 2 | 203800031, 207856044, 1 |
+---------------------------+-------------+-----------+-----------+-------------------------------------------------------------+------------+------------+-----------+----------+-------------------------+


mysql> show processlist;
+--------+------+-----------------+----------------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------------+----------------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 130495 | root | localhost:40761 | fas_procedures | Query | 30 | updating | delete from fas_map_processing.map_data2 where vehicleID = pVehicleID and eventDate >= pStartDate |
+--------+------+-----------------+----------------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+


the delete/insert commands are not running in concurrency - only one of them at moment. insert is performed by batch from java code, and delete is performed in stored procedure.

So any ideas - what can generate such locks ?

Options: ReplyQuote


Subject
Views
Written By
Posted
locks in table
1695
August 25, 2015 03:52AM
692
August 27, 2015 03:17PM


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.