MySQL行级锁、表级锁、页级锁详细介绍_MySQL
页级:引擎 BDB。
表级:引擎 MyISAM ， 理解为锁住整个表，可以同时读，写不行
行级:引擎 INNODB ， 单独的一行记录加锁
表级，直接锁定整张表，在你锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则读也不允许
行级,，仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。
页级，表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
MySQL 5.1支持对MyISAM和MEMORY表进行表级锁定，对BDB表进行页级锁定，对InnoDB表进行行级锁定。
对WRITE，MySQL使用的表锁定方法原理如下：
如果在表上没有锁，在它上面放一个写锁。
否则，把锁定请求放在写锁定队列中。
对READ，MySQL使用的锁定方法原理如下：
如果在表上没有写锁定，把一个读锁定放在它上面
否则，把锁请求放在读锁定队列中。
InnoDB使用行锁定，BDB使用页锁定。对于这两种存储引擎，都可能存在死锁。这是因为，在SQL语句处理期间，InnoDB自动获得行锁定和BDB获得页锁定，而不是在事务启动时获得。
行级锁定的优点：
当在许多线程中访问不同的行时只存在少量锁定冲突。
回滚时只有少量的更改。
可以长时间锁定单一的行。
行级锁定的缺点：
比页级或表级锁定占用更多的内存。
当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。
如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。
用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。
在以下情况下，表锁定优先于页级或行级锁定：
表的大部分语句用于读取。
对严格的关键字进行读取和更新，你可以更新或删除可以用单一的读取的关键字来提取的一行：
UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
DELETE FROM tbl_name WHERE unique_key_col=key_value;
SELECT 结合并行的INSERT语句，并且只有很少的UPDATE或DELETE语句。
在整个表上有许多扫描或GROUP BY操作，没有任何写操作。
/* ========================= mysql 锁表类型和解锁语句 ========================= */
如果想要在一个表上做大量的 INSERT 和 SELECT 操作，但是并行的插入却不可能时，可以将记录插入到临时表中，然后定期将临时表中的数据更新到实际的表里。可以用以下命令实现：
mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;
行级锁的优点有：
在很多线程请求不同记录时减少冲突锁。
事务回滚时减少改变数据。
使长时间对单独的一行记录加锁成为可能。
行级锁的缺点有：
比页级锁和表级锁消耗更多的内存。
锁是计算机协调多个进程或线程并发访问某一资源的机制，不同的数据库的锁机制大同小异。由于数据库资源是一种供许多用户共享的资源，所以如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。了解锁机制不仅可以使我们更有效的开发利用数据库资源，也使我们能够更好地维护数据库，从而提高数据库的性能。
MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。
例如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level-locking）；BDB存储引擎采用的是页面锁（page-level-locking），同时也支持表级锁；InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下是采用行级锁。
上述三种锁的特性可大致归纳如下：
1） 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
2） 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
3） 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
三种锁各有各的特点，若仅从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如WEB应用；行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。什么意思呢，就是说对MyISAM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞 对同一表的写操作；而对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。
MyISAM表的读和写是串行的，即在进行读操作时不能进行写操作，反之也是一样。但在一定条件下MyISAM表也支持查询和插入的操作的并发进行，其机制是通过控制一个系统变量（concurrent_insert）来进行的，当其值设置为0时，不允许并发插入；当其值设置为1 时，如果MyISAM表中没有空洞（即表中没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录；当其值设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。
MyISAM锁调度是如何实现的呢，这也是一个很关键的问题。例如，当一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，此时MySQL将会如优先处理进程呢？通过研究表明，写进程将先获得锁（即使读请求先到锁等待队列）。但这也造成一个很大的缺陷，即大量的写操作会造成查询操作很难获得读锁，从而可能造成永远阻塞。所幸我们可以通过一些设置来调节MyISAM的调度行为。我们可通过指定参数low-priority-updates，使MyISAM默认引擎给予读请求以优先的权利，设置其值为1（set low_priority_updates=1),使优先级降低。
InnoDB锁与MyISAM锁的最大不同在于：一是支持事务（TRANCSACTION），二是采用了行级锁。我们知道事务是由一组SQL语句组成的逻辑处理单元，其有四个属性（简称ACID属性），分别为：
原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全部执行，要么全都不执行；
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态；
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行；
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
InnoDB有两种模式的行锁：
1）共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
( Select * from table_name where ......lock in share mode)
2）排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和 排他写锁。(select * from table_name where.....for update)
为了允许行锁和表锁共存，实现多粒度锁机制；同时还有两种内部使用的意向锁（都是表锁），分别为意向共享锁和意向排他锁。
InnoDB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁！
另外：插入，更新性能优化的几个重要参数
bulk_insert_buffer_size
批量插入缓存大小, 这个参数是针对MyISAM存储引擎来说的.适用于在一次性插入100-1000+条记录时, 提高效率.默认值是8M.可以针对数据量的大小,翻倍增加.
concurrent_insert
并发插入, 当表没有空洞(删除过记录), 在某进程获取读锁的情况下,其他进程可以在表尾部进行插入.
值可以设0不允许并发插入, 1当表没有空洞时, 执行并发插入, 2不管是否有空洞都执行并发插入.
默认是1 针对表的删除频率来设置.
delay_key_write
针对MyISAM存储引擎,延迟更新索引.意思是说,update记录时,先将数据up到磁盘,但不up索引,将索引存在内存里,当表关闭时,将内存索引,写到磁盘. 值为 0不开启, 1开启. 默认开启.
delayed_insert_limit, delayed_insert_timeout, delayed_queue_size
延迟插入, 将数据先交给内存队列, 然后慢慢地插入.但是这些配置,不是所有的存储引擎都支持, 目前来看, 常用的InnoDB不支持, MyISAM支持. 根据实际情况调大, 一般默认够用了
/* ==================== MySQL InnoDB 锁表与锁行 ======================== */
由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。
举个例子: 假设有个表单products ，里面有id跟name二个栏位，id是主键。
例1: (明确指定主键，并且有此笔资料，row lock)
SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;
例2: (明确指定主键，若查无此笔资料，无lock)
SELECT * FROM products WHERE id='-1' FOR UPDATE;
例3: (无主键，table lock)
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;
例4: (主键不明确，table lock)
SELECT * FROM products WHERE id'3' FOR UPDATE;
例5: (主键不明确，table lock)
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;
注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。
注2: 要测试锁定的状况，可以利用MySQL的Command Mode ，开二个视窗来做测试。
在MySql 5.0中测试确实是这样的
另外：MyAsim 只支持表级锁，InnerDB支持行级锁
添加了(行级锁/表级锁)锁的数据不能被其它事务再锁定，也不被其它事务修改（修改、删除）
是表级锁时，不管是否查询到记录，都会锁定表
此外，如果A与B都对表id进行查询但查询不到记录，则A与B在查询上不会进行row锁，但A与B都会获取排它锁，此时A再插入一条记录的话则会因为B已经有锁而处于等待中，此时B再插入一条同样的数据则会抛出Deadlock found when trying to get lock; try restarting transaction然后释放锁，此时A就获得了锁而插入成功
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
Where can I view the records of things I have purchased on Pinduoduo? How to view the records of purchased products?
Mar 12, 2024 pm 07:20 PM
Pinduoduo software provides a lot of good products, you can buy them anytime and anywhere, and the quality of each product is strictly controlled, every product is genuine, and there are many preferential shopping discounts, allowing everyone to shop online Simply can not stop. Enter your mobile phone number to log in online, add multiple delivery addresses and contact information online, and check the latest logistics trends at any time. Product sections of different categories are open, search and swipe up and down to purchase and place orders, and experience convenience without leaving home. With the online shopping service, you can also view all purchase records, including the goods you have purchased, and receive dozens of shopping red envelopes and coupons for free. Now the editor has provided Pinduoduo users with a detailed online way to view purchased product records. method. 1. Open your phone and click on the Pinduoduo icon.
How to check call history in iPhone and export it?
Jul 05, 2023 pm 12:54 PM
Call recording in iPhone is often underestimated and is one of the most critical features of iPhone. With its simplicity, this feature is of vital importance and can provide important insights about the calls made or received on the device. Whether for work purposes or legal proceedings, the ability to access call records can prove invaluable. In simple terms, call history refers to the entries created on your iPhone whenever you make or receive a call. These logs contain key information, including the contact's name (or number if not saved as a contact), timestamp, duration, and call status (dialed, missed, or not answered). They are a concise record of your communication history. Call history includes call history strips stored on your iPhone
How to view and manage Linux command history
Aug 01, 2023 pm 09:17 PM
How to View Command History in Linux In Linux, we use the history command to view the list of all previously executed commands. It has a very simple syntax: history Some options for pairing with the history command include: Option description -c clears the command history for the current session -w writes the command history to a file -r reloads the command history from the history file -n Limit the number of output of recent commands Simply run the history command to see a list of all previously executed commands in a Linux terminal: In addition to viewing command history, you can also manage command history and perform modifications to previously executed commands , reverse search command history or even delete history completely
How to view your medication log history in the Health app on iPhone
Nov 29, 2023 pm 08:46 PM
iPhone lets you add medications to the Health app to track and manage the medications, vitamins and supplements you take every day. You can then log medications you've taken or skipped when you receive a notification on your device. After you log your medications, you can see how often you took or skipped them to help you track your health. In this post, we will guide you to view the log history of selected medications in the Health app on iPhone. A short guide on how to view your medication log history in the Health App: Go to the Health App>Browse>Medications>Medications>Select a Medication>Options&a
C# Development Advice: Logging and Monitoring Systems
Nov 22, 2023 pm 08:30 PM
C# Development Suggestions: Logging and Monitoring System Summary: In the software development process, logging and monitoring systems are crucial tools. This article will introduce the role and implementation suggestions of logging and monitoring systems in C# development. Introduction: Logging and monitoring are essential tools in large-scale software development projects. They can help us understand the running status of the program in real time and quickly discover and solve problems. This article will discuss how to use logging and monitoring systems in C# development to improve software quality and development efficiency. The role of logging system
How to log and monitor Java development projects
Nov 03, 2023 am 10:09 AM
How to log and monitor Java development projects 1. Background introduction With the rapid development of the Internet, more and more companies have begun to develop Java and build various types of applications. In the development process, logging and monitoring are an important link that cannot be ignored. Through logging and monitoring, developers can discover and solve problems in time to ensure the stability and security of applications. 2. The importance of logging 1. Problem tracking: When an application error occurs, logging can help us quickly locate the problem.
How to clear history on iPhone
Jun 29, 2023 pm 01:13 PM
How to clear iPhone history in Safari? To clear your browsing and search history on Apple's Safari, you need to open the Settings app on your device. After selecting Settings, you need to scroll down and select Safari, then another menu will appear and you need to select Clear History and Website Data. You now need to select Clear History and Data from the menu, which will delete all search history, browsing history, cookies, and data from Apple’s Safari browser. That's it, all your previous browsing history and search history are now deleted from Safari. If you don’t want to delete all search history in Safari
How to record running kilometers in keep? Where is the running track recorded?
Mar 12, 2024 am 11:10 AM
We all know that the above are very good sports-type software for us, which can help users complete various sports in real time, and we can also see some of the above trajectories during some running processes. You can learn more about it. Many users don’t know about some of the above functional information, so today I will give you a good explanation of some of the content and experience, so that everyone can better carry out various functions. Some choices. If you also want to know some tracks and records about your own running, be sure not to miss it. More high-quality content is waiting for you to learn about it. A lot of interesting strategy information is waiting for you. If If you also want to know, let’s take a look with the editor now.
