sqlserver 修改数据库隔离级别

sqlserver默认隔离级别是已提交读，但是它的已提交读定义和别的数据库其实是不一样的，写会阻塞读，一定条件下读也会阻塞写，非常影响高并发系统性能。通常，我们需要在创建数据库时设置允许快照读，或者沟通业务停机时间进行设置。

一、 单实例

ALTER DATABASE db_ptype SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE db_ptype SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE db_ptype SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE db_ptype SET MULTI_USER;

如果是在创建数据库时设置，只要执行第二、三条语句即可。

修改隔离级别需要关闭其他所有到该库的连接（并不要求设置为单用户模式），如果之前有其他到该库的连接，修改sql会一直被阻塞之前前面所有sql执行完，同时阻塞后面的语句，影响业务。

SET SINGLE_USER WITH ROLLBACK IMMEDIATE 会kill其他连接到该数据库的会话（如果有大事务可能会回滚非常久），在database级别加一个S锁，修改期间不让其他会话连到该数据库。但是如果修改期间并发非常高，修改隔离级别的会话可能会因为死锁被kill掉，导致DBA都无法连接到该数据库，参考 迷之自信的Single_User Mode - 我是大菠萝 - 博客园

如果你遇到下面这个报错，说明已经失去了这个唯一的连接

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'db_ptype' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed. 

这时需要执行 sp_who 或者以下sql，找到是哪个会话占用了该连接，把它kill掉。当然，如果并发很高，很可能kill的速度完全赶不上应用发起连接的速度。因此，修改隔离级别操作最好在业务低峰或者停业务再操作。

DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses 
WHERE dbid = db_id('<yourDbName>')

EXEC(@kill);

二、 always-on

always-on中尝试改为单用户模式或sample模式等会有如下报错：

MSG 1468, LEVEL 16, STATE 1
THE OPERATION CANNOT BE PERFORMED ON DATABASE “TESTDB” BECAUSE IT IS INVOLVED IN A DATABASE MIRRORING SESSION OR AN AVAILABILITY GROUP. SOME OPERATIONS ARE NOT ALLOWED ON A DATABASE THAT IS PARTICIPATING IN A DATABASE MIRRORING SESSION OR IN AN AVAILABILITY GROUP.

MSG 5069, LEVEL 16, STATE 1
ALTER DATABASE STATEMENT FAILED.

解决方法：将DB踢出高可用组后进行设置，设置完成后再加回来（不要随意在生产库执行）

--1. Drop the database from the Availability Group.
ALTER AVAILABILITY GROUP [TestAG] REMOVE DATABASE [TestDB];
--2. Put the database in Single User Mode.（非必须，设置隔离级别等一些操作时需要）
ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--3. Modify the Transaction Isolation Level 
ALTER DATABASE [TestDB] SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE [TestDB] SET ALLOW_SNAPSHOT_ISOLATION ON;
--4. Turn Multi User back on
ALTER DATABASE [TestDB] SET MULTI_USER;
--5. Add the database back to the Availability Group
ALTER AVAILABILITY GROUP [TestAG] ADD DATABASE [TestDB];
--6. Reconnect the Secondary database to the Availability Group so it starts syncing again
ALTER DATABASE [TestDB] SET HADR AVAILABILITY GROUP = [TestAG];

如果数据库很大，这会相当耗时，如果从库业务还需要用，可能会无法接受。

三、 阿里云高可用

阿里云的sqlserver高可用底层用的是镜像（2017又添加了只读实例功能），跟always-on类似，需要先将从库set partner off。不同的是，改完隔离级别后阿里云会自动把从库加回镜像，不需要手动操作。

alter database db_ptype set partner off;
ALTER DATABASE db_ptype SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE db_ptype SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE db_ptype SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE db_ptype SET MULTI_USER;

之前还遇到一件介于故事和事故之间的事：将从库set partner off后，阿里云的工程师收到了电话告警，从库状态异常，而且后台检查数据库状态变成了单用户。为了修复从库状态，阿里云的工程师 KILL了我们修改隔离级别的会话，导致我们无法将数据库状态改回多用户，影响了业务一段时间。后来他们后台将数据库状态改回多用户并恢复了主从同步，在我们看来就变成了问题莫名其妙的自己恢复了。保险起见，以后万一要修改从库状态，还是要提前知会阿里云一声。

参考

sql - Set database from SINGLE USER mode to MULTI USER - Stack Overflow

http://www.sqlnuggets.com/blog/change-the-isolation-level-of-an-availability-group-database/