荣耀彩票代理

IT技术互动交流平台

mysql 行级锁的使用以及死锁的预防

作者:StudyBlog  来源:IT165收集  发布日期:2016-12-22 20:36:31

 一、前言

    mysqlDEInnoDB,ZHICHISHIWUHEXINGJISUO,KEYISHIYONGXINGSUOLAICHULIYONGHUTIXIANDENGYEWU。SHIYONGmysqlSUODESHIHOUYOUSHIHOUHUICHUXIANSISUO,YAOZUOHAOSISUODEYUFANG。

  二、MySQL行级锁

    XINGJISUOYOUFENGONGXIANGSUOHEPAITASUO。

    共享锁:

      MINGCIJIESHI:GONGXIANGSUOYOUJIAOZUODUSUO,SUOYOUDESHIWUZHINENGDUIQIJINXINGDUCAOZUOBUNENGXIECAOZUO,JIASHANGGONGXIANGSUOHOUQITASHIWUBUNENGZAIJIAPAITASUOLEZHINENGJIAXINGJISUO。

      YONGFA:SELECT `id` FROM  table WHERE id in(1,2)   LOCK IN SHARE MODE JIEGUOJIDESHUJUDOUHUIJIAGONGXIANGSUO

    排他锁:

      荣耀彩票代理名词解释:若某个事物对某一行加上了排他锁,只能这个事务对其进行读写,其他事务不能对其进行加任何锁,其他进程可以读取,不能进行写操作,需等待其释放。

荣耀彩票代理      YONGFA:SELECT `id` FROM mk_user WHERE id=1 FOR UPDATE

  三、实例应用

<?php
        $uid=$_SESSION['uid'];
        //开启事务
        sql:begin
        //开启行级锁的排他锁
        sql:SELECT `coin` FROM user WHERE id=$uid FOR UPDATE 
        //扣除用户账户钱币
        $res=update user set coin=coin-value where id=1;
        if($res){
            //将用户的提现信息添加到提现表
            sql:insert into user values(null,'{$uid}',value);
            //判断添加结果
            if(add_cash_result){
                sql:commit
            }else{
                sql:rollback
            }
        }else{
            sql:rollback;
        }

荣耀彩票代理    QISHIBUZHOUBUFUZA,JIUSHIKAIQISHIWUPANDUANGEGEJIEGUOWEIZHENJIUTIJIAOWEIJIAJIUHUIGUN。DANGEPAITASUOMEIYOUSHENMEWENTI,DANGYIGEBIAOGUANLIANDAODUOGEPAITASUODESHIHOUYAOZHUYIFANGZHIFASHENGSISUO。

  四、死锁

荣耀彩票代理    `id`  ZHUJIANSUOYIN

    `name` index SUOYIN

    `age`  PUTONGZIDUAN

    SISUOCHANSHENGDEGENBENYUANYINSHILIANGGEYISHANGDEJINCHENGDOUYAOQIUDUIFANGSHIFANGZIYUAN,YIZHIYUJINCHENGDOUYIZHIDENGDAI。ZAIDAIMASHANGSHIYINWEILIANGGEHUOZHEYISHANGDESHIWUDOUYAOQIULINGYIGESHIFANGZIYUAN。

荣耀彩票代理    SISUOCHANSHENGDESIGEBIYAOTIAOJIAN:HUCHITIAOJIAN、HUANLUTIAOJIAN、QINGQIUBAOCHI、BUKEBODUO,QUEYIBUKE,XIANGDUIYINGDEZHIYAOPOHUAIQIZHONGYIZHONGTIAOJIANSISUOJIUBUHUICHANSHENG。

荣耀彩票代理    LIRUXIAMIANLIANGTIAOYUJU DIYITIAOYUJUHUIYOUXIANSHIYONG`name`SUOYIN,YINWEInameBUSHIZHUJIANSUOYIN,HAIHUIYONGDAOZHUJIANSUOYIN

    DIERTIAOYUJUSHISHOUXIANSHIYONGZHUJIANSUOYIN,ZAISHIYONGnameSUOYIN RUGUOLIANGTIAOYUJUTONGSHIZHIXING,DIYITIAOYUJUZHIXINGLEnameSUOYINDENGDAIDIERTIAOSHIFANGZHUJIANSUOYIN,DIERTIAOZHIXINGLEZHUJIANSUOYINDENGDAIDIYITIAODEnameSUOYIN,ZHEIYANGJIUZAOCHENGLESISUO。

    解决方法:改造第一条语句 使其根据主键值进行更新
    

#①
update mk_user set name ='1' where `name`='idis12';
#②
update mk_user set name='12'  where id=12;
//改造后
update mk_user set name='1' where id=(select id from mk_user where name='idis12' );

  

Tag标签:   
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规