锁和隔离级别--mysql sql 20170526更新

删掉了之前的博客(摘抄，不精准，理解欠佳)

数据库隔离级别：

http://blog.csdn.net/qq_33290787/article/details/51924963

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

幻读----范围数据、一个事物在读，一个事物在提交

隔离级别	脏读	非重复读	幻像读
read uncommitted	允许	允许	允许
read committed		允许	允许
repeatable read			允许
serializable

 

 

Oracle手动提交，默认read committed。

Mysql是自动提交，默认Repeatable read。

Read uncommitted 可以读到别的事务未提交的数据/事务回滚造成脏读

Read committed 在一个事务中可以读到其他事务提交的数据,这样在提交前后读到的数据不一致  无法重复读。

Repeatable read  在开启一个事务修改数据未提交时，其他事务无法对该数据做修改操作，避免了无法重复读的问题。

Serializable：指的是其他隔离级别无法保证有无新增数据。两次查询结果可能多了N条数据记录。

 

【开启事务做查询时，在其他事务提交前后查询出来数据一致，参考MVCC ,此事务在读取时读取的是mysql快照信息】

关于此 Repeatable read实践

参考http://blog.csdn.net/dong976209075/article/details/8802778

Mvcc参考http://blog.csdn.net/chen77716/article/details/6742128#comments

 

数据库锁和封锁协议：

悲观所和乐观所:

是否加锁：

悲观锁(Pessimistic Lock), 顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

 

乐观锁(Optimistic Lock), 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

 

排它锁和共享锁：

1 封锁类型（Locking）
    封锁是实现并发控制的一个非常重要的技术。所谓封锁就是事务T在对某个数据对象例如表、记录等操作之前，先向系统发出请求，对其加锁。加锁后事务T就对该数据对象有了一定的控制，在事务T释放它的锁之前，其它的事务不能更新此数据对象。基本的封锁类型有两种：排它锁（Exclusive locks简记为X锁）和共享锁（Share locks简记为S锁）。
    排它锁又称为写锁。若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。这就保证了其它事务在T释放A上的锁之前不能再读取和修改A。
    共享锁又称为读锁。若事务T对数据对象A加上S锁，则其它事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其它事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

 

封锁协议	排它锁		共享锁		一致性保证
	操作结束释放	事务结束释放	操作结束释放	事务结束释放	不丢失修改	不脏读	可重复读
一级		√			√
二级		√	√		√	√
三级		√		√	√	√	√

 

Mysql数据第三级锁协议，但是从上面的例子可以看到它并不是严格的第三级锁协议。

在查询时它运用了数据的快照。

 

Mysql不同引擎的锁类型，行级锁、页锁、表锁

http://www.cnblogs.com/donknap/archive/2010/01/27/1657373.html

 

 

面试问道：测试使用int类型索引：

 

= 1 、>1 、 <> 1 、各种like 1 加% 、is null、BETWEEN  and、都会用到索引

 

面试查询第二大：

 

select max(id) from table where id<(select max(id) from table);

 

衍生查询第N大：

 

select max(id) from table  where id not in (select t.id from (select id from table ORDER BY id desc limit 3) t)

 

研究第N大 question:

 

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

 

这版本的 MySQL 不支持使用LIMIT子句的IN/ALL/ANY/SOME子查询，即是支持非IN/ALL/ANY/SOME子查询的LIMIT子查询。

 

也就是说，这样的语句是不能正确执行的。

 

select * from table where id in (select id from table limit 10)

20170710：

SELECT * FROM `test` LIMIT n,m; 查询从n+1的m个数据

SELECT * FROM `test` LIMIT n OFFSET m ;查询从M+1开始的N条数据

 

主外键关联清空数据、删表等操作。

set FOREIGN_key_checks = 0;

set FOREIGN_key_checks = 1;

 

// 写mysql数据库文档用，用此sql查询关键信息，再贴到word文件表格中，done！
SELECT
    column_name,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_COMMENT,
    COLUMN_DEFAULT,
    COLUMN_KEY
FROM
    information_schema. COLUMNS
WHERE
    table_name = 'table'
AND table_schema = 'db';