面试必备：聊聊数据库中的事务和锁

r囧r小猫 2022-09-02 04:06 218阅读 0赞

点击蓝色“java大数据修炼之道”关注我哟
    
    加个“星标”，每晚21:00，一起学技术

**简述：**

事务：保持逻辑数据一致性与可恢复性，必不可少的利器。比如同时对两个表进行插入操作，如果一个失败，一个成功，可以通过事务进行回滚。

锁：多用户访问同一数据库资源时，对访问的先后次序权限管理的一种机制，没有他事务或许将会一塌糊涂，不能保证数据的安全正确读写。

死锁：是数据库性能的重量级杀手之一，而死锁却是不同事务之间抢占数据资源造成的。

**扩展：**

## Sql中使用事务 ##

测试数据

--创建一个账户表,添加约束，余额（money）不小于零
        create table Tb_bankAcount(
            Id int identity(1,1) primary key,
            Name nvarchar(20) not null,
            Money int not null
        )
        alter table Tb_bankAcount
        add constraint CK_money CHECK(money>=0)
        --添加数据
        insert into Tb_bankAcount values('A',200)
        insert into Tb_bankAcount values('B',200)

测试转账事务

begin transaction --开启事务
        declare @errorCount int=0;--记录错误的变量
            update Tb_bankAcount set Money-=500 where Name='A'
            set @errorCount+=@@ERROR
            update Tb_bankAcount set Money+=500 where Name='B'
            set @errorCount+=@@ERROR
    
        if @errorCount>0      --有错误就回滚
            rollback transaction
        else                  --没有错误提交
            commit transaction

Ado.Net中使用事务（SqlTransaction形式）

using (SqlConnection conn = new SqlConnection(connStr))
                 {
                     //要执行的sql脚本
                     string sqlText = @"update Tb_bankAcount set Money-=100 where Name='A'
            update Tb_bankAcount set Money+=100 where Name='B'";
                     conn.Open();
                     SqlTransaction tran = conn.BeginTransaction();
                     using (SqlCommand com = new SqlCommand(sqlText, conn))
                     {
                         try
                         {
                             //开启事务
                             com.Transaction = tran;
                             com.ExecuteNonQuery();
                             //提交事务
                             tran.Commit();
                            Console.WriteLine("事务执行成功");
                         }
                         catch (Exception ex)
                         {
                             //回滚事务
                             tran.Rollback();
                             Console.WriteLine(ex.Message);
                         }
                     }
                 }

上边的代码执行时，由于满足约束条件（Money>0）,执行事务提交。

使用SqlTransaction执行事务时，每个事务都是基于SqlConnection的，如果我们的事务要跨越多个程序集或者使用多个数据库时，使用SqlTransaction来实现事务就比较麻烦了，针对这个问题.net 2.0出现了TransactionScope

Ado.Net中使用分布式事务（TransactionScope形式）

static void Main(string[] args)
    {
        //连接字符串
        string connstr1 = @"your connctionString1";
        string connstr2 = @"your connctionString2";
    
        using (TransactionScope ts = new TransactionScope())
        {
            #region 执行任务1
            using (SqlConnection conn1 = new SqlConnection(connstr1))
            {
                using (SqlCommand com = conn1.CreateCommand())
                {
                    conn1.Open();
                    com.CommandText = "delete from t_stu where id=10";
                    com.ExecuteNonQuery();
                }
            }
            #endregion
    
            #region 执行任务2
            using (SqlConnection conn2 = new SqlConnection(connstr2))
            {
                using (SqlCommand com = conn2.CreateCommand())
                {
                    conn2.Open();
                    com.CommandText = "insert into t_stu(stuname,age) values ('zs',22')";
                    com.ExecuteNonQuery();
                }
            }
            #endregion
            //通过ts.Complete()方法进行提交
            ts.Complete();
        }
    }

上边的代码十分简单，我们可以看到使用TransactionScope可以轻松的构建分布式的事务模型，conn1和conn2两个连接可以连接不同的数据库。TransactionScope实现了IDispose()接口，我们可以使用using语法来自动释放资源。执行TransactionScope时会依此执行TranactionScope的所有代码，当执行到ts.Complete()时表示事务中的任务都执行完成了，进行提交。如果不显示地执行ts.Complete()方法，TransactionScope中代码执行完毕后执行回滚操作。

## 其他测试代码 ##

提交事务

BEGIN TRAN Tran_Money;
      INSERT INTO [dbo].[Money]([Name],[Money])VALUES('沐风',100)
      COMMIT TRAN;

回滚事务

BEGIN TRAN Tran_Money;
    INSERT INTO [dbo].[Money]([Name],[Money])VALUES('沐风',100)
    ROLLBACK TRAN;

转账事务

BEGIN TRAN Tran_Money;
        --开始事务
      DECLARE @tran_error INT;
      SET @tran_error = 0;
      BEGIN TRY 
        UPDATE  dbo.Money
        SET     Money = Money - 30
        WHERE   Name = '张三';
        SET @tran_error = @tran_error + @@ERROR;
            --测试出错代码，看看张三的钱减少，李四的钱是否会增加
            --SET @tran_error = 1;
        UPDATE  dbo.Money
        SET     Money = Money + 30
        WHERE   Name = '李四';
        SET @tran_error = @tran_error + @@ERROR;
      END TRY
    
      BEGIN CATCH
        PRINT '出现异常，错误编号：' + CONVERT(VARCHAR, ERROR_NUMBER()) + ',错误消息：'
            + ERROR_MESSAGE();
        SET @tran_error = @tran_error + 1;
      END CATCH;
    
      IF ( @tran_error > 0 )
        BEGIN
            --执行出错，回滚事务
            ROLLBACK TRAN;
            PRINT '转账失败，取消交易!';
        END;
      ELSE
        BEGIN
            --没有异常，提交事务
            COMMIT TRAN;
            PRINT '转账成功!';
        END

## 锁 ##

锁是实现事务的关键，锁可以保证事务的完整性和并发性。数据库中的锁也是为了解决在并发访问时出现各种冲突的一种机制。

![6846e2a454d2580ab1ef0f6e151eb94a.png][]

### 锁的目的是什么？    ###

主要解决多个用户同时对数据库的并发操作时会带来以下数据不一致的问题：

*  丢失更新，同时修改一条数据
 *  读脏，A修改了数据后，B读取后A又取消了修改，B读脏
 *  不可重复读，A用户读取数据,随后B用户读取该数据并修改,此时A用户再读取数据时发现前后两次的值不一致
 *  还有一种是幻读，这个情况好像不多。

并发控制的主要方法是封锁,锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致

###  锁的粒度有哪些？ ###

*  数据库锁：锁定整个数据库，这通常发生在整个数据库模式改变的时候。
 *  表锁：锁定整个表，这包含了与该表相关联的所有数据相关的对象，包括实际的数据行(每一行)以及与该表相关联的所有索引中的键。
 *  区段锁：锁定整个区段，因为一个区段由8页组成，所以区段锁定是指锁定控制了区段、控制了该区段内8个数据或索引页以及这8页中的所有数据行。
 *  页锁：锁定该页中的所有数据或索引键。
 *  行或行标识符：虽然从技术上将，锁是放在行标识符上的，但是本质上，它锁定了整个数据行。

**行级锁**

select * from tablename with (rowlock) where id=3

select \* from tb WITH(XLOCK) where id = 5

public partial class SqlWith
        {
            public const string NoLock = "WITH(NOLOCK) ";
            public const string HoldLock = "WITH(HOLDLOCK)";
            public const string PagLock = "WITH(PAGLOCK)";
            public const string ReadCommitted = "WITH(READCOMMITTED)";
            public const string TabLockX = "WITH(TABLOCKX)";
            public const string UpdLock = "WITH(UPDLOCK)";
            public const string RowLock = "WITH(ROWLOCK)";
            public const string Null = "Non";
        }

锁定从数据库系统的角度大致可以分为6种：

*  共享锁（S）：还可以叫他读锁。可以并发读取数据，但不能修改数据。也就是说当数据资源上存在共享锁的时候，所有的事务都不能对这个资源进行修改，直到数据读取完成，共享锁释放。
 *  排它锁（X）：还可以叫他独占锁、写锁。就是如果你对数据资源进行增删改操作时，不允许其它任何事务操作这块资源，直到排它锁被释放，防止同时对同一资源进行多重操作。
 *  更新锁（U）：防止出现死锁的锁模式，两个事务对一个数据资源进行先读取在修改的情况下，使用共享锁和排它锁有时会出现死锁现象，而使用更新锁则可以避免死锁的出现。资源的更新锁一次只能分配给一个事务，如果需要对资源进行修改，更新锁会变成排他锁，否则变为共享锁。
 *  意向锁：SQL Server需要在层次结构中的底层资源上（如行，列）获取共享锁，排它锁，更新锁。例如表级放置了意向共享锁，就表示事务要对表的页或行上使用共享锁。在表的某一行上上放置意向锁，可以防止其它事务获取其它不兼容的的锁。意向锁可以提高性能，因为数据引擎不需要检测资源的每一列每一行，就能判断是否可以获取到该资源的兼容锁。意向锁包括三种类型：意向共享锁（IS），意向排他锁（IX），意向排他共享锁（SIX）。
 *  架构锁：防止修改表结构时，并发访问的锁。
 *  大容量更新锁：允许多个线程将大容量数据并发的插入到同一个表中，在加载的同时，不允许其它进程访问该表。

这些锁之间的相互兼容性，也就是，是否可以同时存在。

![bce36549998315a0c9c89269d1ae2765.png][]

锁兼容性具体参见：http://msdn.microsoft.com/zh-cn/library/ms186396.aspx

锁粒度和层次结构参见：http://msdn.microsoft.com/zh-cn/library/ms189849(v=sql.105).aspx

**引用：https://www.cnblogs.com/cnki/p/8715522.html**

PS：如果觉得我的分享不错，欢迎大家随手点赞、在看。▽加我微信,交个朋友长按/扫码添加↑↑↑1、这次专治Docker疑难杂症2、微服务架构实施原理详解3、盘点 HashMap 源码中的那些优雅的设计！4、面试官欺负人：new Object()到底占用几个字节？5、volatile有哪些应用场景？6、面试官问：缓存与数据库一致性如何解决？先操作数据库，还是缓存？

右下角，您点一下在看图片![c219b6e813fa6b850e5af835fab6405a.png][]

小微工资涨1毛

[6846e2a454d2580ab1ef0f6e151eb94a.png]: /images/20220829/94c88bd5698b46878871e23806b5ec19.png
[bce36549998315a0c9c89269d1ae2765.png]: /images/20220829/12e6fbe28b0c4591930ef9369dc9dd3d.png
[c219b6e813fa6b850e5af835fab6405a.png]: /images/20220829/06fdc25fad5e4588899da9f1f67cdc16.png