MySQL锁机制
MySQL锁机制
读/查：select
写的操作：insert,delete,update
锁粒度
表级锁：锁整个表，别人访问不了这张表。(myisam支持表级锁，编辑时默认锁整个表！)
行级锁：只锁当前被访问的行。(innodb支持行级锁，且每次只锁一行！如果锁多行，就会锁多次。如锁1-3行，就会锁3次: 锁第1行，再锁第2行，再锁第3行锁)
(select * from t1 where id<=3,如果客户端读表中的前3行时,就会锁前3行,别人是访问不了这3行的,只能访问其他行)
锁粒度：由"存储引擎"决定----->锁的"范围"(锁整个表,还是锁某行。myisam支持表级锁！innodb支持行级锁！)
锁类型：由"访问表的操作"来决定----->读锁(共享锁)(读操作时，支持并发读)，写锁(排它锁/互斥锁)(写操作时，上锁期间其他客户不能读表或写表)
事务：从开始连接到断开的过程
事务回滚：有任何一个步骤操作失败，它就回滚到操作失败前的状态。只有innodb支持事务回滚！
innodb会自动创建3个事务日志文件，存放位置/var/lib/mysql
ibdata1
ib_logfile0
ib_logfile1
mysql> show status like "%lock%"; 常看当前的锁状态，模糊查询。
+------------------------------------------+-------+
| Variable_name | Value |
+------------------------------------------+-------+
... ...
| Table_locks_immediate | 101 |
| Table_locks_waited | 0 | 当前正在等待解锁的表共有多少个
事务特性(ACID)
Atomic: 原子性(事务的整个操作是一个整体，不可分割，要么全部成功，要么全部失败)
Consistency: 一致性(事务操作的前后，表中的记录没有变化)
Isolation: 隔离性(事务操作是相互隔离不受影响的)
Durability: 持久性(数据一旦提交，不可改变，永久改变表数据)
#################################################################
mysql> show variables like "autocommit"; 查看提交状态
mysql> set autocommit=off; 关闭自动提交
mysql> rollback; 数据回滚
mysql> commit; 提交数据
在终端1操作：
# mysql -uroot -p123456
mysql> use db3;
mysql> create table t4(name char(5))engine=innodb; 指定存储引擎是innodb
mysql> show create table t4;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------+
| t4 | CREATE TABLE `t4` (
`name` char(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------+
mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| t1 |
| t2 |
| t3 |
| t4 |
+---------------+
---------------------------------------------------------------------------------
在终端2操作：
# mysql -uroot -p123456
mysql> use db3;
mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| t1 |
| t2 |
| t3 |
| t4 |
+---------------+
------------------------------------------------------------------------------------------
在终端1操作：
mysql> show variables like "%auto%";
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON | 系统默认自动提交，autocommit是on状态
| automatic_sp_privileges | ON |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_stats_auto_recalc | ON |
| sql_auto_is_null | OFF |
+-----------------------------+-------+
mysql> set autocommit=off; 把自动提交功能给关了
mysql> show variables like "%auto%";
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | OFF | 关闭自动提交，autocommit成为off状态
| automatic_sp_privileges | ON |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_stats_auto_recalc | ON |
| sql_auto_is_null | OFF |
+-----------------------------+-------+
---------------------------------------------------------------------------------------
在终端2操作：
mysql> show variables like "%auto%";
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON | 终端2不受影响，还是自动提交状态
| automatic_sp_privileges | ON |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_stats_auto_recalc | ON |
| sql_auto_is_null | OFF |
+-----------------------------+-------+
----------------------------------------------------------------------------------------
在终端1操作：
mysql> insert into t4 values("bob"); 插入表格
mysql> insert into t4 values("bob");
mysql> select * from t4; 还没有手动提交，但是自己可以看得见修改后的状态
+------+
| name |
+------+
| bob |
| bob |
+------+
-----------------------------------------------------------------------------------------------
在终端2操作：
mysql> select * from t4; 终端2是看不到的，因为终端1还没提交
Empty set (0.00 sec)
-----------------------------------------------------------------------------------------------
在终端1操作：
mysql> commit; 手动提交,如果命令是正确的，提交之后，就不能回滚
--------------------------------------------------------------------------------------
在终端2操作：
mysql> select * from t4; 终端2看得到终端1修改的结果了
+------+
| name |
+------+
| bob |
| bob |
+------+
------------------------------------------------------------------------------------------
在终端1操作：
mysql> delete from t4; 删除t4的内容，但是没有手动提交
mysql> select * from t4; 这边看到已删除
Empty set (0.00 sec)
-----------------------------------------------------------------------------------------------------
在终端2操作：
mysql> select * from t4; 终端2还能看到删除前的内容，因为终端1还没有提交
+------+
| name |
+------+
| bob |
| bob |
+------+
----------------------------------------------------------------------------------------
在终端1操作：
mysql> rollback; 事务回滚。后悔了，不想删除了
mysql> select * from t4; 回到原来状态，没有删除
+------+
| name |
+------+
| bob |
| bob |
+------+
---------------------------------------------------------------------------------------------------------------------
mysql> delete from t4;
Query OK, 2 rows affected (0.00 sec)
mysql> commit; 再次删除后，手动提交
Query OK, 0 rows affected (0.07 sec)
mysql> select * from t4; 删除成功，t4里面已经没有内容了
Empty set (0.00 sec)
----------------------------------------------------------------------------------
在终端2操作：
mysql> select * from t4; 终端2也看不到了
Empty set (0.00 sec)
--------------------------------------------------------------------------------------
在终端1操作：
mysql> set autocommit=on; 打开自动提交功能
mysql> show variables like "%auto%";
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_stats_auto_recalc | ON |
| sql_auto_is_null | OFF |
+-----------------------------+-------+
