mysql select语句造成死锁

zimeng0

WGY20051896 发表于 2017-2-7 22:24
你事务什么隔离级别，不是serialize吧，可以把配置文件贴出来。
第二，你多次运行show engine innodb stat ...

隔离级别是可重复读，是我手动设置的。多次show engine innodb status也还是出现同样的select问题，我已经尝试过很很多次了。
auto_increment_increment = 1
auto_increment_offset = 1
back_log = 3000
binlog_cache_size = 128KB
binlog_checksum = CRC32
binlog_row_image = full
binlog_stmt_cache_size = 32768
character_set_server = utf8mb4
concurrent_insert = 1
connect_timeout = 10
default_storage_engine = InnoDB
default_time_zone = SYSTEM
default_week_format = 0
delayed_insert_limit = 100
delayed_insert_timeout = 300
delayed_queue_size = 1000
delay_key_write = ON
div_precision_increment = 4
eq_range_index_dive_limit = 10
ft_min_word_len = 4
ft_query_expansion_limit = 20
group_concat_max_len = 1024
innodb_adaptive_hash_index = ON
innodb_additional_mem_pool_size = 2097152
innodb_autoinc_lock_mode = 1
innodb_concurrency_tickets = 5000
innodb_ft_max_token_size = 84
innodb_ft_min_token_size = 3
innodb_large_prefix = OFF
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 75
innodb_old_blocks_pct = 37
innodb_old_blocks_time = 1000
innodb_online_alter_log_max_size = 134217728
innodb_open_files = 3000
innodb_print_all_deadlocks = OFF
innodb_purge_batch_size = 300
innodb_purge_threads = 1
innodb_read_ahead_threshold = 56
innodb_read_io_threads = 4
innodb_rollback_on_timeout = OFF
innodb_stats_method = nulls_equal
innodb_stats_on_metadata = OFF
innodb_stats_sample_pages = 8
innodb_strict_mode = OFF
innodb_table_locks = ON
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 10000
innodb_write_io_threads = 4
interactive_timeout = 120
key_cache_age_threshold = 300
key_cache_block_size = 1024
key_cache_division_limit = 100
log_queries_not_using_indexes = OFF
long_query_time = 2
loose_max_statement_time = 0
loose_rds_max_tmp_disk_space = 10737418240
loose_rds_tablestat = OFF
loose_rds_threads_running_high_watermark = 50000
loose_tokudb_buffer_pool_ratio = 0
low_priority_updates = 0
max_allowed_packet = 1024M
max_connect_errors = 100
max_length_for_sort_data = 1024
max_prepared_stmt_count = 16382
max_write_lock_count = 102400
myisam_sort_buffer_size = 262144
net_read_timeout = 30
net_retry_count = 10
net_write_timeout = 60
open_files_limit = 65535
performance_schema = OFF
query_alloc_block_size = 8192
query_cache_limit = 1048576
query_cache_size = 3145728
query_cache_type = 0
query_prealloc_size = 8192
rds_reset_all_filter = 0
rds_sql_select_filter = ''
slow_launch_time = 2
sql_mode =
table_definition_cache = 512
table_open_cache = 2000
tmp_table_size = 2097152
transaction_isolation = REPEATABLE-READ
wait_timeout = 120
explicit_defaults_for_timestamp = false
loose_rds_indexstat = OFF
thread_stack = 262144

WGY20051896

zimeng0 发表于 2017-2-8 10:29
隔离级别是可重复读，是我手动设置的。多次show engine innodb status也还是出现同样的select问题，我已 ...

hi

第一，按照你的配置来看，select是不会占有锁的。

第二，图片中给出的select语句和附件中给出的select语句，不是完全相同的，起码值不一样。
因此，不能说是一个select语句，引起死锁
SELECT
                `leftamt`,`leftnum`,`alloct`,`key`,num
                FROM bu_shop_redpackets
                 WHERE rpid = 97919154262253568
                        AND
                        sta = 4
                       
                        AND
                        start<1485239953297
                        AND
                        1485239953297<end
                       
                        AND leftnum>0

第三，为了搞清楚整个事情的原因，你需要把该select语句，所在事务中的所有语句，都整理出来。这样才能知道死锁究竟是怎么产生的。

zimeng0

WGY20051896 发表于 2017-2-8 17:31
hi

第一，按照你的配置来看，select是不会占有锁的。

已经查到了，是一条update语句阻塞了select操作，把update语句优化了一下就好了。谢谢。