1.为什么Mysql需要加锁？锁定机制用于管理对共享资源的并发访问。一种机制。当多个用户并发访问数据时，数据库中可能会出现多个事务同时操作一行数据。如果我们不控制这种并发操作，它可能会被读取和存储。不正确的数据最终会破坏数据的一致性；我们来看一个典型的并发更新数据导致的数据丢失更新问题：|TransactionA|TransactionB||--|--||beginA||||beginB||selectsalaryformtbwhereid=1（查询结果为1000）||||selectsalaryformtbwhereid=1（查询结果为1000）||updatetbsetsalary=1100whereid=1||||updatetbsetsalary=1200whereid=1||commitA|||commitB|异常结果：表中salary字段id为1的员工的salary被更新为1200，但是该员工的salary实际修改了两次。由于事务B是在事务A之后提交的，先提交的事务A的更新操作丢失了，所以我们需要一个锁机制来保证这种情况不会发生，保证事务中数据的一致性。2.锁型表锁：开销小，上锁速度快；没有死锁；加锁粒度大，锁冲突概率高，并发度最低；行锁：开销大，加锁慢；会发生死锁；lock粒度小；锁冲突概率低，并发度高；3、MyISAM存储引擎：表级锁的锁模式：表级锁有两种模式：表共享读锁（TableReadLock）和表独占写锁（TableWriteLock）；对于MyISAM表的读操作，不会阻塞其他用户对同表的读请求，但会阻塞对同表的写请求；对于MyISAM表的写操作，会阻塞其他用户对同一张表的读写操作；MyISAM表的读写操作，以及写操作与写操作之间都是串行的。ConcurrentInserts：MyISAM表的读写是串行的，但这是一般的情况。在一定条件下，MyISAM表还可以支持并发查询和插入操作；MyISAM存储引擎有一个系统变量concurrent_insert，专门用来控制它的并发插入行为，它的值可以是0、1/2。当concurrent_insert设置为0时，不允许并发插入；当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表中间没有被删除的行），MyISAM允许一个进程读取表，而另一个进程从末尾插入记录表的，这也是MySQL的默认设置；当concurrent_insert设置为2时，无论MyISAM表是否有空洞，都允许在表尾并发插入记录。您可以利用MyISAM存储引擎的并发插入特性来解决应用中同表查询和插入的锁争用问题。例如：将concurrent_insert变量的值设置为2，始终允许并发插入操作，同时在系统空闲期间周期性地执行OPTIMIZETABLE语句，对空间进行碎片整理，回收删除产生的中间空洞记录。MyISAM引擎的锁调度：MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。一个进程请求一个MyISAM表的读锁，另一个进程也请求同一个表的写锁。写优先级高于读优先级，所以写进程会先获得锁，即使读请求先来。在锁的等待队列中，写请求进入锁的等待队列，写锁页在执行前被插回读锁请求；我们可以通过一些设置来调整MyISAM的调度行为，通过指定启动参数low-priority-updates，让MyISAM引擎默认优先读取请求；通过执行命令SETLOW_PRIORITY_UPDATE=1，降低连接发送的更新请求的优先级；通过指定INSERT、UPDATE和DELETE语句的LOW_PRIORITY属性，可以降低语句的优先级。另外，MySQL还提供了一种折衷的方法来调整读写冲突，即为系统参数max_write_lock_count设置一个合适的值。当一个表的读锁达到这个值时，MySQL会暂时降低写请求的优先级，给读者一定的机会获得锁。4、InnoDB存储引擎：乐观锁和悲观锁是并发控制的两种思想，可以用来解决丢失更新的问题：乐观锁会“乐观地”假设大概率不会发生并发更新冲突，以及访问和处理数据。无锁，更新数据时只根据版本号或时间戳判断是否有冲突，有则处理，没有则提交事务；悲观锁会“悲观地”假设大概率存在并发更新冲突，访问和处理数据前加排他锁，在整个数据处理过程中对数据加锁，事务提交后释放锁或回滚；InnoDB存储引擎的标准行级锁：共享锁（SLock）：读锁，允许事务读取一行数据； 排他锁（XLock）：写锁，允许事务删除或更新一行数据； ##transaction1MariaDB[test]>showvariableslike"autocommit";------+--------+|Variable_name|Value|+----------------+------+|autocommit|OFF|+----------------+--------+1rowinset(0.00sec)MariaDB[test]>begin;QueryOK,0rowsaffected(0.00sec)MariaDB[test]>updatetb1setname="aaa"whereid=1;QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0MariaDB[test]>commit;QueryOK,0rowsaffected(0.00sec)##Transaction2:MariaDB[test]>showvariableslike"autocommit";+----------------+--------+|变量名|值|+----------------+-------+|autocommit|OFF|+----------------+--------+1rowinset(0.00sec)MariaDB[test]>begin;QueryOK,0rowsaffected(0.00sec)三月iaDB[test]>updatetb1setname="haha"whereid=1;QueryOK,1rowaffected(12.89sec)Rowsmatched:1Changed:1Warnings:0MariaDB[test]>rollback;QueryOK,0rowsaffected(0.00sec)MariaDB[test]>select*fromtb1whereid=1;+----+-----+|id|名称|+----+-----+|1|aaa|+----+------+1rowinset(0.00sec)InnoDB行锁实现方法： InnoDB行锁是通过在索引上锁定索引项。使用行级锁，否则InnoDB将使用表锁。在没有索引条件的查询时，InnoDB确实使用了表锁而不是行锁；因为MySQL的行锁是为索引锁的，而不是为记录锁的。因此，虽然访问的是不同行的记录，但如果使用相同的索引键，就会发生锁冲突；当表有多个索引时，不同的事务可以使用不同的索引来锁定不同的行。此外，无论是使用主键索引、唯一索引还是普通索引，InnoDB都会使用行锁来锁定数据；即使条件中使用了索引字段，是否使用索引检索数据是由MySQL通过判断不同执行计划的开销来决定的，如果MySQL认为全表扫描效率更高，比如对于一些小表,它不会使用索引。在这种情况下，InnoDB将使用表锁而不是行锁。意向锁：意向共享锁（ISLock）：事务想要获取表中某些行的共享锁；IntentionExclusiveLock(IXLock)：事务想要获取表中某些行的排他锁；查看InnoDB存储引擎Lock信息：MariaDB[test]>showengineinnodbstatus\G;MariaDB[test]>showengineinnodbstatus\G;MariaDB[test]>select*frominformation_schema.innodb_trx\G;***************************1.row****************************trx_id:1266629trx_state:RUNNINGtrx_started:2020-01-0816:24:50trx_requested_lock_id:NULLtrx_wait_started:NULLtrx_weight:0trx_mysql_thread_id:36696trx_query:select*frominformation_schema.innodb_trxtrx_operation_state:NULLtrx_tables_in_use:0trx_tables_locked:0trx_lock_structs:0trx_lock_memory_bytes:376trx_rows_locked:0trx_rows_modified:0trx_concurrency_tickets:0trx_isolation_level:REPEATABLEREADtrx_unique_checks:1trx_foreign_key_checks:1trx_last_foreign_key_error:NULLtrx_adaptive_hash_latched:0trx_adaptive_hash_timeout:100001rowinset(0.00sec)MariaDB[test]>select*frominformation_schema.innodb_locks\G;Emptyset(0.00sec)MariaDB[test]>select*frominformation_schema.innodb_lock_waits\G;Emptyset(0.00sec)一致非锁读（ConsistentNonlockingRead）： 表示InnoDB存储引擎通过多种版本控制（multiversioning）方式读取当前执行时间数据库如果读取行数据正在执行DELETE或UPDATE操作，读取操作不会等待释放该行的锁；相反，InnoDB存储引擎会读取该行的一个快照数据，快照数据是指该行上一版本的数据，它是通过undosegment实现的。而undo是用来回滚事务中的数据的，所以快照数据本身没有额外的开销。另外读取快照数据不需要加锁，因为没有事务需要修改历史数据。但是在不同的事务隔离级别下，对于快照数据，非一致读总是读取锁定行的最新快照数据，而在REPEATABLEREAD事务隔离级别下，对于快照数据，非一致读总是读取行数据版本在交易开始时。一致性锁读：显式锁住数据库读操作，保证数据逻辑的一致性； SELECT...FORUPDATE：对读取的行记录加X锁，其他事务不能对加锁的行加任何锁；SELECT...LOCKINSHAREMODE:给读取的行记录加S锁，其他事务可以给加锁行加S锁，但是如果加X锁，会被阻塞;##Transaction1MariaDB[test]>begin;QueryOK,0rowsaffected(0.00sec)MariaDB[test]>select*fromtb1whereid=1forupdate;+----+------+|id|name|+----+------+|1|aaa|+----+-----+1rowinset(0.00sec)MariaDB[test]>rollback;QueryOK,0rowsaffected(0.00sec)##transaction2MariaDB[test]>begin;QueryOK,0rowsaffected(0.00sec)MariaDB[test]>select*fromtb1whereid=1lockinsharemode;+----+-----+|id|name|+----+------+|1|aaa|+----+-----+1rowinset(11.55sec)MariaDB[test]>rollback;QueryOK,0rowsaffected(0.00sec)锁算法：5.死锁死锁是指一种现象，其中两个或多个交易在执行过程中因争夺资源而相互等待；MyISAM表锁是DeadlockFree，因为MyISAM总是一次获得所有需要的资源。锁，全部或等待，因此不会发生死锁。但是在InnoDB中，除了由单个SQL组成的事务外，锁是逐渐获取的，这就决定了InnoDB中有可能出现死锁。发生死锁后，InnoDB一般可以自动检测到，并让一个事务释放锁回滚，另一个事务获取锁继续完成事务。但是，在外部锁或表锁的情况下，InnoDB无法完全自动检测死锁。这个需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要注意的是，这个参数不是用来解决死锁的。问题在于，在并发访问比较高的情况下，如果大量的事务因为不能立即获得所需的锁而被挂起，会占用大量的计算机资源，造成严重的性能问题，甚至会拖累数据库。我们可以通过设置合适的锁等待超时阈值来避免这种情况的发生。##Transaction1MariaDB[test]>begin;QueryOK,0rowsaffected(0.00sec)MariaDB[test]>updatetb1setname="jyy"whereid=1;QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0MariaDB[test]>updatetb1setname="xixi"whereid=2;QueryOK,1rowaffected(8.25sec)Rowsmatched:1Changed:1Warnings:0MariaDB[test]>commit;QueryOK,0rowsaffected(0.00sec)MariaDB[test]>select*fromtb1whereidin(1,2);+----+-----+|id|name|+----+-----+|1|jyy||2|xixi|+----+------+2rowsinset(0.00sec)##Transaction2MariaDB[test]>begin;QueryOK,0rowsaffected(0.00sec)MariaDB[test]>updatetb1setname="haha"whereid=2;QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0MariaDB[test]>updatetb1setname="heihei"whereid=1;ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction避免死锁的常用方法： 1）在一个应用中，如果不同的程序会同时访问多个Table，你应该尝试同意以相同的顺序访问表，这可以大大减少死锁的机会。上面的例子中，由于两个session访问表的顺序不同，死锁的几率很高，但是如果访问顺序相同，则可以避免死锁；2）当程序批量处理数据时，如果提前对数据进行排序，保证每个线程按照固定的顺序处理记录，也可以大大降低死锁的可能性； 3）在一个事务中，如果要更新记录，应该直接申请足够级别的锁，即独占锁，不要先申请共享锁，导致锁冲突甚至死锁； 4）在REPEATABLE-READ隔离级别下，如果两个线程使用SELECT...FORUPDATE加独占锁。如果没有满足这个条件的记录，两个线程都会加锁成功。当程序发现该记录不存在时，它会尝试插入一条记录。如果两个线程都这样做，就会发生死锁。在这种情况下，将隔离级别设置为READCOMMITTED可以避免该问题； 5）当隔离级别为READCOMMITTED时，如果两个线程都先执行SELECT...FORUPDATE，则判断是否有满足条件的记录，如果没有则插入该记录。这时只有一个线程能插入成功，另一个线程会等待锁。当第一个线程提交时，第二个线程会因为主键冲突而报错，但是这个线程虽然报错了，但是会获得排它锁，这时候如果有第三个线程再次申请排它锁，也会发生死锁。6）如果出现死锁，可以使用上面的SQL命令查看锁信息，确定上次死锁的原因。返回的结果中包含死锁相关事务的详细信息，例如导致死锁的SQL语句、事务获得的锁、正在等待的锁、被回滚的事务等。以此为基础，可以分析死锁产生的原因和改进措施。
