MYSQL数据库update和select命令之间死锁

Question

我在 mysql 数据库中遇到死锁情况。选择查询正在等待更新查询的锁定，更新查询正在等待选择查询的锁定。我在下面粘贴数据库死锁日志。任何人都可以查看日志并告诉我

1)为什么更新命令在只更新一个表(service_status)时需要锁定表(server_registry)

2)为什么select和insert命令会出现死锁。这两个命令需要使用不同的锁。 select需要使用读锁，update需要使用写锁。

请帮忙。提前致谢。

------------------------
LATEST DETECTED DEADLOCK
------------------------
140422 19:49:35
*** (1) TRANSACTION:
TRANSACTION 58C06, ACTIVE 1 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 320, 4 row lock(s)
MySQL thread id 808, OS thread handle 0x36fc, query id 707213 gemsoft 10.127.127.214 gemsoft Sending data
/* criteria query */ select this_.id as id53_1_, this_.creation_date as creation2_53_1_, this_.last_modified as last3_53_1_, this_.server_registry_id as server5_53_1_, this_.service_type as service4_53_1_, serverregi2_.id as id30_0_, serverregi2_.creation_date as creation2_30_0_, serverregi2_.last_modified as last3_30_0_, serverregi2_.is_active as is4_30_0_, serverregi2_.app_context as app5_30_0_, serverregi2_.ip_address as ip6_30_0_, serverregi2_.last_updated_batch_time as last7_30_0_, serverregi2_.is_moniter_server as is8_30_0_, serverregi2_.port_number as port9_30_0_ from service_status this_ left outer join server_registry serverregi2_ on this_.server_registry_id=serverregi2_.id where this_.service_type='MONITER_SERVICE'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 595 n bits 72 index `PRIMARY` of table `gemsoft31_08apr`.`server_registry` trx id 58C06 lock mode S locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000058b02; asc       ;;
 2: len 7; hex 2f00001e7d24f9; asc /   }$ ;;
 3: len 8; hex 800012514eb5acaa; asc    QN   ;;
 4: len 8; hex 800012514eb5e69e; asc    QN   ;;
 5: len 1; hex 00; asc  ;;
 6: len 5; hex 2f64636d61; asc /dcma;;
 7: len 12; hex 67616e657368767961733031; asc ganeshvyas01;;
 8: SQL NULL;
 9: len 1; hex 01; asc  ;;
 10: len 4; hex 38303830; asc 8080;;

*** (2) TRANSACTION:
TRANSACTION 58B02, ACTIVE 151 sec starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 2
MySQL thread id 813, OS thread handle 0xda8, query id 707229 gemsoft 10.127.127.214 gemsoft Updating
/* update com.gemsoft.dcma.da.domain.ServiceStatus */ update service_status set last_modified='2014-04-22 19:49:36', server_registry_id=2, service_type='MONITER_SERVICE' where id=3
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 595 n bits 72 index `PRIMARY` of table `gemsoft31_08apr`.`server_registry` trx id 58B02 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000058b02; asc       ;;
 2: len 7; hex 2f00001e7d24f9; asc /   }$ ;;
 3: len 8; hex 800012514eb5acaa; asc    QN   ;;
 4: len 8; hex 800012514eb5e69e; asc    QN   ;;
 5: len 1; hex 00; asc  ;;
 6: len 5; hex 2f64636d61; asc /dcma;;
 7: len 12; hex 67616e657368767961733031; asc ganeshvyas01;;
 8: SQL NULL;
 9: len 1; hex 01; asc  ;;
 10: len 4; hex 38303830; asc 8080;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 748 n bits 80 index `PRIMARY` of table `gemsoft31_08apr`.`service_status` trx id 58B02 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000003; asc         ;;
 1: len 6; hex 0000000584dc; asc       ;;
 2: len 7; hex 160000026015ff; asc     `  ;;
 3: len 8; hex 800012514eb5acb4; asc    QN   ;;
 4: len 8; hex 800012514eb5e253; asc    QN  S;;
 5: len 15; hex 4c4943454e53455f53455256494345; asc MONITER_SERVICE;;
 6: len 8; hex 8000000000000001; asc         ;;

*** WE ROLL BACK TRANSACTION (1)


Following sqls will help you in understanding table structure.


CREATE TABLE `server_registry` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `creation_date` DATETIME NOT NULL,
    `last_modified` DATETIME NULL DEFAULT NULL,
    `is_active` BIT(1) NULL DEFAULT NULL,
    `app_context` VARCHAR(255) NULL DEFAULT NULL,
    `ip_address` VARCHAR(255) NULL DEFAULT NULL,
    `last_updated_batch_time` DATETIME NULL DEFAULT NULL,
    `is_moniter_server` BIT(1) NULL DEFAULT b'0',
    `port_number` VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)


CREATE TABLE `service_status` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `creation_date` DATETIME NOT NULL,
    `last_modified` DATETIME NULL DEFAULT NULL,
    `service_type` VARCHAR(255) NULL DEFAULT NULL,
    `server_registry_id` BIGINT(20) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `FK8F8400BC7513AC46` (`server_registry_id`),
    CONSTRAINT `FK8F8400BC7513AC46` FOREIGN KEY (`server_registry_id`) REFERENCES `server_registry` (`id`)
)

Answer 1

mySql 锁定系统(用于 innodb)的工作原理如下:

FOR WRITE 写入操作需要行上的独占锁。同时它在相应的父行上获取包含锁。当它完成对子表的写操作时，它将父表的包含锁转换为排它锁。

FOR READ读取操作只需要对从索引获得的范围进行包含锁定，如果索引不正确，它将对整个表进行包含锁定。

问题可能是当 update 尝试将父表(即 server_registry 表锁)转换为独占锁时，select 尝试获取其对 server_registry 表的锁。