技术改变世界 阅读塑造人生! - shaogx.com

This string was altered by TechBlog\Plugins\Example.; This is an example to show the potential of an offcanvas layout pattern in Bootstrap. Try some responsive-range viewport sizes to see it in action.

MySQL InnoDB存储引擎之锁

    概念:         锁是用来管理对共享文件的并发访问。innodb会在行级别上对数据库上锁。不过innodb存储引擎会在数据库内部其他多个地方使用锁,从而允许对不同资源提供并发访问。例如操作缓冲池中的LRU列表,删除,添加,移动LRU列表中的元素,为了保证一致性,必须有锁的介入。MyISAM引擎是表锁,而InnoDB提供一致性的非锁定读、行级锁,且行级锁没有相关额外的开销。     锁         table-level locking(表级锁)             整个表被客户锁定。根据锁定的类型,其他客户不能向表中插入记录,甚至从中读数据也受到限制MyISAM、MEMORY默认锁级别,个别时候,InnoDB也会升级为表级锁         row-level locking(行级锁)             只有线程当前使用的行被锁定,其他行对于其他线程都是可用的InnoDB默认行级锁。是基于索引数据结构来实现的,而不是像ORACLE的锁,是基于block的。InnoDB也会升级为表级锁,全表/全索引更新,请求autoinc锁等         page-level locking(页级锁)             锁定表中某些行集合(称做页),被锁定的行只对锁定最初的线程是可行。如果另外一个线程想要向这些行写数据,它必须等到锁被释放。不过其他页的行仍然可以使用BDB默认页级锁     lock与latch         latch称为闩锁(轻量级的锁),因为其要求锁定的时间必须非常短。若持续的时间长,则应用的性能会非常差。在InnoDB存储引擎中,又可以分为mutex(互斥量)和rwlock(读写锁)。其目的是用来保证并发线程操作临界资源的正确性,并且通常没有死锁检测的机制。latch可以通过命令show engine innodb mutex来进行查看。如图:         由上图可以看出列Type显示的总是InnoDB,列Name显示latch的信息以及所在源码的行数,列Status中显示的os_waits表示操作系统等待的次数。         lock的对象是事务,用来锁定的是数据库中的对象,如表、页、行。并且一般lock的对象仅在事务commit或者rollback后释放(不同事务隔离级别释放的时间可能不一样)。有死锁机制。二则的区别如下:             特点:     InnoDB是通过对索引上的索引项加锁来实现行锁。这种特点也就意味着,只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁。     锁的类型:         有两种标准的行级锁:             共享锁(S lock):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁.SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE             排它锁(X lock):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他锁.SELECT * FROM table_name WHERE ... FOR UPDATE         InnoDB存储引擎支持意向锁且设计比较简练,分为两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁。(意向锁是InnoDB自动加的)             意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁.             意向独占锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁.         表级意向锁与行级锁的兼容情况如下图:                 锁的查看         在InnoDB1.0版本之前只能通过show engine innodb status(transactions行中查看) 或者 show full processlist来查看当前库中锁的请求。但是在这之后在information_schema架构下新增innodb_trx、innodb_locks和innodb_lock_waits三张表记录当前库中锁的情况。         三个表的字段说明如下图     一致性非锁定读(consistent nonlocking read)         一致性的非锁定读是指InnoDB存储引擎通过行多版本控制(multi_versioning)的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行delete或者update操作,这时读取操作不会因此去等待行上锁的释放。相反地,InnoDB存储引擎会去读取行的一个快照数据(当前行数据的历史版本)。快照数据是指该行的之前版本的数据,该实现是通过undo段来完成。而undo用来在事务回滚数据,因此快照数据本身是没有额外开销。而且,读取快照数据是不需要上锁的。一致性非锁定读是InnoDB存储引擎的默认读取方式(在读取不会占用和等待表上的锁)。但是在不同事务隔离级别下,读取的方式不同,并不是在每个事务隔离级别下都是采用非锁定的一致性读。即使都是使用非锁定的一致性读,但是对于快照数据的定义格式也各不相同。在事务隔离级别READ COMMITTED(RC)和REPEATABLE READ(RR,InnoDB存储引擎的默认事务隔离级别)下,InnoDB存储引擎使用非锁定的一致性读。然而,对于快照数据的定义去不相同。在RC事务隔离级别下,对于快照数据,非一致性读总是读取被锁定行的最新一份快照数据。而在RR事务隔离解绑下,对于快照数据,非一致性读总是读取事务开始时的行数据版本。     一致性锁定读         有上文知道,默认的事务隔离级别(RR)模式下,InnoDB存储引擎的select操作使用一致性非锁定读。但是在某些情况下,用户需要显式地对数据库读操作加锁以保证数据逻辑的一致性。InnoDB存储引擎对于select语句支持两种一致性的锁定读操作:             select ... for update:对读取的行记录加X锁,其他事物不能对该行加任何锁。             select ... lock in share mode:对读取的行记录加S锁,其他事物可以对该行加S锁,但是如果加X锁,则会被阻塞。     自增长与锁         在InnoDB存储引擎的内存结构中,对每个含有自增长值的表都有一个自增长计数器(auto-increment counter)。当对含有自增长的计数器的表进行插入操作是,这个计数器会被初始化,执行如下的语句来得到计数器的值:select max(auto_inc_col) from t for update。插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称为AUTO-INC Locking,这是一种特殊的表锁机制,为了提高插入的性能,锁不是在一个事务完成之后才释放,而是在完成对自增长值插入的SQL语句后会立即释放。AUTO-INC Locking在一定程度上提高了并发插入的效率,但是还存在一些性能上的问题。首先,对于有自增长值的列的并发插入性能较差,事务必须等待前一个插入的完成(不用等待事务的完成)。其次,对于insert ... select的大数据量的插入会影响插入的性能,因为另一个事务中的插入会被阻塞。从MySQL5.1.22版本开始,InnoDB存储引擎提供了一种轻量级互斥量的自增长实现机制,这种机制大大提高了自增长值插入的性能。通过参数innodb_autoinc_lock_mode来控制自增长的模式(默认为1)。自增长的插入进行分类如图:         innodb_autoinc_lock_mode的参数值及其对自增长的影响如下图:         MyISAM存储引擎是表锁,自增长不用考虑并发插入的问题。需要注意的是:在InnoDB存储引擎中,自增长值的列必须是索引,同时必须是索引的第一个列,如果不是第一个列,MySQL是会抛出异常的。异常如图     外键与锁         外键主要用于完整性的约束检查。在InnoDB存储引擎中,对于一个外键列,如果没有显式地对这个列加索引,InnoDB存储引擎会自动对其加一个索引,避免表锁。对于外键值的插入或者更新,首先需要查询父表中的记录,对于父表的select操作,不是使用的一致性非锁定读的方式,因为这样会发生数据不一致的问题,所以这时使用的是select ... lock in share mode方式,即主动给父表加一个S锁。     锁的问题         dirty read 脏读             脏读就是读取到脏数据(未提交的数据)。一个事务(A)读取到另一个事务(B)中修改后但尚未提交的数据,并在这个数据的基础上操作。这时,如果B事务回滚,那么A事务读到的数据是无效的。不符合一致性。如图             首先事务的隔离级别有默认的RR改为RU,由上述例子可以看出会话B中两次select操作取得了不同的结果,并且这2条记录是会话A中并未提交的数据,这就产生了脏读。由此可以得出结论:脏读发生的条件是事务的隔离级别为RU。         unrepeatable read 不可重复读             事务(A)读取到了另一个事务(B)已经提交的更改数据,不符合隔离性。不可重复读和脏读的区别是:脏读是读到未提交的数据,而不可重复读则读到的是已经提交的数据。首先将事务隔离级别调整为RC,然后操作下边的例子:         phantom read 幻读             事务(A)读取到了另一个事务(B)提交的新增数据,不符合隔离性。     锁的范围(锁的算法):         1.Record Lock :单个记录上的锁,总是会去锁住索引记录,如果InnoDB存储引擎表在建立的时候没有设置任何一个索引,那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。         2.Gap Lock:间隙锁,锁定一个范围,但不包含记录本身。         3.Next-key Lock: 锁定一个范围和本身 Record Lock + Gap Lock,防止幻读。         主键索引和唯一辅助索引 = record lock         非唯一辅助索引 = next-key lock     阻塞         不同锁之间的兼容性关系,在有些时刻一个事务中的锁需要等待另外一个事务中的锁释放它所占用的资源,这就是阻塞。阻塞并不是一件坏事,其是为了确保事务可以并发且正常地运行。在InnoDB存储引擎中,参数innodb_lock_wait_timeout用来动态的控制等待的时间(默认50秒),innodb_rollback_on_timeout用来静态的设定释放在等待超时时对进行的事务进行回滚操作(默认OFF,代表不回滚)。     死锁        死锁是指两个或者两个以上的事务在执行过程中,因争夺资源而造成的一种相互等待的现象。解决死锁最简单的一种方式是超时,即当两个事务相互等待是,当一个等待时间超过设置的某一阀值时,其中一个事务进行回滚,另外一个等待的事务就能继续进行。在InnoDB存储引擎中,参数innodb_lock_wait_timeout用来设置超时时间。但若超时的事务所占权重比较大,如果事务操作更新了很多行,占用了较多的undo log,这时采用FIFO的方式就不合适啦,因为回滚这个事务的时间相对于另一个事务所占用的时间可能会很多。因此,除了超时机制,当前数据库都普遍采用wait-for graph(等待图)的方式来进行死锁检测。要求数据库报错以下两种信息:a.锁的信息链表;b.事务等待链表。通过上述链表可以构造一张图,而在这个图中若存在回路,就代表存在死锁。在wait-for graph中,事务为图中的节点。如图:... 全文

Mysql InnoDB锁 Mysql InnoDB存储引擎锁 Mysql InnoDB Mysql 锁

mysql表的锁等待

今天线上业务遇到一个问题,因为一张模拟自增序列的表被锁住,涉及该表的业务受到影响。线上情况:1、这个表只有一个id字段。2、id字段为主键索引3、该表只有一行数据,记录全局最大id4、某业务存储过程操作会执行id+1操作,并获取当前id的值,插入到其他相关的表中一行数据5、存储过程执行完毕,最后会commit6、事物隔离级别为READ-COMMITTED7、某业务thread 102336锁住该表,使用show processlist查看,进程102336处于sleep状态8、偶尔会有其他tread 请求锁住该表,均等待超时9、在kill掉进程102336以后,业务恢复正常... 全文

mysql

mysql 锁问题处理

   错误信息如下# Time: 140122 16:34:23 # User@Host: xxxxxxx # Thread_id: 19904440 Schema: xxxx Last_errno: 1205 Killed: 0 # Query_time: 200.911931 Lock_time: 0.000041 Rows_sent: 0 Rows_examined: 0 Rows_affected: 0 Rows_read: 18446744073709551488 # Bytes_sent: 67 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 8DF1ED53F SET timestamp=1390379663; update tbname set c2=0 where c1=379933;    从zabbix lock信息里面提取出相关语句;... 全文

mysql 异常处理

Mysql的锁机制简单了解

介绍下对于Mysql锁机制的理解从基本概念开始:共享锁共享锁的代号是S,是Share的缩写,共享锁的锁粒度是行或者元组(多个行)。一个事务获取了共享锁之后,可以对锁定范围内的数据执行读操作。排它锁排它锁的代号是X,是eXclusive的缩写,排它锁的粒度与共享锁相同,也是行或者元组。一个事务获取了排它锁之后,可以对锁定范围内的数据执行写操作。假设有两个事务t1和t2如果事务t1获取了一个元组的共享锁,事务t2还可以立即获取这个元组的共享锁,但不能立即获取这个元组的排它锁(必须等到t1释放共享锁之后)。如果事务t1获取了一个元组的排它锁,事务t2不能立即获取这个元组的排共享锁,也不能立即获取这个元组的排它锁(必须等到t1释放排它锁之后)。意向锁意向锁是一种表锁,锁定的粒度是整张表,分为意向共享锁(IS)意向排它锁(IX)两类。意向共享锁表示一个事务有意对数据上共享锁或者排它锁。“有意”这两个字表达的意思比较微妙,说的明白点就是指事务想干这个事但还没真去干。举例说明下意向共享锁,比如一个事务t执行了这样一个语句:select * from table lock in share model ,如果这个语句执行成功,就对表table上了一个意向共享锁。lock in share model就是说事务t1在接下来要执行的语句中要获取S锁。如果t1的select * from table lock in share model执行成功,那么接下来t1应该可以畅通无阻的去执行只需要共享锁的语句了。意向排它锁的含义同理可知,上例中要获取意向排它锁,可以使用select * from table for update 。lock in share model 和 for update这两个东西在数据率理论中还有个学名叫悲观锁,与悲观锁相对的当然还有乐观锁。大家可以看到各种锁都是成双成对出现的。关于悲观锁和乐观锁的问题暂且不表,下文再来详述。 锁的互斥与兼容关系锁和锁之间的关系,要么是相容的,要么是互斥的。锁a和锁b相容是指:操作同样一组数据时,如果事务t1获取了锁a,另一个事务t2还可以获取锁b;锁a和锁b互斥是指:操作同样一组数据时,如果事务t1获取了锁a,另一个事务t2在t1释放锁a之前无法获取锁b。上面提到的共享锁、排它锁、意向共享锁、意向排它锁相互之前都是有兼容/互斥关系的,可以用一个兼容性矩阵表示(y表示兼容,n表示不兼容): X S IX ISX n n n nS n y n yIX n n y yIS n y y y 兼容性矩阵为什么是这个样子的?X和S的相互关系在上文中解释过了,IX和IS的相互关系全部是兼容,这也很好理解,因为它们都只是“有意”,还处于YY阶段,没有真干,所以是可以兼容的;剩下的就是X和IX,X和IS, S和IX, S和IS的关系了,我们可以由X和S的关系推导出这四组关系。简单的说:X和IX的=X和X的关系。为什么呢?因为事务在获取IX锁后,接下来就有权利获取X锁。如果X和IX兼容的话,就会出现两个事务都获取了X锁的情况,这与我们已知的X与X互斥是矛盾的,所以X与IX只能是互斥关系。其余的三组关系同理,可用同样的方式推导出来。一致性非阻塞读select... lock in share mode和select ... for update的区别索引记录锁间隙锁后码锁各种语句对应的锁类型在有索引的情况下是以后码锁为基础的行级锁,在固定索引键查找的情况下是索引记录锁,在没有可用索引的情况下上升到表锁有索引的情况:select ... from 一致性非阻塞读,不上锁。在serializable隔离级别下例外,在这个隔离级别下上共享后码锁select ... from ... lock in share mode 共享后码锁 select ... from ... for update 排它后码锁update .... where 排它后码锁delete from .... where 排它后码锁insert ... 排它索引记录锁,如果发生键值唯一性冲突则转成共享锁insert ... on duplicate key update ,一直都是排它锁replace ... 一直都是排它锁本文出自 “我的JAVA世界” 博客,请务必保留此出处http://hanchaohan.blog.51cto.com/2996417/1321107... 全文

mysql 简单学习

降低锁竞争 减少MySQL用户等待时间

导读:通过锁机制,可以实现多线程同时对某个表进行操作。如下图所示,在某个时刻,用户甲、用户乙、用户丙可能会同时或者先后(前面一个作业还没有完成)对数据表A进行查询或者更新的操作。当某个线程涉及到更新操作时,就需要获得独占的访问权。在更新的过程中,所有其它想要访问这个表的线程必须要等到其更新完成为止,此时就会导致锁竞争的问题,从而导致用户等待时间的延长,很多用户对这个问题都提出质疑。在这篇文章中,笔者将跟大家讨论,采取哪些措施可以有效的避免锁竞争,减少MySQL用户的等待时间。... 全文

MySQL 锁竞争

MySQL锁机制 你所不了解的一些事儿

1.MySQL中并发和隔离控制机制Meta-data元数据锁:在table cache缓存里实现的,为DDL(Data Definition Language)提供隔离操作。一种特别的meta-data元数据类型,叫Name Lock。(SQL层)表级table-level数据锁(SQL层)存储引擎特有机制 -- row locks行锁,page locks页锁,table locks表级,versioning版本(在引擎中实现)全局读锁 -- FLUSH TABLES WITH READ LOCK(SQL层)2.在语句执行中表的生命周期... 全文

MySQL锁

MySQL数据库锁机制的相关原理简介

不同于行级或页级锁定的选项:版本(例如,为并行的插入在MySQL中使用的技术),其中可以一个写操作,同时有许多读取操作。这明数据库或表支持数据依赖的不同视图,取决于访问何时开始。其它共同的术语是“时间跟踪”、“写复制”或者“按需复制”。按需复制在许多情况下优先于页级或行级锁定。然而,在最坏的情况下,它可能比使用常规锁定使用多的内存。... 全文

MySQL数据库 锁机制

也谈mysql锁问题

 锁是计算机协调多个进程或线程并发访问某一资源的机制,相对其他数据库而言,Mysql的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制.比如Myisam和Memory存储引擎采用的是表级锁;BDB存储引擎采用的是页面锁,但也支持表级锁;Innodb存储引擎既支持行级锁也支持表级锁;以下是他们各自锁的区别:    表级锁:开销小,加锁快;不会出现死锁;锁粒度大.发生锁冲突概率最高,并发度最低.    行级锁:开销大,加锁慢;会出现死锁,锁粒度最小;发生锁冲突概率最高,并发度也最高.    页面锁:开销和加锁时间界于表锁和行锁之间,会出现死锁;锁粒度界于表锁和行锁之间.1.Myisam 锁问题:    查询表级锁争用情况:... 全文

mysql 数据库 事务 锁问题 休闲

轻松掌握MySQL数据库锁机制的相关原理

在一个update和insert操作频繁的表中,少量数据测试的时候运行良好,在实际运营中,因数据量比较大(21万条记录),会出现死锁现象,用show processlist查看,可以看到一个update语句状态是Locked,一个delete语句状态是Sending data。查看了一下参考手册,把锁定相关的资料整理下来,以便自己记录和追踪该问题的解决情况:... 全文

MySQL 锁机制 数据库

shell 解决同步mysql slave 锁表问题

简明说下我的环境: 这是台 mysql SLAVE 服务器,主的不和他同一机房 ,主的是写的数据库 ,这是读的mysql 一又抓数据的 同步进程就锁,导致下面的select 进程全部锁掉 导致数据库 负载超级从而服务器就死机。 因为我这是做架构调整方案前的一些弊端问题,这是架构调整前的访问关系!为了临时解决就写了一个简单的脚本分享给大家不要见笑:[root@c3 shell]# cat slav.sh ... 全文

解决同步 mysql slave 锁表问题 shell

mysql中kill掉所有锁表的进程爬虫抓取数据分析

快过年了最近club服务器 老有刷数据的 封了N 多IP 而且一刷就锁表,老这样不是办法的想办法解决啊 mysql>showprocesslist;出来哗啦啦好几屏幕的, 没有一千也有几百条, 查询语句把表锁住了, 赶紧找出第一个Locked的thread_id, 在mysql的shell里面执行.mysql>killthread_id;kill掉第一个锁表的进程, 依然没有改善. 既然不改善, 咱们就想办法将所有锁表的进程kill掉吧, 简单的脚本如下.... 全文

爬虫 锁表的进程 mysql 数据库 休闲

mysql在生产环境下有大量锁表,又不允许重启的情况下的处理办法

mysql在生产环境下有大量锁表,又不允许重启的情况下的处理办法满头大汗的宅鸟该怎么办呢?... 全文

不重启 mysql 生产环境下大量锁表

mysql锁

MySQL锁执行操作时施加的锁模式读锁:共享锁写锁:独占锁,排它锁锁粒度:表锁:table lock锁定了整张表行锁:row lock锁定了需要的行粒度越小,开销越大,但并发性越好;粒度越大,开销越小,但并发性越差;锁的实现位置:MySQL锁:可以使用显式锁存储引擎锁:自动进行的(隐式锁);显式锁(表级锁):LOCK TABLESUNLOCK TABLESLOCK TABLES    tbl_name lock_type    [, tbl_name lock_type] ...    锁类型:READ|WRITEmysql>lock tables classes READ;... 全文

mysql 事务

MySQL深入03

MySQL的锁执行操作时施加的锁模式读锁:又称共享锁,多个读操作可以同时施加,非阻塞写锁:又称独占锁或排它锁,阻塞根据锁粒度分类表锁:table lock,锁定了整张表,开销小行锁:row lock,锁定了需要的行,开销大注:锁的粒度越小,开销越大,但并发性越好根据锁的实现位置分类... 全文

MySQL 事务 GTID

不需手动锁表同步mysql数据库

不需手动锁表同步mysql数据库确保系统安装有perl-DBD-mysqlYum install perl-DBD-mysql安装xtrabackupXtrabackup 2.1以后的版本不支持innodb引擎,因此需要下载2.0.*的版本。... 全文

mysql 同步

MySQL 表锁定事宜

摘自官方:为达到最高锁定速度,除InnoDB和BDB之外,对所有存储引擎,MySQL使用表锁定(而不是页、行或者列锁定)。  对于InnoDB和BDB表,如果你用LOCK TABLES显式锁定表,MySQL只使用表锁定。对于这些表类型,我们建议你根本不要使用LOCK TABLES,因为InnoDB使用自动行级锁定而BDB使用页级锁定来保证事务隔离。 对于大表,对于大多数应用程序,表锁定比行锁定更好,但存在部分缺陷。   表锁定使许多线程同时从一个表中进行读取操作,但如果一个线程想要对表进行写操作,它必须首先获得独占访问。更新期间,所有其它想要访问该表的线程必须等待直到更新完成。   表更新通常情况认为比表检索更重要,因此给予它们更高的优先级。这应确保更新一个表的活动不能“饿死”,即使该表上有很繁重的SELECT活动。 表锁定在下面的情况下也存在问题: · 一个客户发出长时间运行的查询。 · 然后,另一个客户对同一个表进行更新。该客户必须等待直到SELECT完成。 · ???  另一个客户对同一个表上发出了另一个SELECT语句。因为UPDATE比SELECT优先级高,该SELECT语句等待UPDATE完成,并且等待第1个SELECT完成。 下面描述了一些方法来避免或减少表锁定造成的竞争: · 试图使SELECT语句运行得更快。你可能必须创建一些摘要(summary)表做到这点。 · ??? 用--low-priority-updates启动mysqld。这将给所有更新(修改)一个表的语句以比SELECT语句低的优先级。在这种情况下,在先前情形的第2个SELECT语句将在UPDATE语句前执行,而不需要等候第1个SELECT完成。 · 可以使用SET LOW_PRIORITY_UPDATES=1语句指定具体连接中的所有更新应使用低优先级。参见13.5.3节,“SET语法” · 可以用LOW_PRIORITY属性给与一个特定的INSERT、UPDATE或DELETE语句较低优先级。 · 可以用HIGH_PRIORITY属性给与一个特定的SELECT语句较高优先级。参见13.2.7节,“SELECT语法”。 · 为max_write_lock_count系统变量指定一个低值来启动mysqld来强制MySQL在具体数量的插入完成后临时提高所有等待一个表的SELECT语句的优先级。这样允许在一定数量的WRITE锁定后给出READ锁定。· 如果你有关于INSERT结合SELECT的问题,切换到使用新的MyISAM表,因为它们支持并发的SELECT和INSERT。 · !!!如果你对同一个表混合插入和删除,INSERT DELAYED将会有很大的帮助。参见13.2.4.2节,“INSERT DELAYED语法”。 · 如果你对同一个表混合使用SELECT和DELETE语句出现问题,DELETE的LIMIT选项可以有所帮助。参见13.2.1节,“DELETE语法”。 · 对SELECT语句使用SQL_BUFFER_RESULT可以帮助使表锁定时间变短。参见13.2.7节,“SELECT语法”。 · 可以更改mysys/thr_lock.c中的锁代码以使用单一的队列。在这种情况下,写锁定和读锁定将具有相同的优先级,对一些应用程序会有帮助。 这里是一些MySQL中表锁定相关的技巧: · 如果不混合更新与需要在同一个表中检查许多行的选择,可以进行并行操作。 · 可以使用LOCK TABLES来提高速度,因为在一个锁定中进行许多更新比没有锁定的更新要快得多。将表中的内容切分为几个表也可以有所帮助。  · 如果在MySQL中表锁定时遇到速度问题,可以将你的表转换为InnoDB或BDB表来提高性能。1. read lock*************************** 1. row ***************************   Table: test_lock_tab Create Table: CREATE TABLE `test_lock_tab` (   `id` smallint(5) unsigned zerofill NOT NULL AUTO_INCREMENT,   `name` char(36) NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8  test $~ select * from test_lock_tab; +-------+------------+ | id    |   name     | +-------+------------+ | 00001 |  河蟹XXXX  |  +-------+------------+ 当前session0: test_lock_tab + read lock test $~ insert into test_lock_tab set name='默默QQQQ';  ERROR 1099 (HY000): Table 'test_lock_tab' was locked with a READ lock and can't be updated test $~ select * from a; ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLEStest $~ select * from test_lock_tab;  正常,包括不用缓存的读操作。sessionX: 对 test_lock_tab 的写操作不能执行,等待read锁释放;所有session read 操作正常。当 session0 再去占有其他表如的锁时,对 test_lock_tab 锁自动释放。 然而,此时,sessionA 对 test_lock_tab 表进行更新(UPDATE) or 删除(DELETE)。该客户sessionA 必须等待直到SELECT完成。sessionB 再对 test_lock_tab 表上发出了另一个SELECT语句。因为 UPDATE 比 SELECT 优先级高(默认),该SELECT语句等待 UPDATE(sessionA) 完成,并且等待第1个 SELECT(session0) 完成。除非 select 从 query_cache 读取【query_cache的重要性】。 或者給 SELECT 授予 HIGH_PRIORITY 属性。再或者~~~ 在然,此时,sessionA 对 test_lock_tab 表进行插入(INSERT)。该客户sessionA 必须等待直到SELECT完成。sessionB 再对 test_lock_tab 表上发出了另一个SELECT语句不需等待 sessionA 的 INSERT 操作完成。                                     priority[praiˈɔriti]   值得注意的一个参数: query_cache_wlock_invalidate!!! 一般情况,当客户端对MyISAM表进行WRITE锁定时,如果查询结果位于查询缓存中,则其它客户端未被锁定,可以对该表进行查询。将该变量设置为1,则可以对表进行WRITE锁定,使查询缓存内所有对该表进行的查询变得非法。这样当锁定生效时,可以强制其它试图访问表的客户端来等待。 -- 就算有缓存也不让用... ⊙﹏⊙b汗(有同学把这个设置为 on 了,不知其用意...不知道这东西在哪个应用应该on,还未发现...)(未完...待续)  InnoDB 中各 SQL 语句的锁定设置SELECT ... FROM ... : 这是一个 consistent read,不以锁定方式读取数据库的快照,除非事务的隔离级被设置为 SERIALIZABLE,在这种情况下将在它所读取的记录索引上设置共享的 next-key locks。SELECT ... FROM ... LOCK IN SHARE MODE : 在所读取的所有记录索引上设置同享的锁定。SELECT ... FROM ... FOR UPDATE : 在所读取的所胡记录索引上设置独占地(exclusive)锁定。INSERT INTO ... VALUES (...) : 在插入的记录行上设置一个独占地锁定;注意这个锁定并不是一个 next-key lock ,并不会阻止其它用户在所插入行之前的间隙(gap)中插入新记录。如果产生一个重复键值错误, 在重复索引记录上设置一个共享的锁定。如果在一个表中定义了一个 AUTO_INCREMENT 列,InnoDB 在初始化自增计数器时将在与自增列最后一个记录相对应的索引上设置一个独占的锁定。在访问自增计数器时, InnoDB 将设置一个特殊的表锁定模式 AUTO-INC ,这个锁定只持续到该 SQL 语句的结束而不是整个事务的结束。INSERT INTO T SELECT ... FROM S WHERE ... 在已插入到表 T 中的每个记录上设置一个独占的(无 next-key)锁定。以一个 consistent read 搜索表 S ,但是如果 MySQL 打开了日志开关将在表 S 上设置一个共享的锁定。 在从备份中进行前滚(roll-forward)修复时,每个 SQL 语句必须严格按照原先所执行的顺序运行,所以 InnoDB 不得不设置锁定。CREATE TABLE ... SELECT ... 与上项相似,以 consistent read 或锁定方式完成 SELECT 。REPLACE 如果没有一个 unique key 冲突,它的执行与 insert 一致。否则将在它所要更新的记录上设置一个独占的锁定。UPDATE ... SET ... WHERE ... : 在搜索时所遭遇到的记录上设置一个独占的锁定。DELETE FROM ... WHERE ... : 在搜索时所遭遇到的每一个记录上设置一个独占的锁定。如果一个表上有 FOREIGN KEY 约束,所有需要检查约束条件的 insert, update, 或 delete 将在它所要检查约束的记录上设置记录共享级的锁定。同样在约束失败时,InnoDB 也设置这个锁定。LOCK TABLES ... : 设置表锁定。在 MySQL 的代码层(layer of code)设置这些锁定。InnoDB 的自动死锁检测无法检测出有关下列情形的表锁定:查看下面的一个章节。同时查看第 14 章节 'InnoDB 限制与不足' 有关下列内容: 自从 MySQL 提供行锁以来,将有可能发生当其他用户设置了行级锁定时你又对该表设置了锁定。But that does not put transaction integerity into danger.在 3.23.50 版本以前, SHOW TABLE STATUS 应用于一个自增表时将在自增列的最大记录索引上设置一个独占的行级锁定。 这就意味着 SHOW TABLE STATUS 可能会引起一个事务的死锁,这可能是我们所意想不到的。从 3.23.50 开始,在读取自增列值时将不再设置任何锁定,除非在某些情况下,比如在数据库启动后没有任何记录。*************************** 1. row ***************************        Table: t1 Create Table: CREATE TABLE `t1` (   `a` int(11) NOT NULL,   `b` char(36) DEFAULT NULL,   PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)  ... 全文

MySQL 数据库 休闲 表锁 职场

MYSQL 数据表读锁机制详解

 一、概述MySQL有三种锁的级别:页级、表级、行级。 MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。 MySQL这3种锁的特性可大致归纳如下: 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。二、MyISAM表锁MyISAM存储引擎只支持表锁,是现在用得最多的存储引擎。 1、查询表级锁争用情况 可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺: mysql> show status like ‘table%’; +———————–+———-+ | Variable_name | Value | +———————–+———-+ | Table_locks_immediate | 76939364 | | Table_locks_waited | 305089 | +———————–+———-+ 2 rows in set (0.00 sec)Table_locks_waited的值比较高,说明存在着较严重的表级锁争用情况。 2、MySQL表级锁的锁模式 MySQL的表级锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁。 所以对MyISAM表进行操作,会有以下情况: a、对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。 b、对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。 下面通过例子来进行验证以上观点。数据表gz_phone里有二百多万数据,字段id,phone,ua,day。现在同时用多个客户端同时对该表进行操作分析。 a、当我用客户端1进行一个比较长时间的读操作时,分别用客户端2进行读和写操作: client1: mysql>select count(*) from gz_phone group by ua; 75508 rows in set (3 min 15.87 sec) client2: select id,phone from gz_phone limit 1000,10; +——+——-+ | id | phone | +——+——-+ | 1001 | 2222 | | 1002 | 2222 | | 1003 | 2222 | | 1004 | 2222 | | 1005 | 2222 | | 1006 | 2222 | | 1007 | 2222 | | 1008 | 2222 | | 1009 | 2222 | | 1010 | 2222 | +——+——-+ 10 rows in set (0.01 sec) mysql> update gz_phone set phone=’11111111111′ where id=1001; Query OK, 0 rows affected (2 min 57.88 sec) Rows matched: 1 Changed: 0 Warnings: 0 说明当数据表有一个读锁时,其它进程的查询操作可以马上执行,但更新操作需等待读锁释放后才会执行。 b、当用客户端1进行一个较长时间的更新操作时,用客户端2,3分别进行读写操作: client1: mysql> update gz_phone set phone=’11111111111′; Query OK, 1671823 rows affected (3 min 4.03 sec) Rows matched: 2212070 Changed: 1671823 Warnings: 0 client2: mysql> select id,phone,ua,day from gz_phone limit 10; +—-+——-+——————-+————+ | id | phone | ua | day | +—-+——-+——————-+————+ | 1 | 2222 | SonyEricssonK310c | 2007-12-19 | | 2 | 2222 | SonyEricssonK750c | 2007-12-19 | | 3 | 2222 | MAUI WAP Browser | 2007-12-19 | | 4 | 2222 | Nokia3108 | 2007-12-19 | | 5 | 2222 | LENOVO-I750 | 2007-12-19 | | 6 | 2222 | BIRD_D636 | 2007-12-19 | | 7 | 2222 | SonyEricssonS500c | 2007-12-19 | | 8 | 2222 | SAMSUNG-SGH-E258 | 2007-12-19 | | 9 | 2222 | NokiaN73-1 | 2007-12-19 | | 10 | 2222 | Nokia2610 | 2007-12-19 | +—-+——-+——————-+————+ 10 rows in set (2 min 58.56 sec) client3: mysql> update gz_phone set phone=’55555′ where id=1; Query OK, 1 row affected (3 min 50.16 sec) Rows matched: 1 Changed: 1 Warnings: 0 说明当数据表有一个写锁时,其它进程的读写操作都需等待读锁释放后才会执行。3、并发插入原则上数据表有一个读锁时,其它进程无法对此表进行更新操作,但在一定条件下,MyISAM表也支持查询和插入操作的并发进行。 MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2。 a、当concurrent_insert设置为0时,不允许并发插入。 b、当concurrent_insert设置为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置。 c、当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。4、MyISAM的锁调度由于MySQL认为写请求一般比读请求要重要,所以如果有读写请求同时进行的话,MYSQL将会优先执行写操作。这样MyISAM表在进行大量的更新操作时(特别是更新的字段中存在索引的情况下),会造成查询操作很难获得读锁,从而导致查询阻塞。 我们可以通过一些设置来调节MyISAM的调度行为: a、通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。 b、通过执行命令SET LOW_PRIORITY_UPDATES=1,使该连接发出的更新请求优先级降低。 c、通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。 上面3种方法都是要么更新优先,要么查询优先的方法。这里要说明的就是,不要盲目的给mysql设置为读优先,因为一些需要长时间运行的查询操作,也会使写进程“饿死”。只有根据你的实际情况,来决定设置哪种操作优先。这些方法还是没有从根本上同时解决查询和更新的问题。 在一个有大数据量高并发表的mysql里,我们还可采用另一种策略来进行优化,那就是通过mysql主从(读写)分离来实现负载均衡,这样可避免优先哪一种操作从而可能导致另一种操作的堵塞。下面将用一个篇幅来说明mysql的读写分离技术。... 全文

MySQL5.6 更改字段属性仍旧会锁全表,注意这个坑!

如图:如果开发让修改表字段属性,建议用pt-online-schema-change。MySQL5.6的在线DDL会锁全表。注意这个坑。另外,增加、删除字段或索引不会锁全表,删除主键会锁全表。... 全文

mysql5.6 ddl

1 2