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

First of all, we know that MySQL supports a variety of engines, and there are many differences among different storage engines. The most important thing is that even in auto-commit mode, commit; must be shown to release lock resources after start transaction;. Myisam's locking mechanism: row-level locks are not supported, table-level locks are supported, which are divided into shared read locks and exclusive write locks. 1) what is a table lock: the operation object is a data table. Mysql supports most lock policies (common mysql innodb), which is the one with the lowest system overhead but the lowest concurrency. If transaction t adds a read lock to the whole table, other transactions can not be read or written. 2) what is a read lock: also known as shared lock, S lock, if transaction T adds S lock to table A, transaction T can read table A but cannot modify table A, and other transactions can only add S lock to table An instead of X lock until T releases the S lock on A. This ensures that other transactions can read table A, but no changes can be made to table A until transaction T releases the S lock on table A. to sum up: you can't write while I'm reading; 3) what is a write lock: also known as exclusive lock, X lock. If transaction T adds an X lock to table A, transaction T can read table An or modify table A, and other transactions can no longer add any locks to table A until transaction T releases the lock on table A. This ensures that other transactions cannot read and modify Table A until transaction T releases the lock on Table A. to sum up: when I write, you can't read or write. Experiment 1: when I read, you can't write, but you can read; first, the table company_info is the table of the myisam engine; transaction 1, perform a long query operation mysql > select company_id from company_info where company_name like'% liluwedafasdf%'; Empty set (43.27 sec) transaction 2, try to update the table company_info, find waiting. It was executed for 21 seconds. Mysql > update company_info set company_name='liuhehhe' where company_id='4028809f60bf40fd0160bf4678be0000' +-- +-+ | company_id | company_name | +-+-+ | 4028809f60bf40fd0160bf4678be0000 | liuhehhe | + -+-+ Query OK 0 rows affected (21.98 sec) Rows matched: 1 Changed: 0 Warnings: 0 Open another window As you can see, transaction 2 is indeed waiting for a table-level lock: mysql > show processlist +-+-- + -+ | Id | User | Host | db | Command | Time | State | Info | +-+ -+- -- + | 14 | root | localhost | liuwenhe | Sleep | 4506 | | NULL | | 15 | root | localhost | liuwenhe | Query | 1 | Sending data | select company_id from company_info where company_name like'% liluwedafasdf%' | | 16 | root | localhost | liuwenhe | Query | 1 | Waiting for table level lock | update company_info set company_name='liuhehhe' where company_id='4028809f60bf40fd0160bf4678be0000 | | 17 | Root | localhost | NULL | Query | 0 | init | show processlist | +-+ -+ 4 rows in set (0.00 sec) and then open a window Execute query operation (transaction 3): transaction 3 is finished before transaction 1 is finished, indicating that transaction 1 will not block transaction 3. Mysql > select company_name from company_info where company_id='4028809f60bf40fd0160bf4678be0000' +-+ | company_name | +-+ | liuhehhe | +-+ 1 row in set (2.10 sec) experiment 1 proves that if transaction 1 adds an S lock to table A, transaction 1 can read table A but cannot modify table A, and other transactions can only add S lock to table An instead of X lock. Until T releases the S lock on A. That is to say, you can't write while I'm reading, but you can read; experiment 2: when I'm writing, you can't read or write; transaction 1: perform updating a field of a table. Mysql > update company_info set company_name='liuhehhe' where company_name like'% liuwehe%'; Query OK, 0 rows affected (1.95 sec) Rows matched: 0 Changed: 0 Warnings: 0 transaction 2 attempted to query this table and found waiting. Mysql > select count (*) from company_info; +-+ | count (*) | +-+ | 1818708 | +-+ 1 row in set (1.11 sec) opens another window to view the process and finds that transaction 2 is indeed waiting for a table-level lock Mysql > show processlist +-+-- + -+ | Id | User | Host | db | Command | Time | State | Info | + -- + -- + | 19 | root | localhost | liuwenhe | Query | 1 | updating | update company_info set company_name='liuhehhe' where company_name like'% liuwehe%' | | 20 | root | localhost | liuwenhe | Query | 1 | Waiting for table level lock | select count (*) from company_info | | 21 | root | localhost | NULL | Query | 0 | init | show processlist | + + -+ 3 rows in set (0.00 sec) experiment 2 proves that: under the myisam engine of mysql Updating a row of data in a table will also result in an exclusive lock at the previous table level, so that other transactions are not allowed to read the data from the table, and of course, other transactions are not allowed to write the table, that is to say, when I write, you cannot read or write. Mysql myisam lock summary: MyISAM read and write operations, as well as write operations are serial! MyISAM automatically locks the table when performing read and write operations (that is, using lock table commands without display, and then paying attention to table-level locks). MyISAM always acquires all the locks needed by SQL statements at once, which is the reason why MyISAM will not have deadlocks. Different from the lock mechanism of mysql innodb, the general select of innodb does not add any locks. Next we will introduce the lock mechanism of innodb. Please see the next article.

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.