014、权限和锁
MySQL账户权限控制
- 权限申请流程要设置规范，合理，让需求不明确者知难而退
- 办公开发和测试环境可以放开权限，测试和正式环境要严格控制数据库写权限，并且读权限和对外业务服务分离
- 开发人员正式环境数据库权限分配原则：给单独的不对外服务的正式库只读权限，不能分配正式主库写权限
- 特殊人员：需要权限时，我们要问清楚做什么，发邮件说明，尽量都由DBA全权代理
- 特殊账号all privileges必须由DBA控制
数据库参数：
mysql> show variables like 'read_only%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+
1 row in set (0.00 sec)
--该参数如果是on的状态，针对具有update,delete,insert的权限的普通账号，会禁止DML操作
--但是对具有all privileges权限或者root账号不起作用。
--实验
mysql> grant update,delete,insert on test.* to 'test_user'@'%' identified by '123456';
Query OK, 0 rows affected (0.06 sec)
mysql> grant select on test.* to test_user;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
[root@localhost ~]# mysql -utest_user -p123456
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
mysql> use test;
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> insert into t1 select 2;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statementweb账户权限分配制度
- 写库账号默认权限为：select,insert,update,delete，不要给建表、改表(create,alter)等权限
- 读库账号默认权限为select(配合MySQL read-only参数使用)，确保从库对所有非super权限是只读的。
- 最好专库专账号，不要一个账号管理多个库，库特别多的小公司根据情况特殊对待管理。
- 避免root用户作为web连接用。
- web和数据库分离的服务器授权可以按ip或网段授权。
锁的介绍
锁是数据库系统区别于文件系统的一个关键特性，锁机制用于管理对共享资源的并发访问。innodb存储引擎会在行级别上对表数据上锁，这固然不错，不过innodb存储引擎也会在数据库内部其他多个地方使用锁，从而允许对多种不同资源提供并发访问。操作缓存池的LRU列表，删除，添加，移动LRU列表中的元素，为了保证一致性，必须有锁的介入，数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。
MySQL对表加锁(innodb)是在索引上，而Oracle是在数据块上。
锁类型：共享锁和排他锁。
- 共享锁：s lock，允许事务读一行数据；
- 排他锁：x lock，允许事务删除或者更新一行数据；
当一个事务已经获得了行r的共享锁，那么另外的事务可以立即获得行r的共享锁，因为读取并没有改变r的数据，我们称这种情况为锁兼容。
但如果有事务想获得行r的排他锁，则它必须等待事务释放行r上的共享锁，这叫锁的不兼容。
查看锁的状态：
我们可以通过show full processlist，show engine innodb status等命令来查看当前的数据库请求。
也可以通过information_schema中的innodb_trx，innodb_locks，innodb_lock_waits这三张表来临控当前事务并分析可能存在的锁的问题。
锁等待现象，实验：
mysql> insert into t values(1,'a'),(2,'b'),(3,'c'),(4,'d');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+------+------+
4 rows in set (0.00 sec)第一个会话开启事务：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t set name='aa' where id = 1;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0第二个会话更新同样的行：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t set name='aaa' where id=1; --发现一直在等待
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> show variables like '%innodb_lock_wait_timeout%'; --等待超时的时间
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.00 sec)第二个案例，锁阻塞：
mysql> insert into t1 select 1;
Query OK, 1 row affected (0.06 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t1 select 2;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t1 select 3;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t1 select 4;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t1 select 7;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t1 select 8;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t1 where id < 6 lock in share mode;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)第二个会话：
mysql> insert into t1 select 5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction以上的现象叫做锁堵塞。
锁等待超时时间设置：
mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.00 sec)
通过表查看锁情况
在information_schema库下，有3张表可以很直观的显示锁的情况：
INNODB_LOCKS，INNODB_TRX，INNODB_LOCK_WAITS
这三张表都是memory存储引擎。
mysql> show create table INNODB_LOCKS \G;
*************************** 1. row ***************************
Table: INNODB_LOCKS
Create Table: CREATE TEMPORARY TABLE `INNODB_LOCKS` (
`lock_id` varchar(81) NOT NULL DEFAULT '',
`lock_trx_id` varchar(18) NOT NULL DEFAULT '',
`lock_mode` varchar(32) NOT NULL DEFAULT '',
`lock_type` varchar(32) NOT NULL DEFAULT '',
`lock_table` varchar(1024) NOT NULL DEFAULT '',
`lock_index` varchar(1024) DEFAULT NULL,
`lock_space` bigint(21) unsigned DEFAULT NULL,
`lock_page` bigint(21) unsigned DEFAULT NULL,
`lock_rec` bigint(21) unsigned DEFAULT NULL,
`lock_data` varchar(8192) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)只有在有锁的情况下，这三个表才有数据：
mysql> select * from INNODB_LOCKS;
+---------------+-------------+-----------+-----------+-------------+-----------------+------------+-----------+----------+------------------------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+-------------+-----------------+------------+-----------+----------+------------------------+
| 274754:84:3:1 | 274754 | X | RECORD | `test`.`t1` | GEN_CLUST_INDEX | 84 | 3 | 1 | supremum pseudo-record |
| 274753:84:3:1 | 274753 | S | RECORD | `test`.`t1` | GEN_CLUST_INDEX | 84 | 3 | 1 | supremum pseudo-record |
+---------------+-------------+-----------+-----------+-------------+-----------------+------------+-----------+----------+------------------------+
2 rows in set (0.00 sec)
mysql> select * from INNODB_TRX;
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 274754 | LOCK WAIT | 2021-04-20 15:46:36 | 274754:84:3:1 | 2021-04-20 15:46:36 | 2 | 4 | insert into t1 select 5 | inserting | 1 | 1 | 2 | 360 | 1 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 10000 | 0 | 0 |
| 274753 | RUNNING | 2021-04-20 14:38:13 | NULL | NULL | 2 | 1 | NULL | NULL | 0 | 0 | 2 | 360 | 7 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 10000 | 0 | 0 |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
2 rows in set (0.00 sec)
mysql> select * from INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 274754 | 274754:84:3:1 | 274753 | 274753:84:3:1 |
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.00 sec)以上三张表，可以放在shell脚本里，结合grep、awk过滤正在执行的sql，占用锁的事务和表。
一致性非锁定读
一致性的非锁定行读：
是指innodb存储引擎通过多版本控制的方式来读取当前执行时间数据库中行的数据。
如果读取的行正在执行delete\update操作，这时读取操作不会因此等待行上X锁的释放，相反innodb存储引擎会去读取行的一个快照数据，不需要等待访问的行上的x锁的释放。
快照数据是指该行之前版本的数据，原理是UNDO段来实现，而UNDO用来在事务中回滚数据，因此快照数据本身没有额外的开销。
此外读取快照数据是不需要上锁的，因为没有必要对历史的数据进行修改。
非锁定读的机制大大提高了数据读取的并发性，在innodb存储引擎默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁，但在不同事务隔离级别下，读取的方式不同，并不是每个事务隔离级别下读取的都是一致性读。
不同的事务隔离级别，快照数据的定义不相同：
- 在read-committed事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据；
- 在repeatable-read事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。
对select加锁
innodb存储引擎对select语句加锁：
- select …… for update对读取的行记录加一个X锁，其他事务想在这些行上加任何锁都会被阻塞；(read new version)
- select …… lock in share mode对读取的行记录加一个S锁，其他事务可以向被锁定的记录加S锁，但对于加X锁，则会被阻塞
- select …… for update，select …… lock in share mode必须在一个事务中，当事务提交了，锁也就释放了，在使用上述两句select锁定语句时，务必加上begin,start transaction或者set autocommit=0
自增长和锁
自增长在数据库中是非常常见的一种属性，也是很多dba或开发人员首选的主键方式。
在innodb存储引擎的内存结构中，每个含有自增值的表都有一个增长计数器，当对有自增长计数器的表进行插入操作时，这个计数器会被初始化。
插入操作会根据这个自增长的计数器值加1赋自增长列，这个实现方式称作auto-inc-locking，这种锁其实是采用一种特殊的表锁机制，为了提高插入性能，锁并不是在一个事务完成后才释放，而是在完成对自增长插入的sql语句后立即释放。
auto-inc-locking从一定程度上提高了并发插入的效率，但这里还是存在一些问题：
- 首先对于有自增长值的列的并发插入性能较差，所以必须等待前一个插入的完成；
- 其次对于insert …… select的大数据量的插入，会影响插入的性能，因为另一个事务中的插入会被阻塞；
从mysql 5.122版本后，innodb存储引擎中提供了一种轻量级互斥量的自增长机制，这种机制大大提高了自增长插入性能。
参数介绍：innodb_autoinc_lock_mode，默认值为1，对于“simple inserts”，该值会用互斥量(mutex)去对内存中的计数器进行累加的操作。对于“bulk inserts”，还是会使用传统表锁的AUTO-INC locking方式。这样做，如果不考虑回滚操作，对于自增长的值的增长还是连续的。而在这种方式下，statement-based方式的replication还是能很好的工作。需要注意的是，如果已经使用AUTO-INC locking的方式产生自增长的值，而这时需要进行“simple inserts”的操作时，还是要等待AUTO-INC locking的的释放。
自增长的插入分类情况：
- INSERT-like：INSERT-like指所有的插入语句，如insert、replace、insert ... select、replace ... select、load data等；
- simple inserts：指能在插入前就确定插入行数的语句。这些语句包括insert、replace等。需要注意的是：simple insert不包含insert ... on dumplcate key update这类sql语句；
- bulk inserts：指在插入前不能确定得到插入行数的语句，如insert .. select、replace ... select、load data。
自增长需要注意的问题：
- innodb存储引擎中的实现和myisam不同，myisam是表锁的，自增长不用考虑并发插入的问题。因此在master用innodb存储引擎，salve用myisam存储引擎的replication架构下，必须考虑这种情况；
- innodb存储引擎下，自增长的列必须是索引，并且是索引的第一个列，如果是第二个列则会报错：
mysql> create table t( -> a int auto_increment, -> b int, -> key(b,a) -> ); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
锁的算法
innodb存储引擎有3种行锁的算法设计：
- record lock：单个行记录上的锁；
- gap lock：间隙锁，锁定一个范围，但不包括记录本身；
- next-key lock：★锁定一个范围，并且锁定记录本身。
浙公网安备 33010602011771号