做网站怎样调用支付宝接口,企业做网站的步骤与做网站注意事项,网红营销的优势与劣势,网页升级访问紧急升级访问中一. 背景知识二. 步入正题#xff1a;表锁和行锁1.1. 表锁 vs 行锁在 MySQL 中锁的种类有很多#xff0c;但是最基本的还是表锁和行锁#xff1a;表锁指的是对一整张表加锁#xff0c;一般是 DDL 处理时使用#xff0c;也可以自己在 SQL 中指定#xff1b;而行锁指的是锁…一. 背景知识二. 步入正题表锁和行锁1.1. 表锁 vs 行锁在 MySQL 中锁的种类有很多但是最基本的还是表锁和行锁表锁指的是对一整张表加锁一般是 DDL 处理时使用也可以自己在 SQL 中指定而行锁指的是锁定某一行数据或某几行或行和行之间的间隙。行锁的加锁方法比较复杂但是由于只锁住有限的数据对于其它数据不加限制所以并发能力强通常都是用行锁来处理并发事务。表锁由 MySQL 服务器实现行锁由存储引擎实现常见的就是InnoDb所以通常我们在讨论行锁时隐含的一层意义就是数据库的存储引擎为 InnoDb 而 MyISAM 存储引擎只能使用表锁。1.2. 表锁表锁由 MySQL 服务器实现所以无论你的存储引擎是什么都可以使用。一般在执行 DDL 语句时譬 如 ALTER TABLE 就会对整个表进行加锁。在执行 SQL 语句时也可以明确对某个表加锁。1.2.1 操作演示(user_test创建表脚本见2.4.1)-- 事物A中 显示对 user_test 加上读锁locktableuser_test read;select*fromuser_testwhereid3; -- 此时事物B中对其进行update或者write操作start TRANSACTION;-- 手动开启事物insertintouser_test(age,name)values(18,Tom);结论此时事物B一致处于等待事物A释放锁的状态最终会回去锁超时2.1. 行锁(以下验证是在默认的隔离级别(可重复读的事务隔离级)下操作)InnoDB NEXT-KEY Locks解决了在可重复读的事务隔离级别下出现幻读的问题。什么是幻读幻读是在可重复读的事务隔离级别下会出现的一种问题简单来说可重复读保证了当前事务不会读取到其他事 务已提交的 UPDATE 操作。但同时也会导致当前事务无法感知到来自其他事务中的 INSERT 或 DELETE 操 作这就是幻读。2.2. 关于行锁我们要知道的行锁在 InnoDB 中是基于索引实现的所以一旦某个加锁操作没有使用索引那么该锁就会退化为表锁。2.3 行锁分类2.3.1 从加锁范围划分a)记录锁(Record Locks)存在与唯一索引包括主键索引 顾名思义记录锁就是为某行记录加锁它封锁该行的索引记录b)间隙锁(Gap Locks)存在与非唯一索引中锁定开区间范围内的一段间隔它是基于临键锁实现的。间隙锁基于非唯一索引它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法请务必牢记使用间隙锁锁住的是一个区间而不仅仅是这个区间中的每一条数据。是一种加在两个索引之间的锁或者加在第一个索引之前或最后一个索引之后的间隙。有时候又称为范围(Range Locks)这个范围可以跨一个索引记录多个索引记录甚至是空的。使用间隙锁可以防止其他事务在这个范围内插入或修改记录保证两次读取这个范围内的记录不会变从而不会出现幻读现象。很显然间隙锁会增加数据库的开销虽然解决了幻读问题但是数据库的并发性一样受到了影响所以在选择数据库的隔离级别时要注意权衡性能和并发性根据实际情况考虑是否需要使用间隙锁大多数情况下使用 read committed 隔离级别就足够了对很多应用程序来说幻读也不是什么大问题。产生间隙锁的条件(RR事务隔离级别下)3.1. 使普通索引锁定3.2. 使用多列唯一索引3.3. 使用唯一索引锁定多行记录。c)临键锁(Next-Key Locks)临键锁存在于非唯一索引中(主键中不存在临键锁)该类型的每条记录的索引上都存在这种锁它是一种特殊的间隙锁锁定一段左开右闭的索引区间。临键锁是记录锁与间隙锁的组合它的封锁范围既包含索引记录又包含索引区间。注意: Next-Key 可以理解为一种特殊的间隙锁也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁当某个事务持有该数据行的临键锁时会锁住一段左开右闭区间的数据。需要强调的一点是InnoDB 中行级锁是基于索引实现的临键锁只与非唯一索引列有关在唯一索引列(包括主键列)上不存在临键锁 , 但是存在间隙锁。临键锁的主要目的也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC临键锁则也会失效。2.3.2 从兼容性和存在形态划分2.4. 操作演示2.4.1 创建测试表并初始化数据CREATETABLEuser_test(idbigint(20)unsignedNOTNULLAUTO_INCREMENT COMMENT自增id,ageint(11)unsignedNOTNULLCOMMENT年龄,namevarchar(16)NOTNULLCOMMENT姓名,PRIMARY KEY(id)COMMENT主键)ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT行锁测试表;INSERTINTOuser_test(id,age,name)VALUES(1,10,Lee);INSERTINTOuser_test(id,age,name)VALUES(2,24,Ted);INSERTINTOuser_test(id,age,name)VALUES(3,32,Zed);INSERTINTOuser_test(id,age,name)VALUES(4,45,Talon);2.4.2Record lock 记录锁验证执行一下sql-- 事物A 中更新数据 sql如下-- 手动开启事物或者 beginSTART TRANSACTION;-- 根据非唯一索引列 UPDATE 某条记录 (只会产生记录锁不会产生间隙锁)UPDATEuser_testSETnameLISHIWHEREid2;-- 等观看完下面事物B在开启事物commit;rollback;-- 事物B 也同时更新统一条记录START TRANSACTION;UPDATEuser_testSETnameZHANGSANWHEREid2;commit;rollback;此时事物B会出现如图结果事物A commit 或者 rollback 此时事物B会拿到锁执行成功事物B更新其他id记录不受影响START TRANSACTION;UPDATEuser_testSETnameZHANGSANWHEREid1;commit;2.4.2Next-Key Locks 临键锁验证首先在age字段上创建普通索引ALTERTABLEuser_test ADD INDEX index_age(age);此时该表中 age 列潜在的临键锁有(-∞, 10],(10, 24],(24, 32],(32, 45],(45, ∞],事物A 中更新数据 sql如下START TRANSACTION;-- 根据非唯一索引列 UPDATE 某条记录select*fromuser_testwhereage10forupdate-- 事物B此时插入或者更新age小于10大于1的一条记录-- 插入一条记录为age7的记录start TRANSACTION;insertintouser_test(age,name)values(7,Tom);COMMIT;-- 事物B更新其中一条记录age为8也会被阻塞(虽然条件id是主键索引更新的字段是普通索引因此也会加上间隙锁)start TRANSACTION;UPDATEuser_testSETage8WHEREid2;COMMIT;此时事物B会出现如图结果事物A commit 或者 rollback 此时事物B会拿到锁执行成功2.4.3. Gap Locks 间隙锁验证打开间隙锁设置首先查看 innodb_locks_unsafe_for_binlog 是否禁用show variableslikeinnodb_locks_unsafe_for_binlog;innodb_locks_unsafe_for_binlog默认值为OFF即启用间隙锁。因为此参数是只读模式如果想要禁用间隙锁需要修改my.cnf(windows是my.ini)重新启动才行。默认mac是没有my.cnf文件的因此要在 etc文件下创建 my.cnf文件(etc/my.cnf)my.cnf内容如下(主要添加)innodb_locks_unsafe_for_binlog13.my.cnf内容如下# Example MySQL config file formediumsystems. # # Thisisfor a system with little memory(32M-64M)whereMySQL plays# an important part,orsystems up to128MwhereMySQLisused together with# other programs(suchasa web server) # # MySQL programs look for option filesinasetof# locations which dependonthe deployment platform. # You can copy this option file to one of those # locations. For information about these locations,see:# http://dev.mysql.com/doc/mysql/en/option-files.html # #Inthis file,you can use alllongoptions that a program supports.# If you want to know which options a program supports,run the program# with the--helpoption. # The following options will be passed to all MySQL clients [client]default-character-setutf8#passwordyour_passwordport3306socket/tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld]character-set-serverutf8init_connectSET NAMES utf8 port 3306 socket /tmp/mysql.sock skip-external-locking key_buffer_size 16M max_allowed_packet 1M table_open_cache 64 sort_buffer_size 512K net_buffer_length 8K read_buffer_size 256K read_rnd_buffer_size 512K myisam_sort_buffer_size 8M character-set-serverutf8 init_connectSETNAMES utf8 innodb_locks_unsafe_for_binlog 1# Dont listenona TCP/IP port at all. This can be a security enhancement,# if all processes that need to connect to mysqld runonthe same host.# All interaction with mysqld must be made via Unix socketsornamed pipes.# Note that using this option without enabling named pipesonWindows#(via theenable-named-pipeoption)will render mysqld useless!# #skip-networking # Replication Master Server(default)# binary loggingisrequired for replicationlog-binmysql-bin # binary logging format-mixed recommendedbinlog_formatmixed # required unique idbetween1and2^32-1# defaults to1if master-hostisnotset# but willnotfunctionasa master if omittedserver-id1 # Replication Slave(comment out master section to use this) # # To configure this hostasa replication slave,you can choosebetween# two methods: # #1)Use the CHANGE MASTER TO command(fully describedinour manual)-# the syntaxis: # # CHANGE MASTER TO MASTER_HOST,MASTER_PORT,# MASTER_USER,MASTER_PASSWORD; # #whereyou replace,,byquoted stringsand#bythe masters port number (3306 by default). # # Example: # # CHANGE MASTER TO MASTER_HOST125.564.12.1, MASTER_PORT3306, # MASTER_USERjoe, MASTER_PASSWORDsecret; # # OR # # 2) Set the variables below. However, in case you choose this method, then # start replication for the first time (even unsuccessfully, for example # if you mistyped the password in master-password and the slave fails to # connect), the slave will create a master.info file, and any later # change in this file to the variablesvaluesbelow will be ignoredand# overriddenbythe content of the master.infofile,unless you shutdown# the slave server,deletemaster.infoandrestart the slaver server.# For that reason,you may want to leave the lines below untouched#(commented)andinstead use CHANGE MASTER TO(see above) # # required unique idbetween2and2^32-1#(anddifferentfromthe master)# defaults to2if master-hostisset# but willnotfunctionasa slave if omitted#server-id2 # # The replication master for this slave-required#master-host # # The username the slave will use for authentication when connecting # to the master-required#master-user # # The password the slave will authenticate with when connecting to # the master-required#master-password # mysqld# The port the masterislisteningon.# optional-defaults to3306#master-port # # binary logging-notrequired for slaves,but recommended#log-binmysql-bin # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir/usr/local/mysql/data#innodb_data_file_pathibdata1:10M:autoextend#innodb_log_group_home_dir/usr/local/mysql/data# You canset.._buffer_pool_size up to50-80% # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size16M#innodb_additional_mem_pool_size2M#Set.._log_file_size to25%of buffer pool size#innodb_log_file_size5M#innodb_log_buffer_size8M#innodb_flush_log_at_trx_commit1#innodb_lock_wait_timeout50 [mysqldump] quick max_allowed_packet16M [mysql]no-auto-rehash# Remove the next comment character if you arenotfamiliar with SQL#safe-updatesdefault-character-setutf8 [myisamchk]key_buffer_size20Msort_buffer_size20Mread_buffer2Mwrite_buffer2M [mysqlhotcopy]interactive-timeout此时再次查看间隙锁是否开启唯一索引的间隙锁-- 创建test表并插入一些数据CREATETABLEtest(idint(1)NOTNULLAUTO_INCREMENT,namevarchar(8)DEFAULTNULL,PRIMARY KEY(id))ENGINEInnoDB DEFAULT CHARSETutf8;INSERTINTOtestVALUES(1,小罗);INSERTINTOtestVALUES(5,小黄);INSERTINTOtestVALUES(7,小明);INSERTINTOtestVALUES(11,小红);在进行测试之前我们先来看看test表中存在的隐藏间隙(-∞, 1](1, 5](5, 7](7, 11](11, ∞]-- 执行事物1如下START TRANSACTION;-- 查询 id 在 5 - 11 范围的数据并加记录锁 SELECT*FROMtestWHEREidBETWEEN5AND11FORUPDATE;-- 事务B进行插入数据操作 -- 注意以下的语句不是放在一个事务中执行而是分开多次执行每次事务中只有一条添加语句START TRANSACTION;-- 事务2插入一条 id 3name 小张1 的数据 INSERTINTOtest(id,name)VALUES(3,小张1);# 正常执行-- 事务3插入一条 id 4name 小白 的数据INSERTINTOtest(id,name)VALUES(4,小白);# 正常执行-- 事务4插入一条 id 6name 小东 的数据INSERTINTOtest(id,name)VALUES(6,小东);# 阻塞-- 事务5插入一条 id 8 name 大罗 的数据INSERTINTOtest(id,name)VALUES(8,大罗);# 阻塞-- 事务6插入一条 id 9 name 大东 的数据INSERTINTOtest(id,name)VALUES(9,大东);# 阻塞-- 事务7插入一条 id 11 name 李西 的数据INSERTINTOtest(id,name)VALUES(11,李西);# 阻塞-- 事务8插入一条 id 12 name 张三 的数据INSERTINTOtest(id,name)VALUES(12,张三);# 正常执行-- 提交事务1释放事务1的锁 COMMIT;从上面我们可以看到(5, 7]、(7, 11] 这两个区间都不可插入数据其它区间都可以正常插入数据。所以我们可以得出结论当我们给 (5, 7] 这个区间加锁的时候会锁住 (5, 7]、(7, 11] 这两个区间。我们再来测试如果我们锁住不存在的数据时会怎样-- 开启事务1 START TRANSACTION;-- 查询 id 3 这一条不存在的数据并加记录锁 SELECT*FROMtestWHEREid3FORUPDATE;-- 延迟30秒执行防止锁释放 SELECTSLEEP(30);-- # 注意以下的语句不是放在一个事务中执行而是分开多次执行每次事务中只有一条添加语句-- 事务2插入一条 id 3name 小张1 的数据 INSERTINTOtest(id,name)VALUES(2,小张1);# 阻塞-- 事务3插入一条 id 4name 小白 的数据 INSERTINTOtest(id,name)VALUES(4,小白);# 阻塞-- 事务4插入一条 id 6name 小东 的数据 INSERTINTOtest(id,name)VALUES(6,小东);# 正常执行-- 事务5插入一条 id 8 name 大罗 的数据 INSERTINTOtest(id,name)VALUES(8,大罗);# 正常执行-- 提交事务1释放事务1的锁 COMMIT;我们可以看出指定查询某一条记录时如果这条记录不存在会产生间隙锁。结论对于指定查询某一条记录的加锁语句如果该记录不存在会产生记录锁和间隙锁如果记录存在则只会产生记录锁如WHERE id 5 FOR UPDATE;对于查找某一范围内的查询语句会产生间隙锁如WHERE id BETWEEN 5 AND 7 FOR UPDATE;总结上述文档是自己及其团队小伙伴们一起讨论的结果自己做了一下总结很感谢团队成员刘昌力、刘明远、朱文彬、祁世松、桑萌萌、石伟男以及彭绍翔等他们的讨论支持。此文档是对行锁和表锁的一个粗略的认识存在一定的不足、知识点的缺失、不完善等问题。希望大家能够一起再完善一下共同学习进步掌握知识技能更好的正确高效的运用到平时的工作当中去才是我们分享技术文档的意思所在。
