5

These are simple UPDATEs on very small tables in an InnoDB database. On occasion, an operation appears to lock, and doesn't timeout. Then every subsequent UPDATE ends with a timeout. The only recourse right now is to ask my ISP to restart the daemon. Every field in the table is used in queries, so all the fields are indexed, including a primary.

I'm not sure what causes the initial lock, and my ISP doesn't provide enough information to diagnose the problem. They are reticent about giving me access to any settings as well.

In a previous job, I was required to handle similar information, but instead I would do an INSERT. Periodically, I had a script run to DELETE old records from the table, so that not so many records needed to be filtered. When SELECTing I used extrapolation techniques so having more than just the most recent data was useful. This setup was rock solid, it never hung, even under very heavy usage.

I have no problem replacing the UPDATE with an INSERT and periodic DELETEs, but it just seems so clunky. Has anyone encountered a similar problem and fixed it more elegantly?

Current Configuration

  • max_heap_table_size: 16 MiB
  • count(*): 4 (not a typo, four records!)
  • innodb_buffer_pool_size: 1 GiB

Edit: DB is failing now; locations has 5 records. Sample error below.

MySQL query:

UPDATE locations SET x = "43.630181733", y = "-79.882244160", updated = NULL
    WHERE uuid = "6a5c7e9d-400f-c098-68bd-0a0c850b9c86";

MySQL error:

Error #1205 - Lock wait timeout exceeded; try restarting transaction

locations
Field      Type         Null  Default
uuid       varchar(36)  No
x          double       Yes    NULL
y          double       Yes    NULL
updated    timestamp    No     CURRENT_TIMESTAMP 


Indexes:
Keyname    Type     Cardinality  Field
PRIMARY    PRIMARY  5            uuid
x          INDEX    5            x
y          INDEX    5            y
updated    INDEX    5            updated
Yimin Rong
  • 1,890
  • 4
  • 31
  • 48
  • Indexes slow down writes especially if you have all the fields indexed. how much memory do you have? how many records? what's your buffer_pool_size? too many questions here – Sam Feb 21 '14 at 14:54
  • I provided some answers. The installation is on FatCow, so I'm likely sharing the MySQL server with other users. – Yimin Rong Feb 21 '14 at 15:45
  • When in this state, what does `SHOW INNODB STATUS` return? Anything interesting about the update query - subselects? thousands of columns? – Matthew Feb 24 '14 at 17:25
  • `SHOW INNODB STATUS` not permitted. Four columns: one UUID key, two doubles and one timestamp; single table, no sub-selects. – Yimin Rong Feb 24 '14 at 17:30
  • Is it just an update query or are you doing `select ... for update`. Is there a mix of InnoDB and MyISAM in the database (although it sounds like the update is not doing a join of any kind anyway...) – Matthew Feb 24 '14 at 17:34
  • Have you tried to reproduce in a dev environment with the same setup where you do have access to the db? Will your provider provide details on their MySQL configuration? – Matthew Feb 24 '14 at 17:36
  • Just InnoDB, plain update queries. They have "Show MySQL runtime information", "Show MySQL system variables", and "Show processes". There is so much information, I don't know what to look for. – Yimin Rong Feb 24 '14 at 18:10
  • Please provide the table schemas and an example `UPDATE` which is problematic. – jeremycole Feb 24 '14 at 23:05
  • isn't a `where` missing in your `update`? – Walter Tross Feb 25 '14 at 07:09
  • @WalterTross - No, this is a query to update all the `updated` fields to the current timestamp. But simple updates using `WHERE` fail, too. 2014-02-24T22:42:26-05:00 UPDATE `locations` SET `x` = "43.630181733", `y` = "-79.882244160", `updated` = NULL WHERE `uuid` = "6a5c7e9d-400f-c098-68bd-0a0c850b9c86" 2014-02-24T22:43:17-05:00 -1 Lock wait timeout exceeded; try restarting transaction – Yimin Rong Feb 25 '14 at 08:00
  • could the single-row updates be colliding with table updates? Table updates are never a good idea, anyway. How many table updates, how many row updates, and how many table and row selects are you doing per second? – Walter Tross Feb 25 '14 at 09:05
  • The table-wide update was just for testing purposes to "refresh" the records. Typically, in production would not do table-wide updates. So zero table-wide updates, one record update/minute per user, (sometimes complex) select queries every minute. – Yimin Rong Feb 25 '14 at 14:43
  • Have you any after update trigger on that table? What does the `SHOW FULL PROCESSLIST` shows when performing the update? – Cedric Simon Feb 25 '14 at 15:25
  • @CedricSimon - That operation isn't permitted by my ISP, so can't answer, sorry. – Yimin Rong Feb 25 '14 at 15:51
  • @Yimin Ron : If your are not able to use debugging tools, and you can't reproduce it on your local, I don't think we can be of much help. I suppose you can't access the my.cnf file either, to check configuration details... – Cedric Simon Feb 25 '14 at 17:16
  • have you given `replace`s a try? – Walter Tross Feb 25 '14 at 17:46
  • Not yet. Database has locked up, so trying to get ISP to unlock it. – Yimin Rong Feb 25 '14 at 17:48
  • What is the query that is "locked but not timing out" -- try to capture a `SHOW FULL PROCESSLIST` while it is locked. – jeremycole Feb 25 '14 at 20:11
  • @jeremycole - `SHOW FULL PROCESSLIST` is not permitted under existing ISP. – Yimin Rong Feb 25 '14 at 20:14
  • do you have explicit transactions or locks anywhere in your code? – Walter Tross Feb 26 '14 at 07:19
  • None, just using autocommit. – Yimin Rong Feb 26 '14 at 10:49
  • have you tried checking if the table is locked before executing your query? – mamdouh alramadan Feb 26 '14 at 17:54
  • No, how do I do that? – Yimin Rong Feb 26 '14 at 21:14
  • it's not a good idea to assign the bounty before it expires, especially if it's not assigned to your accepted answer. Many people only see bounty questions when they are close to their expiration (which BTW is followed by a grace period), because that's when they reach the first page. In other words, you missed the chance to find a TRUE answer to your question. – Walter Tross Feb 28 '14 at 23:42
  • @WalterTross - I know, but going on vacation in a few hours, and won't have access to Internet. If I let it default, only half the bounty is awarded. As far as who got what, I like the accepted answer because it led me to some realizations about InnoDB, but also appreciated the work by the one who won the bounty, and it was critical in understanding why the DB was locking. SO doesn't let you split bounties, so this was the best compromise. – Yimin Rong Mar 01 '14 at 09:48
  • I give up understanding why you deemed those two answers useful – Walter Tross Mar 01 '14 at 10:29

3 Answers3

6

It's a known issue with InnoDB, see MySQL rollback with lost connection. I would welcome something like innodb_rollback_on_disconnect as mentioned there. What's happening to you is that you're getting connections disconnected early, as can happened on the web, and if this happens in the middle of a modifying query, the thread doing that will hang but retain a lock on the table.

Right now, accessing InnoDB directly with web services is vulnerable to these kinds of disconnects and there's nothing you can do within FatCow other than ask them to restart the service for you. Your idea to use MyISAM and low priority is okay, and will probably not have this problem, but if you want to go with InnoDB, would recommend an approach like the following.

1) Go with stored procedures, then the transactions are guaranteed to run to completion and not hang in the event of a disconnect. It's a lot of work, but improves reliability big time.

2) Don't rely on auto commit, ideally set it to zero, and explicitly begin and end each transaction with BEGIN TRANSACTION and COMMIT.

Community
  • 1
  • 1
  • I can immagine an early disconnect only because of a program crash. No correctly handled timeout should do that. Anyway, I really don't understand why explicit transactions should be safer than `autocommit` – Walter Tross Feb 25 '14 at 20:52
  • The situation described in the question you linked does not apply, because the OP is not using explicit transactions, only simple updates with autocommit. Even if (ad absurdum) one unplugged the cable, as in that question, in the middle of the update, the update should complete and autocommit within a few milliseconds. Deadlocks (as could happen with updates where indices are not locked in the same order) should be detected immediately, just to rule out another possible cause for the hanging of queries that the OP is seeing. – Walter Tross Feb 27 '14 at 09:01
2
transaction1> START TRANSACTION;
transaction1> SELECT * FROM t WHERE i > 20 FOR UPDATE;
+------+
| i |
+------+
| 21 |
| 25 |
| 30 |
+------+

transaction2> START TRANSACTION;
transaction2> INSERT INTO t VALUES(26);
transaction2> COMMIT;
transaction1> select * from t where i > 20 FOR UPDATE;

+------+
| i |
+------+
| 21 |
| 25 |
| 26 |
| 30 |
+------+

What is a gap lock?

  1. A gap lock is a lock on the gap between index records. Thanks to this gap lock, when you run the same query twice, you get the same result, regardless other session modifications on that table.

  2. This makes reads consistent and therefore makes the replication between servers consistent. If you execute SELECT * FROM id > 1000 FOR UPDATE twice, you expect to get the same value twice.

  3. To accomplish that, InnoDB locks all index records found by the WHERE clause 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 t;
+------+
| age |
+------+
| 21 |
| 25 |
| 30 |
+------+

Start a transaction and delete the record 25:

transaction1 > START TRANSACTION;
transaction1 > DELETE FROM t WHERE age=25;

At this point we suppose that only the record 25 is locked. Then, we try to insert another value on the second session:

transaction2 > START TRANSACTION;
transaction2 > INSERT INTO t VALUES(26);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
transaction2 > INSERT INTO t VALUES(29);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
transaction2 > INSERT INTO t VALUES(23);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
transaction2 > INSERT INTO t VALUES(31);
Query OK, 1 row affected (0.00 sec)

After running the delete statement on the first session, not only the affected index record has been locked but also the gap before and after that record with a shared gap lock preventing the insertion of data to other sessions.

jmail
  • 5,944
  • 3
  • 21
  • 35
  • So, given the uncertainties of the web, maybe I shouldn't be using InnoDB and transactions? Maybe use MyISAM and use "LOW PRIORITY" for updates? – Yimin Rong Feb 25 '14 at 08:05
  • Also, is there a way to boot off any pending transactions? – Yimin Rong Feb 25 '14 at 08:29
  • MyISAM is the default storage engine. It is based on the older (and no longer available) ISAM storage engine but has many useful extensions. Each MyISAM table is stored on disk in three files. – jmail Feb 25 '14 at 09:12
  • MyISAM is not the default storage engine any more, [since MySQL 5.5.5](http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html) – Walter Tross Feb 25 '14 at 13:43
  • 1
    For me, it doesn't matter if it's not the default engine any more. If InnoDB is not stable in the particular environment for whatever reason, then it's not stable. I'm trying to get the ISP to restart the daemon so I can convert the tables to MyISAM and redo the stability tests. I don't like the idea of table-wide locking in MyISAM, but if I specify UPDATE LOW PRIORITY, then that might work out okay. – Yimin Rong Feb 25 '14 at 14:49
  • @YiminRong, at your loads you don't need that – Walter Tross Feb 25 '14 at 15:22
  • Maybe not at current loads, but I intend to ramp up to 1000's of users, which is why I'm freaking out that it's not stable at < 10 users. – Yimin Rong Feb 25 '14 at 15:50
  • This doesn't have anything to do with *gap* locking if the `UPDATE` is updating all rows. It's just normal record locking. Using MyISAM or `UPDATE ... LOW PRIORITY` is not a solution, at best it would be a hack/workaround papering over a much more serious problem in your application or connection management. – jeremycole Feb 25 '14 at 18:10
  • @jeremycole - Will modify the example in the question. It's not a typical query, and too many people are following this red herring. – Yimin Rong Feb 25 '14 at 20:07
0

If your UPDATE is literally:

UPDATE locations SET updated = NULL;

You are locking all rows in the table. If you abandon the transaction while holding locks on all rows, of course all rows will remain locked. InnoDB is not "unstable" in your environment, it would appear that it is doing exactly what you ask. You need to not abandon the open transaction.

jeremycole
  • 2,741
  • 12
  • 15
  • 1
    No, that was just one I was using during testing in the ISP provided MySQL admin portal. Most of the time, the queries are single record updates done via PHP mysqli. – Yimin Rong Feb 25 '14 at 19:23