• mysql 死锁分析

    表结构:

    CREATE TABLE `se_exclude_keyword` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `se_code` VARCHAR(200) NOT NULL COMMENT 'SE编码' COLLATE 'utf8_bin',
    `se_account_id` BIGINT(20) NOT NULL COMMENT 'SE账户ID',
    `campaign_id` BIGINT(20) NULL DEFAULT NULL COMMENT '活动主键ID',
    `adgroup_id` BIGINT(20) NULL DEFAULT NULL COMMENT '广告组主键ID',
    `keyword_name` VARCHAR(300) NULL DEFAULT NULL COMMENT '关键词' COLLATE 'utf8_bin',
    `exclude_type` BIGINT(20) NULL DEFAULT NULL COMMENT '否定类型:0非精确,1广泛、2短语、3精确',
    `exclude_range` VARCHAR(200) NULL DEFAULT NULL COMMENT '否定范围: campaign 活动、adgroup 单元' COLLATE 'utf8_bin',
    `create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` DATETIME NULL DEFAULT NULL COMMENT '更新时间',
    `create_user` VARCHAR(300) NULL DEFAULT NULL COMMENT '创建人' COLLATE 'utf8_bin',
    `update_user` VARCHAR(300) NULL DEFAULT NULL COMMENT '更新人' COLLATE 'utf8_bin',
    `del` INT(11) NULL DEFAULT NULL COMMENT '逻辑删除标记 1 已删除 0 正常',
    `operate_code` BIGINT(20) NOT NULL COMMENT '操作类型:   1 update 2 create 3 delete',
    `submited` INT(11) NULL DEFAULT NULL COMMENT '操作是否提交 1 是 0 否',
    `project_id` BIGINT(20) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `idx_sek_se_account_id` (`se_account_id`),
    INDEX `idx_sek_campaign_id` (`campaign_id`),
    INDEX `idx_sek_project_id` (`project_id`),
    INDEX `idx_sek_adgroup_id` (`adgroup_id`)
    )
    COMMENT=' 活动、单元的否定关键词;同一条记录活动ID和广告组ID不能同时存在'
    COLLATE='utf8_bin'
    ENGINE=InnoDB
    
    出现死锁的情况应该属于以下情况:
    相同索引键来访问即使是不同行记录,出现锁冲突。

    ————————
    LATEST DETECTED DEADLOCK
    ————————
    2014-04-25 06:30:06 7efd9f7af700
    *** (1) TRANSACTION:
    TRANSACTION 659049795, ACTIVE 0 sec starting index read
    mysql tables in use 4, locked 4
    LOCK WAIT 5 lock struct(s), heap size 1248, 4 row lock(s)
    MySQL thread id 887239, OS thread handle 0x7efd9fc41700, query id 749922319 192.168.4.195 ttt Sending data
    delete sek from se_exclude_keyword sek where se_code = ‘baidu’ and se_account_id = 1343 and project_id = 277 and exists (select 1 from se_campaign where id = sek.campaign_id and se_campaign_id=14726849) and sek.exclude_range = ‘campaign’ and sek.submited = 1
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 348 page no 7849 n bits 280 index `idx_sek_project_id` of table `sem3_prd_new`.`se_exclude_keyword` trx id 659049795 lock_mode X waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
    0: len 8; hex 8000000000000115; asc ;;
    1: len 8; hex 8000000000001018; asc ;;

    *** (2) TRANSACTION:
    TRANSACTION 659049793, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 2043
    mysql tables in use 4, locked 4
    147 lock struct(s), heap size 31160, 15940 row lock(s)
    MySQL thread id 887225, OS thread handle 0x7efd9f7af700, query id 749922318 192.168.4.195 ttt Sending data
    delete sek from se_exclude_keyword sek where se_code = ‘baidu’ and se_account_id = 20463 and project_id = 277 and exists (select 1 from se_campaign where id = sek.campaign_id and se_campaign_id=13824923) and sek.exclude_range = ‘campaign’ and sek.submited = 1
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 348 page no 7849 n bits 280 index `idx_sek_project_id` of table `sem3_prd_new`.`se_exclude_keyword` trx id 659049793 lock_mode X
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
    0: len 8; hex 73757072656d756d; asc supremum;;

    Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
    0: len 8; hex 8000000000000115; asc ;;
    1: len 8; hex 8000000000001018; asc ;;

    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 348 page no 3156 n bits 192 index `PRIMARY` of table `se_exclude_keyword` trx id 659049793 lock_mode X locks rec but not gap waiting
    Record lock, heap no 39 PHYSICAL RECORD: n_fields 18; compact format; info bits 0
    0: len 8; hex 800000000002b69a; asc ;;
    1: len 6; hex 0000256ba525; asc %k %;;
    2: len 7; hex ad000002f80110; asc ;;
    3: len 5; hex 6261696475; asc baidu;;
    4: len 8; hex 800000000000053f; asc ?;;
    5: len 8; hex 8000000000ce1d63; asc c;;
    6: SQL NULL;
    7: len 6; hex e9a291e98193; asc ;;
    8: len 8; hex 8000000000000000; asc ;;
    9: len 8; hex 63616d706169676e; asc campaign;;
    10: len 8; hex 800012514ec325ee; asc QN % ;;
    11: len 8; hex 800012514ec325ee; asc QN % ;;
    12: len 5; hex 5345415049; asc SEAPI;;
    13: len 5; hex 5345415049; asc SEAPI;;
    14: len 4; hex 80000000; asc ;;
    15: len 8; hex 8000000000000000; asc ;;
    16: len 4; hex 80000001; asc ;;
    17: len 8; hex 8000000000000115; asc ;;

    *** WE ROLL BACK TRANSACTION (1)

     

    Innodb的行锁是根据检索的索引项进行加锁(注意不要理解成对索引加锁),具体的说是对所有根据索引检索到的记录进行加锁(由于Innodb使用主键为聚簇索引的方式,也可以理解为对主键进行加锁,Innodb总会有聚簇索引的,即使没有也会隐式生成一个),这样就决定了Innodb的行锁会有下面一些性质:

    • 只有通过索引检索,才能使用行锁,否则使用表锁
    • 使用相同索引键来访问即使是不同行记录,也会出现锁冲突。(这里指的是需要锁的情况下。在非一致性锁定读情况下,即使读取的行已经被使用Select … For Update,也是可以读的(因为读的是快照))
    • 间隙锁,范围检索以及对不存在记录相等条件的检索都会使用,所以应该在设计上尽量避免,避免导致严重的锁等待

    以下情况会使用表锁:

    • 没有使用索引进行检索
    • 判断不同执行计划的代价,如果全表扫描效率更高,也会使用表锁
    • 数据类型转化而导致表锁。
  • OpenSSL 版本 CVE-2014-0160 心血漏洞 对 https 服务nginx的影响和修复

    关于 OpenSSL1.0.1版本 CVE-2014-0160 心血漏洞 对 https 服务的影响和修复

    漏洞描述:
    http://www.techweb.com.cn/ucweb/news/id/2025856

    漏洞检查:
    http://wangzhan.360.cn/heartbleed

    漏洞解决:

    我们使用的主要是 nginx 的 https 服务,因此需要针对 nginx进行处理,
    对操作系统 比如 centos 打补丁只能解决系统本身的ssl相关问题
    使用 OpenSSL 1.0.1g (不受影响) 重新编译 nginx 即可。

    wget ftp://ftp.csx.cam.ac.uk/pub/software/programming/pcre/pcre-8.35.tar.gz
    wget http://zlib.net/zlib-1.2.8.tar.gz
    wget ftp://ftp.openssl.org/source/openssl-1.0.1g.tar.gz
    tar -xf pcre-8.35.tar.gz
    tar -xf zlib-1.2.8.tar.gz
    tar -xf openssl-1.0.1g.tar.gz
    tar -xf nginx-1.4.7.tar.gz

    cd nginx-1.4.7

    # 编译依赖的lib需要源代码目录
    ./configure –prefix=/data/lib/nginx-1.4.7 –sbin-path=/data/lib/nginx-1.4.7/sbin/nginx –conf-path=/data/lib/nginx-1.4.7/conf/nginx.conf –error-log-path=/data/lib/nginx-1.4.7/logs/error.log –pid-path=/data/lib/nginx-1.4.7/var/nginx.pid –lock-path=/data/lib/nginx-1.4.7/var/nginx.lock –http-log-path=/data/lib/nginx-1.4.7/logs/access.log –http-client-body-temp-path=/data/lib/nginx-1.4.7/temp/client_body_temp –http-proxy-temp-path=/data/lib/nginx-1.4.7/temp/proxy_temp –http-fastcgi-temp-path=/data/lib/nginx-1.4.7/temp/fastcgi_temp –http-uwsgi-temp-path=/data/lib/nginx-1.4.7/temp/uwsgi_temp –http-scgi-temp-path=/data/lib/nginx-1.4.7/temp/scgi_temp –with-ipv6 –with-pcre=/data/lib/src/pcre-8.35 –with-openssl=/data/lib/src/openssl-1.0.1g –with-zlib=/data/lib/src/zlib-1.2.8 –with-http_ssl_module –with-http_realip_module –with-http_addition_module –with-http_sub_module –with-http_dav_module –with-http_flv_module –with-http_gzip_static_module –with-http_random_index_module –with-http_secure_link_module –with-http_degradation_module –with-http_stub_status_module
    make
    make install

    mkdir /data/lib/nginx-1.4.7/temp -p

    # 复制旧版本配置文件
    cp -R /data/lib/nginx/conf/nginx.conf /data/lib/nginx-1.4.7/conf/
    cp -R /data/lib/nginx/conf/ssl /data/lib/nginx-1.4.7/conf/

    # 更新软连接并重启
    ./nginx/sbin/nginx -s stop
    rm -f nginx
    ln -s nginx-1.4.7 nginx
    ./nginx/sbin/nginx

    重启后使用
    http://wangzhan.360.cn/heartbleed
    再次检测