Shulou(Shulou.com)06/01 Report--

The MySQL innodb storage engine uses the same row locking mechanism as Oracle, and you will see how to view row locks that exist in the system in the following lab. Here is the test process:

Session 1: update record

Mysql > set autocommit=off

Query OK, 0 rows affected (0.01 sec)

Mysql > update T1 set email='test@test.com' where id=0

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4 Changed: 4 Warnings: 0

Session 2: update the same record and wait

Mysql > set autocommit=off

Query OK, 0 rows affected (0.00 sec)

Mysql > update T1 set email='abc' where id=0

Session 3: view system wait events:

Mysql > show status like'% lock%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Com_lock_tables | 0 | |

| | Com_unlock_tables | 0 | |

| | Innodb_row_lock_current_waits | 1 |-- here |

| | Innodb_row_lock_time | 0 | |

| | Innodb_row_lock_time_avg | 0 | |

| | Innodb_row_lock_time_max | 0 | |

| | Innodb_row_lock_waits | 1 | |

| | Key_blocks_not_flushed | 0 | |

| | Key_blocks_unused | 14497 | |

| | Key_blocks_used | 0 | |

| | Qcache_free_blocks | 1 | |

| | Qcache_total_blocks | 1 | |

| | Table_locks_immediate | 2070991 | |

| | Table_locks_waited | 2 | |

+-+ +

14 rows in set (0.01 sec)

Session 1: submit record

Mysql > commit

Query OK, 0 rows affected (0.01 sec)

Session 2:update will finish it immediately.

Mysql > update T1 set email='abc' where id=0

Query OK, 4 rows affected (2 min 43.44 sec)-such a long time to complete the update operation

Rows matched: 4 Changed: 4 Warnings: 0

Session 3: view the system wait event again

Mysql > show status like'% lock%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Com_lock_tables | 0 | |

| | Com_unlock_tables | 0 | |

| | Innodb_row_lock_current_waits | 0 |-- 0 here |

| | Innodb_row_lock_time | 163436 | |

| | Innodb_row_lock_time_avg | 163436 | |

| | Innodb_row_lock_time_max | 163436 | |

| | Innodb_row_lock_waits | 1 | |

| | Key_blocks_not_flushed | 0 | |

| | Key_blocks_unused | 14497 | |

| | Key_blocks_used | 0 | |

| | Qcache_free_blocks | 1 | |

| | Qcache_total_blocks | 1 | |

| | Table_locks_immediate | 2070991 | |

| | Table_locks_waited | 2 | |

+-+ +

14 rows in set (0.01 sec)

Query the connection ID of session session 1 and session 2

Session 1:

Mysql > status

-

Mysql Ver 12.22 Distrib 4.0.24, for pc-solaris2.10 (i386)

Connection id: 15

Session 2:

Mysql > status

-

Mysql Ver 12.22 Distrib 4.0.24, for pc-solaris2.10 (i386)

Connection id: 13

When session 1 above has not been committed, you can execute the following command to view some transaction blocking information

Mysql > show innodb status\ G

-

TRANSACTIONS

-

Trx id counter 0 3852351

Purge done for trx's NRO < 0 3852350 undo NRO < 0

History list length 11

Total number of lock structs in row lock hash table 7

LIST OF TRANSACTIONS FOR EACH SESSION:

-TRANSACTION 0 0, not started, OS thread id 15

MySQL thread id 18, query id 2071119 localhost root

Show innodb status

-TRANSACTION 0 3852350, ACTIVE 6 sec, OS thread id 14 starting index read

Mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct (s), heap size 320

MySQL thread id 13, query id 2071118 localhost test Updating-you can see the waiters here

Update T1 set email='abc' where id=0-here you can see the SQL being executed by the waiters

-TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 32782 n bits 1056 index `idx_t1_ id` of table `dc_test/ t1` trx id 0 3852350 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 80000000; asc;; 1: len 6; hex 00000196fe5d; asc]

-

-TRANSACTION 0 3852348, ACTIVE 391sec, OS thread id 12

7 lock struct (s), heap size 1024, undo log entries 4

MySQL thread id 15, query id 2071117 localhost test

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.