php简单操作mysql锁机制

8 篇文章 0 订阅

锁机制
共享锁与排他锁
共享锁(读锁):其他事务可以读,但不能写。
排他锁(写锁) :其他事务不能读取,也不能写。

对于MySQL来说,有三种锁的级别:页级、表级、行级

  • 页级的典型代表引擎为BDB。
  • 行级的典型代表引擎为INNODB。
  • 表级的典型代表引擎为MyISAM,MEMORY以及很久以前的ISAM。
  • BDB 存储引擎采用的是页面锁(page-level locking),但也支持表级锁
  • InnoDB 存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。
  • MyISAM 和 MEMORY 存储引擎采用的是表级锁

1.MyISAM 表锁

MyISAM表级锁模式:

  • 表共享读锁 (Table Read Lock):不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;
  • 表独占写锁 (Table Write Lock):会阻塞其他用户对同一表的读和写操作;

MyISAM加表锁方法:

  • 用 LOCK TABLE 命令给 MyISAM 表显式加锁
  • LOCK TABLES real_table (READ|WRITE), insert_table (READ|WRITE); //加锁
  • UNLOCK TABLES; //解锁

列子:
比如有 account(id,name,cash),hero(number,name,country)这两张表

  1. lock tables account read; 将account加为只读锁
    当前进程查询:select * from hero;会报Table ‘hero’ was not locked with LOCK TABLES。
    当前进程更改其他表:update hero set name=“ss” where number=1; 会报Table ‘hero’ was not locked with LOCK TABLES
    当前进程更改表:update account set name=“ssss” where id=1;会报Table ‘account’ was locked with a READ lock and can’t be updated
    如果别mysql进程进来,可以查询其他表和account,但不能更改account,会一直等待,需要释放锁才执行

1.1PHP操作

<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2021/4/29 0029
 * Time: 11:20
 */

$link = new mysqli('127.0.0.1', 'root', '123', 'db_school'); // 连接数据库
if(mysqli_connect_errno()){                                // 检查连接错误
    printf("连接失败:%s<br>", mysqli_connect_error());
    exit();
}

//(s1)
$table = "account";
$type = "read";
$sql1 = "LOCK TABLES $table $type";
$link->query($sql1);

//处理逻辑
//$sql1 = "select * from $table;";        //(s1)true
//$sql1 = "select * from hero;";        //false
//$sql1 = "update hero set name='ss' where number=1; ";     //false
$sql1 = "update account set name='ssss' where id=1;";   //false
$result = $link->query($sql1);
var_dump($result);


sleep(20);  //测试    //假设还没释放锁,开启cmd进mysql(s2)可以查询,但不能执行更改和删除操作,会等待这边释放锁
$link->query("unlock tables");    //取消全部的锁

//解锁后正常操作
//$result = $link -> query($sql1);
//var_dump($result);

$link->close();

查询表级锁争用情况
show status like ‘Table%’;

  • Table_locks_immediate 指的是能够立即获得表级锁的次数
  • Table_locks_waited 指的是不能立即获取表级锁而需要等待的次数

2.InnoDB加锁方法:

对于普通 SELECT 语句,InnoDB 不会加任何锁;只能在事务执行过程中使用加锁
锁只有在执行commit或者rollback的时候才会释放,并且所有的锁都是在同一时刻被释放。

  • 共享锁(S):SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。其他 session 仍然可以查询记录,并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作,则很有可能造成死锁。大家都能读,但是不能改,只有其中一个独占共享锁时候才能改;
  • 排他锁(X):SELECT * FROM table_name WHERE … FOR UPDATE。其他 session 可以查询该记录,但是不能对该记录加共享锁或排他锁,而是等待获得锁。我要改,你们都不能改,也不能读
    #select … lock in share mode //共享锁
    #select … for update //排他锁
    ##在 MySQL 8.0 中
  • 共享锁(S):SELECT * FROM table_name WHERE … FOR SHARE
  • 排他锁(X):SELECT * FROM table_name WHERE … FOR UPDATE[NOWAIT|SKIP LOCKED]
    –NOWAIT:发现有锁等待后会立即返回错误,不用等待锁超时后报错。
    –SKIP LOCKED:跳过被锁定的行,直接更新其他行,但是这样要注意是否会造成更新结果不符合预期。

2.1PHP操作

<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2021/4/29 0029
 * Time: 10:06
 */

$link = new mysqli('127.0.0.1', 'root', '123', 'db_school'); // 连接数据库
if(mysqli_connect_errno()){                                // 检查连接错误
    printf("连接失败:%s<br>", mysqli_connect_error());
    exit();
}
//案例1
$id = 1; //明确指定主键,并且有此数据,row lock (行锁)
//$id = -1;   //明确指定主键,若查无此数据,无lock (无锁)
$link->autocommit(0);                   // 开始事务(s1)
//FOR UPDATE仅适用于InnoDB,且必须在交易区块(BEGIN/COMMIT)中才能生效。
$sql = "select * from account where id=$id for update";
$link->query($sql);

/***
 * 此时其他mysql进程可以查询该记录,但是不能对该记录加共享锁或排他锁,而是等待获得锁。
 */
//(s1)可以进行更改,和查询等操作
//$sql1 = "update account set name='aaa' where id=$id;";  //进行更改
//$sql1 = "select * from account where id=$id;";  //进行查询
$sql1 = "delete from account where id=$id;";  //进行删除
$result = $link -> query($sql1);
var_dump($result);

sleep(20); //测试    //假设还在事务处理中,开启cmd进mysql(s2)执行更改和删除操作,会等待这边释放锁

$link->commit();
$link->close();

查看正在被锁定的的表
show OPEN TABLES where In_use > 0;
SHOW PROCESSLIST显示哪些线程正在运行。

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值