MySQL概述及入门(四)
MySql概述及入门(四)
什么是MySQL的锁?
数据库锁定机制，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则，简单说，就是不让别人动
总的来说，MySQL各存储引擎使用了三种类型（级别）的锁定机制：行级锁定，页级锁定和表级锁定。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。使用页级锁定的主要是BerkeleyDB存储引擎
* 按照锁的粒度来分：行级锁和表级锁
-
行级锁时，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
- 最大程度的支持并发，同时也带来了最大的锁开销。
- 在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。
- 行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统
-
表级锁时，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
- 这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
- 表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用
* 按照锁的功能来分：共享读锁和排他写锁
-
共享锁时，别人能读，不能改变量表数据
-
排它锁时，别人既不能读，也不能改表数据
* 悲观锁（排他写锁）和乐观锁（使用某一版本列或者唯一列进行逻辑控制）
MySQL 实现的表级锁定的争用状态变量：show status like 'table%'
* table_locks_immediate：产生表级锁定的次数；
* table_locks_waited：出现表级锁定争用而发生等待的次数；
l 手动增加表锁
lock table 表名称 read(write),表名称2 read(write)，其他;
l 查看表锁情况
show open tables;
l 删除表锁
unlock tables;
MySQL如何加锁?
l 建表语句
CREATE TABLE `mylock` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`) );
l 添加记录
INSERT INTO mylock (id,NAME) VALUES (1, 'a');
INSERT INTO mylock (id,NAME) VALUES (2, 'b');
INSERT INTO mylock (id,NAME) VALUES (3, 'c');
INSERT INTO mylock (id,NAME) VALUES (4, 'd');
l 读锁演示
l 写锁演示
InnoDB引擎的锁机制
InnoDB 实现了以下两种类型的行锁：
- 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
- 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：
- 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
- 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。
l Innodb所使用的行级锁定争用状态查看：show status like 'innodb_row_lock%';
* Innodb_row_lock_current_waits：当前正在等待锁定的数量；
* Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
* Innodb_row_lock_time_avg：每次等待所花平均时间；
* Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
* Innodb_row_lock_waits：系统启动后到现在总共等待的次数；
死锁
死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。
锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。
InnoDB避免死锁：
在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
改变事务隔离级别
集群搭建之主从复制?
主从复制原理
查看binlog日志： mysqlbinlog 文件名称
主服务器配置
l 第一步：修改my.conf文件：在[mysqld]段下添加：
#启用二进制日志
log-bin=mysql-bin
#服务器唯一ID，一般取IP最后一段
server-id=133
l 第二步：重启mysql服务
service mysqld restart
l 第三步：建立帐户并授权slave（登录到MySQL）
mysql>GRANT FILE ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'root'@'%' identified by 'root';
mysql>GRANT REPLICATION SLAVE ON *.* to 'root'@'%' identified by 'root';
刷新权限
mysql> FLUSH PRIVILEGES;
查看mysql现在有哪些用户
mysql>select user,host from mysql.user;
l 第四步：查询master的状态
mysql> show master status;
从服务器配置
l 第一步：修改my.conf文件
[mysqld]
server-id=135
l 第二步：删除UUID文件
l 第三步：重启并登录到MySQL进行配置从服务器
mysql>change master to master_host='192.168.10.133',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=569
l 第四步：启动从服务器复制功能
mysql>start slave;
l 第五步：检查从服务器复制功能状态：
mysql> show slave status
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES