Not the answer you need?
Register and ask your own question!

mysql 5.1 deadlock on session table

TirexTirex EntrantCurrent User Role Beginner
I need help of community, can't understand mysql update principles and cause of deadlocks in my database.

Why mysql dont use intersection of indexes in locked query for security.session?
if i run this locked query as select it affect less rows than update.

How i can check with explain or some else command how many rows would be affect during update, and what indexses are locked?
Thanks.


--- tech spec
select version()
5.1.66-0+squeeze1-log
---
*** (1) TRANSACTION:
TRANSACTION 1 3503488031, ACTIVE 0 sec, process no 19046, OS thread id 1674599280 starting index read
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1024, 3 row lock(s)
MySQL thread id 64523, query id 470413769 n1-rest 192.168.3.65 secsys Updating
UPDATE session SET closed='2015-11-21 12:04:55', state_id=2 WHERE (((session.CLIENT = 530181) and (session.STATE_ID = 1)) and (session.PORTAL = 2))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 941 page no 16017 n bits 128 index `idx_sesson_portal` of table `security`.`session` trx id 1 3503488031 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;; 1: len 8; hex 800000000000a9f4; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 1 3503488006, ACTIVE 1 sec, process no 19046, OS thread id 1789717360 fetching rows, thread declared inside InnoDB 492
mysql tables in use 3, locked 3
3247 lock struct(s), heap size 224576, 273927 row lock(s)
MySQL thread id 64534, query id 470413467 n1-rest 192.168.3.65 secsys Updating
UPDATE session SET closed='2015-11-21 12:04:54', state_id=2 WHERE (((session.CLIENT = 246709) and (session.STATE_ID = 1)) and (session.PORTAL = 2))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 941 page no 16017 n bits 128 index `idx_sesson_portal` of table `security`.`session` trx id 1 3503488006 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;; 1: len 8; hex 800000000000a9f4; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 941 page no 6493 n bits 200 index `PRIMARY` of table `security`.`session` trx id 1 3503488006 lock_mode X locks rec but not gap waiting
Record lock, heap no 120 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
 0: len 8; hex 80000000000b1083; asc         ;; 1: len 6; hex 0001c027a19c; asc    '  ;; 2: len 7; hex 80000003d42839; asc      (9;; 3: len 8; hex 8000000000081705; asc         ;; 4: len 8; hex 80001253a2a2be20; asc    S    ;; 5: len 4; hex 5534cd72; asc U4 r;; 6: len 30; hex 39336361356662392d613961382d346662362d623234362d323936646535; asc 93ca5fb9-a9a8-4fb6-b246-296de5;...(truncated); 7: len 8; hex 8000000000000002; asc         ;; 8: len 8; hex 80000000000bda31; asc        1;; 9: len 4; hex 5534ce3f; asc U4 ?;; 10: len 4; hex 80000002; asc     ;; 11: len 8; hex 8000000000000002; asc         ;; 12: SQL NULL;

*** WE ROLL BACK TRANSACTION (1)
show create table security.session

CREATE TABLE `session` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `CLIENT` bigint(20) DEFAULT NULL,
  `CLOSED` datetime DEFAULT NULL,
  `CREATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `REFERENCE` varchar(255) DEFAULT NULL,
  `STATE_ID` bigint(20) DEFAULT NULL,
  `AUTH_ID` bigint(20) DEFAULT NULL,
  `UPDATED` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `PORTAL` int(11) DEFAULT '1',
  `BRAND_ID` bigint(20) NOT NULL,
  `LOCALE` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `FK_SESSION_AUTH_ID` (`AUTH_ID`),
  KEY `FK_SESSION_STATE_ID` (`STATE_ID`),
  KEY `FK_SESSION_REFERENCE` (`REFERENCE`),
  KEY `idx_sesson_portal` (`PORTAL`),
  KEY `idx_session_client` (`CLIENT`),
  KEY `idx_session_brand_id` (`BRAND_ID`),
  CONSTRAINT `FK_SESSION_AUTH_ID` FOREIGN KEY (`AUTH_ID`) REFERENCES `auth` (`ID`),
  CONSTRAINT `FK_SESSION_STATE_ID` FOREIGN KEY (`STATE_ID`) REFERENCES `sessionstate` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1504216 DEFAULT CHARSET=utf8
select count(*) from security.session
742335
show variables like '%isola%';
tx_isolation    READ-COMMITTED
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.