MYSQL事务

常用语句

show create table 表名;  // 查看表结构，表引擎
show open tables ; // 查看表锁状态
lock table 表名 READ; // 添加读锁,阻塞所有的update操作
unlock tables 解锁
lock table 表名 WRITE ; // 添加独占写锁，阻塞其他用户的update操作,其他回话中无法读

MYSQL事务，锁表

SELECT
    trx_id AS  `事务ID`,
    trx_state AS `事务状态`,
    trx_requested_lock_id  AS  `事务需要等待的资源`,
    trx_wait_started    AS  `事务开始等待时间`,
    trx_tables_in_use AS `事务使用表`,
    trx_tables_locked AS `事务拥有锁`,
    trx_rows_locked  AS `事务锁定行`,
    trx_rows_modified  AS `事务更改行`
FROM
    information_schema.innodb_trx ;

SELECT
    lock_id         AS  `锁ID`,
    lock_trx_id    AS  `拥有锁的事务ID`,
    lock_mode      AS  `锁模式    `,
    lock_type      AS  `锁类型`,
    lock_table         AS  `被锁的表`,
    lock_index         AS  `被锁的索引`,
    lock_space         AS  `被锁的表空间号`,
    lock_page      AS  `被锁的页号`,
    lock_rec       AS  `被锁的记录号`,
    lock_data      AS  `被锁的数据`
FROM
    information_schema.innodb_locks;

SELECT
  requesting_trx_id   AS  `请求锁的事务ID`,
  requested_lock_id   AS  `请求锁的锁ID`,
  blocking_trx_id     AS  `当前拥有锁的事务ID`,
  blocking_lock_id    AS  `当前拥有锁的锁ID`
FROM
  innodb_lock_waits;

事务控制语句

begin,start transaction 显示开启一个事务
commit,commit work 提交事务
rollback rollback work 回滚数据，结束事务
save point identifier 创建一个保存点
release savepoint identifier 删除一个保存点
rollback to identifier 事务回滚到指定保存点
set transaction 设置事务隔离级别
set autocommit 自动提交默认1，如果不开启，每一个sql都会作为事务，直到输入commit

事务的隔离级别

锁

1.myisam 只能使用表级锁，table read lock ,table write lock
2.myisam在执行查询语句之前，会自动给涉及的所有表加读锁，在执行更新操作时会自动给涉及所有表加写锁
3.读锁（共享锁）,写锁 (排他锁)

读锁和写锁

1.select ... :快照读,不加锁
2.select ... in share mode: 当前读,加读锁
3.select ... for update : 当前读,加写锁