热门IT资讯网

MySQL的几种锁机制的使用介绍

发表于:2024-11-25 作者:热门IT资讯网编辑
编辑最后更新 2024年11月25日,MySQL的几种锁机制的使用介绍锁在日常的开发过程中,为了控制线程的并发肯定会用到锁机制。对于数据库而言,锁机制就是数据库为了保证数据的一致性,而使各种共享资源在被并发访问变得有序所设计的一种规则。当

MySQL的几种锁机制的使用介绍


在日常的开发过程中,为了控制线程的并发肯定会用到锁机制。对于数据库而言,锁机制就是数据库为了保证数据的一致性,而使各种共享资源在被并发访问变得有序所设计的一种规则

当然MySQL也不例外,根据不同的存储引擎,MySQL中锁的特性大致归纳为如下:

行锁表锁页锁
MyISAM

BDB
InnoDB

(注:由于BDB已经被InnoDB所取代,我们只讨论MyISAM表锁和InnoDB行锁的问题)

不同的锁在开销、加锁速度、死锁、粒度、并发性能等方面也有很大差异,

表锁:开销小,加锁快;不会出现死锁;锁定力度大,发生锁冲突概率高,并发度最低 行锁:

行锁:开销大,加锁慢;会出现死锁;锁定粒度小,发生锁冲突的概率低,并发度高 页锁:

页面锁:开销和加锁速度介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并发度一般

MyISAM

MyISAM的表级锁有两种模式:表共享读锁和表独占写锁,在兼容性方面,除了读锁与读锁之间互相兼容之外,其余互不兼容。此外,MyISAM表的读操作与写操作之间,以及写操作之间是串行的。

通常,MyISAM在执行查询语句之前会自动给涉及的所有表加读锁,在执行更新操作之前会自动给涉及的表加写锁,这个过程并不需要用户干预。MyISAM在自动加锁时,总是一次性获得SQL语句所需的全部锁,这也是MyISAM表不会出现死锁的原因。

考虑到某些情况下为了在一定程度上模拟事务操作,实现对某一时间点多个表的一致性读取,例如订单、订单明细,用户也可以用LOCK TABLE命令给MyISAM表显式加锁,例如lock table xxx read/write。

需要注意的是,在用LOCK TABLES给表显式加表锁时,必须同时取得所有涉及到表的锁,并且MySQL不支持锁升级,也就是说,在执行LOCK TABLES后,只能访问显式加锁的这些表,不能访问未加锁的表;同时,如果加的是读锁,那么只能执行查询操作,而不能执行更新操作。

之前说过MyISAM表的读和写是串行的,但这是就总体而言的。在一定条件下,MyISAM表也支持查询和插入操作的并发进行。

MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2。

1) 当concurrent_insert设置为0时,不允许并发插入。

2)当concurrent_insert设置为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置。

3)当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。

如果读进程和写进程同时对表加锁,MyISAM会怎么处理呢?MyISAM写进程先获得锁。不仅如此,即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁请求之前!这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。当然MySQL也提供了一种折中的办法来调节读写冲突,即给系统参数max_write_lock_count设置一个合适的值,当一个表的读锁达到这个值后,MySQL就暂时将写请求的优先级降低,给读进程一定获得锁的机会。

InnoDB

InnoDB实现了以下类型的行锁,

1)共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。

2) 排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。

另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁

1)意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。

2)意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。

上述锁模式的兼容情况具体如下表所示,x表示冲突,√表示兼容,

XIXSIS
X××××
IX××
S××
IS×

需要注意的是,

1)意向锁是InnoDB自动加的,不需用户干预。

2)对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);

3)对于普通SELECT语句,InnoDB不会加任何锁;

4)事务可以通过以下语句显示给 记录集 加共享锁或排他锁,

共享锁(S):SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。

排他锁(X):SELECT * FROM table_name WHERE … FOR UPDATE。

共享锁主要用在需要数据依存关系时来确认某行记录是否存在,并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作,则很有可能造成死锁,对于锁定行记录后需要进行更新操作的应用,应该使用排他锁。

重点来了,InnoDB行锁是通过给索引上的索引项加锁实现的,这意味着只有通过索引条件检索数据,InnoDB才使用行级锁,否则InnoDB使用表锁

间隙锁(Next-Key锁)

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做"间隙(GAP)",InnoDB也会对这个"间隙"加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。

InnoDB使用间隙锁的目的

1)一方面是为了防止幻读,以满足相关隔离级别的要求,对于上面的例子,要是不使用间隙锁,如果其他事务插入了empid大于100的任何记录,那么本事务如果再次执行上述语句,就会发生幻读;

2)另外一方面,是为了满足其恢复和复制的需要。

表锁

对于InnoDB表,绝大部分情况下都应该使用行级锁,因为事务和行锁往往是我们之所以选择InnoDB表的理由。

但在个别特殊事务中,也可以考虑使用表级锁

第一种情况是:事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁来提高该事务的执行速度。

第二种情况是:事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表,从而避免死锁、减少数据库因事务回滚带来的开销。

当然,应用中这两种事务不能太多,否则,就应该考虑使用MyISAM表了。

死锁

发生死锁后,InnoDB一般都能自动检测到,并使一个事务释放锁并回退,另一个事务获得锁,继续完成事务。但在涉及外部锁,或涉及表锁的情况下,InnoDB并不能完全自动检测到死锁,这需要通过设置锁等待超时参数 innodb_lock_wait_timeout来解决。需要说明的是,这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖跨数据库。我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。

此外,我们在操作数据库时也可以遵循一些原则,避免死锁发生:

1)在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会。

2)在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能。

3)在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁。

4)在REPEATABLE-READ隔离级别下,如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁,在没有符合该条件记录情况下,两个线程都会加锁成功。程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成READ COMMITTED,就可避免问题。

5)当隔离级别为READ COMMITTED时,如果两个线程都先执行SELECT…FOR UPDATE,判断是否存在符合条件的记录,如果没有,就插入记录。此时,只有一个线程能插入成功,另一个线程会出现锁等待,当第1个线程提交后,第2个线程会因主键重出错,但虽然这个线程出错了,却会获得一个排他锁!这时如果有第3个线程又来申请排他锁,也会出现死锁。

当然,如果发生死锁了,我们也可以使用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因。

0