Skip to content

Instantly share code, notes, and snippets.

@isotopp
Last active August 2, 2020 20:25
Show Gist options
  • Save isotopp/c3b5a7438545a9c43355bfc5eb3d4df4 to your computer and use it in GitHub Desktop.
Save isotopp/c3b5a7438545a9c43355bfc5eb3d4df4 to your computer and use it in GitHub Desktop.
with referential integrity we get an s-lock explosion.
CREATE TABLE `c` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`parent` bigint unsigned DEFAULT NULL,
UNIQUE KEY `id` (`id`),
KEY `parent` (`parent`),
CONSTRAINT `c_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `c` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
INSERT INTO `c` VALUES (1,NULL),(2,1),(3,1),(4,2),(5,2),(6,3),(7,3),(8,3);
start transaction read write;
select * from c where id = 8 for update;
select object_name, index_name, lock_type, lock_mode, lock_data, lock_status from performance_schema.data_locks;
update c set id=9 where id =8;
select object_name, index_name, lock_type, lock_mode, lock_data, lock_status from performance_schema.data_locks;
@CyberBLN
Copy link

CyberBLN commented Aug 2, 2020

SELECT ... FOR UPDATE sets an intention exclusive lock (IX) on table level and exclusive lock (X) on row level (your table 1). Until you not UPDATE it is not known which gaps and next keys have to be locked additional to the already exclusive locked row identified by its id. The S locks are only required because you UPDATE the PK column and you have a FKC between parent and id.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment