Hi Miguel ， I’ve tried your example “the affect gap locks to SELECT … FOR UPDATE ” on my server ,and my results are… affected (0.00 sec) root@localhost:test 14:35:29>select * from t; +——+ | i | +——+ | 21 | | 30 | | 26 | +——+ 3 rows in set… id 1100101952 inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1216, 1 row lock(s) MySQL thread id…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…created which temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row …cache 512619219 (509995888 + 2623331) File system 294352 (82672 + 211680) Lock system 318875832 (318747272 + 128560) Recovery system 0 (0 + 0…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… with an exclusive lock and the gaps between them with a shared gap lock. This lock doesn’t only affect to SELECT … FOR UPDATE. This is an example with a DELETE statement: transaction1 > SELECT * FROM… source of information: http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… it works. Even though with general lock compatibility guidelines Read Lock should not conflict with another Read Lock, it does for this statement… any SELECT statement to complete in order to complete locking tables. This means if you have workload which includes some very long SELECT queries you can be potentially waiting for hours for this statement to complete. Here is example how it can look: mysql…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…0.00 sec) mysql> select count(*) from A; +———-+ | count(*) | +———-+ | 2359296 | +———-+ 1 row in set (1.73 sec) mysql> select * from C; +——+ | t | +——+ … = @saved_cs_client */; — – Dumping data for table `C` — LOCK TABLES `C` WRITE; /*!40000 ALTER TABLE `C` DISABLE KEYS …
Comment: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
March 11, 2012 By 7 Comments
… 5.1.22 change autoincrement mod http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling…. – consecutive lock mode # =2 is interleaved lock mode , i don’t investigate what it do innodb_autoinc_lock_mode=0 2. restart mysql server… into tab (name) values (‘Peter’),(‘Oto’),(‘Jan’),(‘Jan’),(‘Jan’),(‘Romco’); select * from tab order by 1; 1 Peter 2 Oto 3…
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
… with slow query output. The tables are in InnoDB with MySQL 5 and RHEL 5. Its a 32 bit machine with… = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data innodb_buffer_pool_size = 2000M innodb_additional_mem_pool…_lock_wait_timeout = 50 A count command with approx 3 million records is taking too long and all my transactions are locked. select…
Post: SELECT LOCK IN SHARE MODE and FOR UPDATE
August 6, 2006 By 51 Comments
…: mysql> commit; Query OK, 0 rows affected (0.01 sec) SESSION2: mysql> select * from tst; Empty set (0.00 sec) mysql> select * from tst lock in share mode; +—+ | i | +—+ | 1 | +—+ 1 row in set (0.00 sec) mysql> select * from tst for update; +—+ | i | +—+ | 1 | +—+ 1 row in set (0.00 sec) #Standard SELECT does not see rows while SELECT for…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…_event_wait_low,sync_array_wait_event,rw_lock_s_lock_spin,pfs_rw_lock_s_lock_fu nc,btr_search_build_page_hash…_multi_range_next,QUICK_RANGE_SELECT::get _next,rr_quick,sub_select,do_select,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,my sql…_next,QUICK_RANGE_SELECT::get_next,rr_quick,sub_select,do_select,JOIN::exec,mysql_select,handle_select,execu te_sqlcom_select,mysql_execute_command,mysql_parse,dispatch_command…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
…6238) ==9090== by 0x6A5E0C: JOIN::exec() (sql_select.cc:1863) ==9090== by 0x6A7D72: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List
