MySQL transaction and lock mechanism

Not yet in the center 2021-01-21 16:44:23
mysql transaction lock mechanism

Mysql Transaction and lock mechanism

The concept of affairs

 Database transaction is a logical unit in the execution of database management system , It consists of a limited sequence of database operations . But not any sequence of database operations can be a transaction
Yes mysql Common engines are :MyISAM and InnoDB,MyISAM The default high-speed engine does not support transaction function ,InnoDB Support row locking and transaction processing , Velocity ratio MyISAM Slightly slower .
Its existence includes the following two purposes :
1. Provides a way for database operation sequence to recover from failure to normal state , At the same time, it provides a method to keep the consistency of database even in abnormal state .
2. When multiple applications are accessing the database concurrently , You can provide an isolation method between these applications , To prevent the operation of each other from interfering with each other .

Four characteristics of transaction (ACID)

  • Atomicity (Autmic): An atomic transaction is either fully executed , Or not at all . in other words , Every task in the unit of work must be performed correctly , If any task fails , The entire transaction is terminated and any previous changes to the data are undone . If all tasks are successfully performed , The transaction will be committed , Then the changes to the data will be permanent
  • Uniformity (Consistency): Consistency represents the integrity of the underlying data store . It is guaranteed by the transaction system and application developers . The transaction system guarantees the atomicity of transactions , Isolation and persistence to meet this requirement ; Application developers need to ensure that the database has appropriate constraints ( Primary key , Referential integrity, etc ), And the business logic implemented in the unit of work will not lead to data inconsistency ( The data expectations are inconsistent with the actual business situation ). for example , In just now AB During the transfer process , from A The amount deducted from the account must be equal to B The amount deposited in the account is equal .
  • Isolation, (Isolation): Isolation means that a transaction must be executed independently without interfering with other transactions , in other words , Before the transaction is completed , The data it accesses cannot be affected by other parts of the system .
  • persistence (Durability): Persistence indicates that when a system or medium fails , Ensure that the update data for committed transactions cannot be lost , That means once the transaction is committed ,DBMS Make sure that it changes the data in the database permanently , Can stand any system failure , Persistence can be guaranteed through database backup and recovery .

Isolation level

  • READ_UNCOMMITTED: This is the lowest level of isolation for transactions , It allows another transaction to see uncommitted data of the transaction , Dirty reading will occur 、 It can't be read repeatedly 、 Fantasy reading ( Lowest isolation level , High concurrent performance )

  • READ_COMMITTED: Ensure that the data modified by one transaction can only be read by another transaction after it is submitted . Another transaction cannot read uncommitted data of the transaction . Avoid dirty reading , But there will be non repeatable reading 、 The problem of unreal reading ( Lock the row being read ,mysql Default isolation level )

  • REPEATABLE_READ: It can prevent dirty reading 、 It can't be read repeatedly , But there's magic reading ( Lock all rows read )

  • SERIALIZABLE: This is the most expensive but reliable level of transaction isolation , Transactions are processed as sequential execution . Make sure that nothing happens ( Lock table , Concurrency is extremely low )

Isolation level Read uncommitted It can't be read repeatedly Fantasy reading
READ UNCOMMITTED Meeting Meeting Meeting
READ COMMITTED No Meeting Meeting
  • Read uncommitted : Business T2 Can read transactions T1 Uncommitted data , When a transaction T1 When performing a rollback ,T2 The data read is invalid

  • It can't be read repeatedly : Business T2 Read transactions T1 When not on and T1 The submitted data is inconsistent ( It's mainly data updates that lead to inconsistencies

  • Fantasy reading : Business T2 Read transactions T1 When not on and T1 The submitted data is inconsistent ( It is locked at this time T2 Read row , The main reason is the inconsistency caused by inserting and deleting data

    // Set the transaction isolation level for the current connection
    SET SESSION TRANSACTION ISOLATION LEVEL Transaction isolation level ;
    // Set up all connections ( Including new connections ) Transaction isolation level for
    SET GLOBAL TRANSACTION ISOLATION LEVEL Transaction isolation level ;

Pessimistic lock and optimistic lock

Pessimistic locking : Suppose a concurrency conflict happens , Avoid all operations that may violate data integrity .

Optimism lock : Assume no concurrency conflicts , Check for data integrity violations only when submitting operations , Note that optimistic locking does not solve the problem of dirty reading .

In general , Pessimistic lock depends on the lock mechanism of database , To ensure the maximum degree of exclusivity and exclusivity of the operation , This will lead to a large amount of database performance overhead and low concurrency , Especially for long term transactions , The cost is often too much to bear . To solve such problems , The optimistic lock mechanism appears . Optimism lock , Most of the time it's based on the data version ( Version ) Record mechanism implementation . What is a data version ? Add a version identity to the data , In the database table based version solution , Usually by adding a “version” Field to implement . When reading out data , Read out this version number together , When updated later , Add one... To this version number . here , Compare the version data of submitted data with the current version information recorded in the database table , If the submitted data version number is greater than the current version number of the database table , It will be updated , Otherwise, it is considered to be outdated data .ok~, The simple concepts of pessimistic lock and optimistic lock are understood first .

Shared lock and exclusive lock

stay mysql in , To ensure data consistency and prevent data processing conflicts , The technology of locking and unlocking is introduced , In this way, specific data in the database will not be used by other users ( Process or transaction ) Operation to lock the data , Unlock until the data is processed . According to different purposes of use, locks are divided into shared locks ( Also known as read lock ) And exclusive locking ( Write lock ).

Shared lock : Lock that makes object data read-only , This allows multiple parties to read one data at the same time , At this point, the data cannot be modified .
Lock him in : On the data insert/update/delete Lock it when you want to , At this point, other users ( Process or transaction ) You can't read data , So as to ensure data integrity .

Business 、 Isolation level 、 The relationship between locks

Through the above analysis , We also understand the business 、 The concept of lock and isolation level , But what's the relationship between locks and transactions and the level of separation ? actually , Business is solving multiple sql The atomicity of execution 、 Uniformity 、 Isolation, 、 The overall solution for persistence , The transaction separation level is the overall solution of concurrency control , In fact, it uses all kinds of locks to solve the concurrency problem . Lock is the internal basic mechanism of database concurrency control . For application developers , Only when the level of transaction separation cannot solve concurrency problems and requirements , It is necessary to set the lock manually in the statement . About lock , about UPDATE、DELETE and INSERT sentence ,InnoDB An exclusive lock will be automatically added to the involved data set (X); For ordinary SELECT sentence ,InnoDB No locks , Transactions can be displayed by using the following statements to add shared or exclusive locks to a recordset . Please note that InnoDB Row locking is achieved by locking the index entries on the index , in other words , Data is retrieved only through index conditions ,InnoDB To use row level locks , otherwise ,InnoDB Table locks will be used .

本文为[Not yet in the center]所创,转载请带上原文链接,感谢

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云