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