数据库常见面试题

Oracle乐观锁和悲观锁

1 悲观锁
所谓的悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次拿数据的时候都会上锁。这样别人拿数据的时候就要等待直到锁的释放。

数据库行级锁，目的是让数据被查出来的时候就加上锁，然后再执行下面的程序逻辑，这样后面为了操作相同数据而进来的请求，就会在一开始就被拦住（这种效果千万不要以为可以做防重复提交）

在操作DML（insert，update，delete）语句时，oracle会自动加上行级锁，在select * from table for update 【of column】【nowait|wait 3】时，oracle也会自动加锁

1.1 单表 for update
一般在for update 时加nowait，这样就不用等待其他事务执行了，一判断有事务，立马抛出错误。

下面简单说一下 for update的四种情况：

select * from table where id = ‘1001’ for update 锁住了这条数据，那么另外一个人对该笔数据进行DML操作或者也执行同样的for update操作时，会检测到这笔数据上有行级锁，那么就会等待着锁释放；这样就会出现一个问题：其他的程序如果需要对这笔数据操作，就需要等，至于等多久要看锁什么时候释放！
select * from table where id = ‘1001’ for update nowait，意思就是如果这笔数据上本身加了锁，另外一个人去执行这句SQL的时候，发现加了锁，就会直接抛出异常（ORA-00054:资源正忙），不会等待这笔数据的锁释放。
select * from table where id = ‘1001’ for update wait 5；意思就是如果这笔数据被锁住，另外一个人如果执行这句SQL后，会等待5秒，如果5秒后这句SQL还没有得到这笔数据的锁，就会抛出异常（ORA-00054:资源正忙）
先执行 A语句：select * from table where id = ‘1001’ for update 把1001加上锁，然后再执行 B语句：select * from table where id = ‘1001’ and id =‘1002’ for update;这时候肯定查不出来，因为A已经把B要加锁的数据锁了，这样B连1002的数据都查不出来。解决方案：skip locked。如果把B语句改为：select * from table where id = ‘1001’ and id =‘1002’ for update skip locked；意思就是执行的时候如果发现要查询的数据有锁，就把加了锁的数据排除，把剩下的数据加锁，然后查出来！
上面讲到了 for update 的四种方式，实际情况如何选择呢？

关于NOWAIT，当有LOCK冲突时会提示错误并结束STATEMENT而不是在那里等待(比如:要查的行已经被其它事务锁了,当前的锁事务与之冲突,加上nowait,当前的事务会结束会提示错误并立即结束 STATEMENT而不再等待).
WAIT 子句指定等待其他用户释放锁的秒数，防止无限期的等待。
“使用FOR UPDATE WAIT”子句的优点如下：

防止无限期地等待被锁定的行；
允许应用程序中对锁的等待时间进行更多的控制。
对于交互式应用程序非常有用，因为这些用户不能等待不确定
若使用了skip locked，则可以越过锁定的行，不会报告由wait n 引发的‘资源忙’异常报告
1.2 关联表for update
现在大部分业务都是联表查询，如果用for update 的话，就会把所有关联表查询出来的列所在的行全部加锁，那这个锁可就重了，比如：

select * from t1,t2 where t1.id = t2.id and t1.age = ‘20’ for update
就会把T1和T2两个表中符合条件的行锁定；如果上述SQL我只想对T1表的结果集加锁，怎么办？答案：of column_name

例子：

select * from t1,t2 where t1.id = t2.id and t1.age = ‘20’ for update of t1.id;
这样就会只把T1表中的符合条件的行加锁，T2表中符合条件的行不会加锁。

PS:如果单表for update of column_name查询，其实和 for update操作是一样的！

1.3 解除for update 锁的占用
如果在为了方便修改表时，使用了for update 就容易把表锁住，如果这时候及时的commit或者rollback还能释放锁

如果，在提交或者释放之前这个plsql会话连接断了，或者别人占有锁而自己也想操作锁，可以如下操作：

查看锁表进程

select
t2.username,t2.sid,t2.serial#,t2.logon_time
from
v$locke{d}_{o}bjectt1,v$session t2
where t1.session_id=t2.sid ;

解锁杀死锁表进程

// kill的数字即是，锁表进程中的SID和SERIAL字段的值，把所有的值全部杀掉即可
alter system kill session ‘1155,39095’;
1.4 悲观锁缺点
虽然悲观锁应用起来很简单并且十分安全，与此同时却有两大问题：

锁定：应用的使用者选择一个记录进行更新，然后去吃午饭，但是没有结束或者丢弃该事务。这样其他所有需要更新该记录的用户就必须等待正在进行实务操作的用户回来并且完成该事务或者直到DBA杀掉该不愉快的事务并且释放锁。
死锁：用户A和B同时更新数据库。用户A锁定了一条记录并且试图请求用户B持有的锁，同时用户B也在等待获取用户A持有的锁。两个事务同时进入了无限等待状态即进入死锁状态。
2 乐观锁
所谓的乐观锁：就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据。oracle默认使用乐观锁

在乐观锁中，我们有3种常用的做法来实现：

2.1 比对法
第一种就是在数据取得的时候把整个数据都copy到应用中，在进行提交的时候比对当前数据库中的数据和开始的时候更新前取得的数据。当发现两个数据一模一样以后，就表示没有冲突可以提交，否则则是并发冲突，需要去用业务逻辑进行解决。

2.2 版本戳
第二种乐观锁的做法就是采用版本戳，这个在Hibernate中得到了使用。采用版本戳的话，首先需要在你有乐观锁的数据库table上建立一个新的column，比如为number型，当你数据每更新一次的时候，版本数就会往上增加1。比如同样有2个session同样对某条数据进行操作。两者都取到当前的数据的版本号为1，当第一个session进行数据更新后，在提交的时候查看到当前数据的版本还为1，和自己一开始取到的版本相同。就正式提交，然后把版本号增加1，这个时候当前数据的版本为2。当第二个session也更新了数据提交的时候，发现数据库中版本为2，和一开始这个session取到的版本号不一致，就知道别人更新过此条数据，这个时候再进行业务处理，比如整个Transaction都Rollback等等操作。在用版本戳的时候，可以在应用程序侧使用版本戳的验证，也可以在数据库侧采用Trigger(触发器)来进行验证。不过数据库的Trigger的性能开销还是比较的大，所以能在应用侧进行验证的话还是推荐不用Trigger。

2.3 timestamp型
第三种做法和第二种做法有点类似，就是也新增一个Table的Column，不过这次这个column是采用timestamp型，存储数据最后更新的时间。在Oracle9i以后可以采用新的数据类型，也就是timestamp with time zone类型来做时间戳。这种Timestamp的数据精度在Oracle的时间类型中是最高的，精确到微秒(还没与到纳秒的级别)，一般来说，加上数据库处理时间和人的思考动作时间，微秒级别是非常非常够了，其实只要精确到毫秒甚至秒都应该没有什么问题。和刚才的版本戳类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。如果不想把代码写在程序中或者由于别的原因无法把代码写在现有的程序中，也可以把这个时间戳乐观锁逻辑写在Trigger或者存储过程中。

2.4 例子Demo
说明：小明成绩错了，要改成绩。班主任能改，年级主任也能改！

//先查出来小明的成绩

select t.id,t.result from T t where t.id=‘10001’;—10001,59
//更新成绩,改为60

update T t set t.result =‘60’ where t.id=‘10001’ and t.result = ‘59’
//加上and t.result = ‘59’ 这个条件的目的就是为了验证，数据库里10001的成绩在此期间有没有被其他人改过，如果改过，那就更新条数为0（因为找不到符合条件的数据）；

PS:没有找到数据，所以没更新10001这笔数据，最好是程序返回一个没有更新到这笔数据的提示，如果不加任何提示，前端就会认为更新成功了！

1.利用数据库中的数据和已经取出的数据的一致性做为“锁”，与for update相比，乐观锁机制是等到更改数据的时候才去校验，悲观锁是读取数据就开始做了校验，从这个角度来看，乐观锁是对数据库没有额外开销，那么效率相对是高的。

2. 需要更改的字段可以作为乐观锁的验证字段；或者表里建立version版本号，每更新一次数据版本号+1；或者加lastupdatedate（最后更新时间），同理：数据更改的同时lastupdatedate也跟着变更！

3.其实乐观锁存在一个很致命的问题：

场景： 已上述小明改成绩为例，假设班主任改的同时，年级主任也改，两个请求几乎同时执行了查询：

select t.id,t.result from T t where t.id=‘10001’;—10001,59
都查出来是59分！！

然后几乎同时执行了改成绩，班主任改成60分，年级主任改成了80分，关键是还都update到10001了班主任：

update T t set t.result =‘60’ where t.id=‘10001’ --and t.result = ‘59’ ；
年级主任：

update T t set t.result =‘80’ where t.id=‘10001’ --and t.result = ‘59’ ；
这时候班主任事务先提交，数据库小明成绩改成了60，年级主任事务紧接着提交，小明的成绩又从60改成了80，那么对于班主任来说，他的数据就是更新丢失！！！所以大家使用起来要注意并发的情况！！

mysql和oracle的区别

(1) 对事务的提交
MySQL默认是自动提交

    Oracle默认不自动提交，需要用户手动提交，需要在写commit;指令或者点击commit按钮

(2) 分页查询
MySQL是直接在SQL语句中写"select… from …where…limit x, y",有limit就可以实现分页

Oracle则是需要用到伪列ROWNUM和嵌套查询
(3) 事务隔离级别
MySQL有4中隔离级别：读未提交，读已提交，可重复读，串行化

Oracle只有2中隔离级别：读已提交、串行化

MySQL是read commited的隔离级别，而Oracle是repeatable read的隔离级别，同时二者都支持serializable串行化事务隔离级别，可以实现最高级别的读一致性。每个session提交后其他session才能看到提交的更改。

(4) 对事务的支持
MySQL在innodb存储引擎的行级锁的情况下才可支持事务，而Oracle则完全支持事务
(5) 保存数据的持久性
MySQL是在数据库更新或者重启，则会丢失数据，Oracle把提交的sql操作线写入了在线联机日志文件中，保持到了磁盘上，可以随时恢复
(6) 并发性
MySQL以表级锁为主，对资源锁定的粒度很大，如果一个session对一个表加锁时间过长，会让其他session无法更新此表中的数据。虽然InnoDB引擎的表可以用行级锁，但这个行级锁的机制依赖于表的索引，如果表没有索引，或者sql语句没有使用索引，那么仍然使用表级锁。
Oracle使用行级锁，对资源锁定的粒度要小很多，只是锁定sql需要的资源，并且加锁是在数据库中的数据行上，不依赖与索引。所以Oracle对并发性的支持要好很多。
(7) 逻辑备份
MySQL逻辑备份时要锁定数据，才能保证备份的数据是一致的，影响业务正常的dml使用,Oracle逻辑备份时不锁定数据，且备份的数据是一致
(8) 复制
MySQL复制服务器配置简单，但主库出问题时，丛库有可能丢失一定的数据。且需要手工切换丛库到主库。
Oracle:既有推或拉式的传统数据复制，也有dataguard的双机或多机容灾机制，主库出现问题是，可以自动切换备库到主库，但配置管理较复杂。
(9) 性能诊断
MySQL的诊断调优方法较少，主要有慢查询日志。（现在的手段工具也挺多了）
Oracle有各种成熟的性能诊断调优工具，能实现很多自动分析、诊断功能。比如awr、addm、sqltrace、tkproof等
(10)权限与安全
MySQL的用户与主机有关，感觉没有什么意义，另外更容易被仿冒主机及ip有可乘之机。
Oracle的权限与安全概念比较传统，中规中矩。
(11)分区表和分区索引
MySQL的分区表还不太成熟稳定。
Oracle的分区表和分区索引功能很成熟，可以提高用户访问db的体验。
(12)管理工具
MySQL管理工具较少，在linux下的管理工具的安装有时要安装额外的包（phpmyadmin， etc)，有一定复杂性。
Oracle有多种成熟的命令行、图形界面、web管理工具，还有很多第三方的管理工具，管理极其方便高效。
(13)最重要的区别
MySQL是轻量型数据库，并且免费，没有服务恢复数据，并且开源
Oracle是重量型数据库，收费，Oracle公司对Oracle数据库有任何服务。

事务的 4 个隔离级别

未提交读（Read Uncommitted）：事务可以读取未提交的数据，也称作脏读（Dirty Read）。一般很少使用。

提交读（Read Committed）：是大多数 DBMS （如：Oracle, SQLServer）默认事务隔离。执行两次同样的查询却有不同的结果，也叫不可重复读。

可重复读（Repeable Read）：是 MySQL 默认事务隔离级别。能确保同一事务多次读取同一数据的结果是一致的。可以解决脏读的问题，但理论上无法解决幻读（Phantom Read）的问题。

可串行化（Serializable）：是最高的隔离级别。强制事务串行执行，会在读取的每一行数据上加锁，这样虽然能避免幻读的问题，但也可能导致大量的超时和锁争用的问题。很少会应用到这种级别，只有在非常需要确保数据的一致性且可以接受没有并发的应用场景下才会考虑。

脏读不可重复读幻读

脏读：读取到了其他事务还没提交的数据。

不可重复读：两次查询同一条数据有不同的结果，因为其它事务可能在UPDATE操作。

幻读：事务 B 根据条件查询到了 N 条数据，但这时事务 A INSERT或 DELETE了 M 条符合事务 B 查询条件的数据。事务 B 再次查询结果就和上一次不一致了，得到了 N+M 条数据。

不可重复读和幻读的区别

不可重复读：是同一数据内容被修改了，是 UPDATE操作。
幻读：某一个范围内的数据行数变多了或变少了，是 INSERT 和 DELETE 操作。

什么情况会导致索引失效

1.使用 SELECT * 进行查询;
2.创建了组合索引，但查询条件未准守最左匹配原则;
3.在索引列上进行计算、函数、类型转换等操作;
4.以 % 开头的 LIKE 查询比如 like ‘%abc’;;
5.查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;
6.发生隐式转换;
7.使用不等于（!=或者<>）的时候

解决索引失效的方法

1.尽量使用覆盖索引（之访问索引列的查询），减少 select * 覆盖索引能减少回表次数；

2.使用组合索引时，需要遵循“最左前缀”原则；

3.不在索引列上做任何操作，例如计算、函数、类型转换，会导致索引失效而转向全表扫描；

4.LIKE以通配符开头（%abc）MySQL索引会失效变成全表扫描的操作；

5.少用or，用它来连接时会索引失效。

6.字符串不加单引号会导致索引失效（可能发生了索引列的隐式转换）；

7.MySQL在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描；

union和union all的区别

一、区别1：取结果的并集

1、union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序;

2、union all: 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复;

二、区别2：获取结果后的操作

1、union: 会对获取的结果进行排序操作

2、union all: 不会对获取的结果进行排序操作

union all只是合并查询结果，并不会进行去重和排序操作，在没有去重的前提下，使用union all的执行效率要比union高