… hints in MySQL and SQL Server. In MySQL/Innodb LOCK IN SHARE MODE and SELECT FOR UPDATE are more than hints. Behavior will be different from normal SELECT…assume you can simply add SELECT FOR UPDATE to your select and reduce deadlocks if you’re updating selected rows. As query results may chance you…
Post: Logging Deadlock errors
September 19, 2012 By 4 Comments
…deadlocks u=root,h=127.0.0.1 After some time, we can check the deadlock information in our table: mysql> select…In MySQL 5.6 will be possible to log all deadlocks on error log using innodb_print_all_deadlocks…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
… to traditional MySQL replication. For those…mysql> commit; Query OK, 0 rows affected (0.00 sec) node3 mysql> select * from …mysql> select * from autoinc; +—+——-+ | i | j | +—+——-+ | 1 | node3 | | 4 | node2 | | 7 | node2 | +—+——-+ 3 rows in set (0.00 sec) Whoops! We got a deadlock…
Post: MySQL 5.6: Improvements in the Nutshell
January 27, 2013 By 16 Comments
…for my talk for Percona MySQL University in Raleigh,NC, Tuesday 29th of January I have created the outline of improvements available in MySQL…
Post: Checking for a live database connection considered harmful
May 5, 2010 By 38 Comments
… very common for me to look at a customer’s database and…timeouts and deadlocks, if you so desire. These are areas where many applications can benefit a lot, in my experience….the MySQL protocol level, which will increment Com_admin_commands in SHOW GLOBAL STATUS, or a trivial query such as ‘SELECT 1′…
Page: Consulting for MySQL
July 31, 2006 By
…for MySQL Want your developers to learn MySQL best practices ? How to work with large data sets in MySQL or how to optimize queries…MySQL and other databases for your application workload. Innodb Storage Engine Running transaction applications with MySQL ? Need help with deadlocks…
Post: Test Drive of Solid
September 22, 2006 By 5 Comments
… timeout for deadlock resolution. For workloads with frequent row waits this however would result in increased number of deadlocks. Test 2: SELECT FOR UPDATE: Session1: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test2 where id =2 for…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…mysql tables in use 1, locked 0 MySQL thread id 8079, query id 728899 localhost root Sending data select…queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 10099, id 88021936, state: waiting for…
Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes
July 25, 2012 By 6 Comments
…in set (0.00 sec) percona2 mysql> insert into percona values (0,’percona2′,’daniel’); Query OK, 1 row affected (0.02 sec) percona2 mysql> select…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…in which they were defined, and the PRIMARY KEY is always going to be considered as being defined first. So, MySQL…in our table: (root@localhost) [test]> select…queries per second all trying to lock and update the same row is not going to end well; deadlocks…
