首页 » 猿-技术 » 正文

MySQL手记22 — Tips:不走索引就锁全表数据吗?

发表于: CoderCoder.cn · 作者: ·  2020-6-12 ·  1,098 views  ·  17 replies 
本站文章均为原创,转载请注明出处和链接!

今天和小伙伴讨论到:
      如果MySQL的加锁,没有走索引,走全表扫描的话,那么加锁是把所有的数据行都锁住,还是只锁住符合where条件的数据?
      确实,我们在工作中经常提醒开发人员,让SQL都能走索引,以使得加锁的范围越小越好。所以这个问题,还需要看一个方面:事务的隔离级别

Repeatable_Read隔离级别

表结构:name字段没有索引,在name字段上进行update操作:

CREATE TABLE `test3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
​
mysql> select * from test3;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | aaa       |
|  2 | bbb       |
|  3 | ccc333ccc |
|  4 | ddd       |
|  5 | eee       |
+----+-----------+
5 rows in set (0.00 sec)

      
开启一个事务,执行update进行加锁:

mysql> begin;
Query OK, 0 rows affected (0.01 sec)
​
​
mysql> update test3 set name='abc' where name='ccc333ccc';
Query OK, 1 row affected (0.02 sec)

      
查看当前加锁的情况:

root:information_schema> select * from INNODB_TRX \G
*************************** 1. row ***************************
                    trx_id: 458026
                 trx_state: RUNNING
               trx_started: 2020-06-12 05:00:40
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 972
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 6
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0

      
或者可使用**show engine innodb status **查看:

------------
TRANSACTIONS
------------
Trx id counter 458027
Purge done for trx's n:o < 458010 undo n:o  begin;
Query OK, 0 rows affected (0.00 sec)
​
root:wstestdb> update test3 set name
----等待

      
第二个事务执行后,处于等待状态,此时查看加锁的情况:

mysql> select * from information_schema.INNODB_TRX \G
*************************** 1. row ***************************
                    trx_id: 458027
                 trx_state: LOCK WAIT
               trx_started: 2020-06-12 05:10:27
     trx_requested_lock_id: 458027:77:3:2
          trx_wait_started: 2020-06-12 05:10:27
                trx_weight: 2
       trx_mysql_thread_id: 993
                 trx_query: update test3 set name='abc' where name='eee'
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 458026
                 trx_state: RUNNING
               trx_started: 2020-06-12 05:00:40
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 972
                 trx_query: select * from information_schema.INNODB_TRX
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 6
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.01 sec)

第二个事务的状态显示:trx_state: LOCK WAIT,即由于不能马上获得锁,所以需要等待。
      

结果二:

      即使第二个事务中,update的是不同的行,但是由于name字段上没有索引,所以InnoDB需要对所有的行及间隙上锁,所以会出现“LOCK WAIT”的状态。

Read-Committed隔离级别

      由于RC隔离级别是没有GAP锁的,所以在进行加锁的时候,只会对于符合条件的数据进行加锁:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
​
​
mysql> update test3 set name='abc' where name='ccc333ccc';
Query OK, 1 row affected (0.00 sec)

      
查看此时的加锁情况:

mysql> select * from information_schema.INNODB_TRX \G
*************************** 1. row ***************************
                    trx_id: 458034
                 trx_state: RUNNING
               trx_started: 2020-06-12 06:15:58
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 998
                 trx_query: select * from information_schema.INNODB_TRX
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.01 sec)

      
第二个session执行:

root:wstestdb> begin;
Query OK, 0 rows affected (0.00 sec)
​
​
root:wstestdb> update test3 set name='abc' where name='eee';
Query OK, 1 row affected (0.01 sec)
​

      

结果一:

可以看出,第二个session也是能够成功执行的,因为更改的是不同的行。

查看加锁的情况:

mysql> select * from information_schema.INNODB_TRX \G
*************************** 1. row ***************************
                    trx_id: 458035
                 trx_state: RUNNING
               trx_started: 2020-06-12 06:16:22
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 997
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 2
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 458034
                 trx_state: RUNNING
               trx_started: 2020-06-12 06:15:58
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 998
                 trx_query: select * from information_schema.INNODB_TRX
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.01 sec)

小结

这个例子也是“不可重复读”的一个体现:
      RC:没走索引时,可以更新不同的行
      RR:没走索引时,不可以更新不同的行      

   在RC级别下,虽然只更新了一行数据update test3 set name=’abc’ where name=’ccc333ccc’;,但是提交之后,再进行查询时,得到name=‘eee’的这行数据也被更新了。
   而RR级别通过GAP锁,防止其它的session更新所有的行与间隙,从而得到了一个可重复读取的结果。

本文链接: http://codercoder.cn/index.php/2020/06/mysql-note-22-tips-lock-status-when-not-using-index/
«上一篇: :下一篇»

评论区

  1. Avdotya发表于:2020-09-30 18:43

    Thanks for the article post.Really thank you! Great.

  2. Lilianaxyyz发表于:2020-12-03 07:33

    cbd oil for sale amazon reputable cbd oil companies how to make cbd oil

  3. Walkernvbl发表于:2020-12-10 12:07

    dapsone caps usa dapsone 1000caps online pharmacy dapsone caps medication

  4. Aubreybwgc发表于:2020-12-19 16:01

    viagra price viagra 120 mg united states cost of viagra

  5. Ridgehmjp发表于:2020-12-20 12:46

    levaquin 500 mg united kingdom order levaquin levaquin 250 mg uk

  6. binance kuvhurapool发表于:2021-05-20 14:16

    Spot on with this write-up, I really feel this amazing site needs a
    lot more attention. I’ll probably be back again to read more, thanks for the info!

  7. best dota 2 options binary发表于:2021-06-09 10:15

    Hey there! Would you mind if I share your blog with
    my myspace group? There’s a lot of folks that I
    think would really appreciate your content. Please let me
    know. Many thanks

  8. I like the helpful info you provide in your articles.
    I’ll bookmark your weblog and check again here regularly.

    I am quite sure I’ll learn a lot of new stuff right here!
    Good luck for the next!

  9. My spouse and I stumbled over here different web address and thought I should
    check things out. I like what I see so i am just following you.
    Look forward to exploring your web page for a second time.

  10. killer binary options secrets发表于:2021-06-09 20:07

    Fantastic post but I was wanting to know if you could write a litte
    more on this topic? I’d be very thankful if you could elaborate a little
    bit further. Appreciate it!

  11. bitcoin wallet application发表于:2021-06-10 01:32

    Hi! I just wanted to ask if you ever have any trouble with hackers?
    My last blog (wordpress) was hacked and I
    ended up losing many months of hard work due to no back up.
    Do you have any methods to prevent hackers?

  12. website dieses benutzers besuchen发表于:2021-06-10 10:11

    Today, I went to the beach with my children. I found a sea shell and
    gave it to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.” She put the
    shell to her ear and screamed. There was a hermit crab inside
    and it pinched her ear. She never wants to go back!

    LoL I know this is completely off topic but I had to
    tell someone!

  13. Every weekend i used to pay a visit this site, because i want enjoyment, for the reason that this
    this website conations genuinely pleasant funny material too.

  14. 우리카지노发表于:2021-06-11 08:21

    Hi this is kind of of off topic but I was wondering if blogs use
    WYSIWYG editors or if you have to manually code with HTML.

    I’m starting a blog soon but have no coding
    skills so I wanted to get guidance from someone with experience.
    Any help would be enormously appreciated!

  15. It’s perfect time to make some plans for the future and it’s
    time to be happy. I have read this post and if I could I want to suggest you some interesting things or tips.

    Maybe you can write next articles referring to this article.
    I want to read more things about it!

  16. site发表于:2021-06-12 20:36

    Hi colleagues, its impressive post about
    educationand completely explained, keep it up all thee time.

    site

  17. Johnnie发表于:2021-06-21 13:44

    Normally I don’t learn article on blogs, but I wish to say that this write-up very forced me to try and do it!
    Your writing style has been amazed me. Thanks, quite nice
    article.