MYSQL database deadlock between update and select command -

i getting deadlock situation in mysql db. select query waiting lock hold update query , update query waiting lock hold select query. pasting db deadlock logs below. please go through logs , tell me

1) why update command needs lock on table(server_registry) when updatinng 1 table(service_status)

2) why there deadlock bwteen select , insert command.both of them needs use different locks. select needs use read lock , update needs use write lock.

please help. in advance.

------------------------ 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 id53_1_, this_.creation_date creation2_53_1_, this_.last_modified last3_53_1_, this_.server_registry_id server5_53_1_, this_.service_type service4_53_1_, serverregi2_.id id30_0_, serverregi2_.creation_date creation2_30_0_, serverregi2_.last_modified last3_30_0_, serverregi2_.is_active is4_30_0_, serverregi2_.app_context app5_30_0_, serverregi2_.ip_address ip6_30_0_, serverregi2_.last_updated_batch_time last7_30_0_, serverregi2_.is_moniter_server is8_30_0_, serverregi2_.port_number port9_30_0_ service_status this_ left outer join server_registry serverregi2_ on this_.server_registry_id=serverregi2_.id this_.service_type='moniter_service' *** (1) waiting lock 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 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' id=3 *** (2) holds 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 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 lock 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 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         ;;  *** roll transaction (1)   following sqls 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`) ) 

mysql locking system (for innodb) works :

for write write operations needs exclusive lock on rows. @ same takes inclusive lock on corresponding parent rows. when finishes write operation on child converts inclusive lock of parent table exclusive lock.

for read read operation needs inclusive lock on range got index , if index not proper make inclusive lock on whole table.

problem may when update trying convert parent table i.e. server_registry table lock exclusive lock @ time select trying lock on server_registry table.