| Bug #3175 | Add mechanism to serialize writes in InnoDB (e.g. for timestamp) | ||
|---|---|---|---|
| Submitted: | 15 Mar 2004 7:50 | Modified: | 13 May 2010 16:03 |
| Reporter: | Tim Bunce | Email Updates: | |
| Status: | Open | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S4 (Feature request) |
| Version: | OS: | ||
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[15 Mar 2004 8:42]
Jacqui Caren
The problme does not appear to platform specific - It appears to be easily replicated under Windows and/or linux. One word of warning - we are being forced to move to Firebird because of this problem, so it is a feature request that will result in (hopefully) keeping clients.
[16 Mar 2006 13:21]
Heikki Tuuri
Maybe using SELECT ... LOCK IN SHARE MODE would solve this problem? --Heikki
[23 Nov 2015 16:04]
Pawel Pogorzelski
Is there a workaround for this issue? Or plan to fix it?
Description: It is common for the 'timestamp' data type to be used as a way to monitor inserts and updates to a table. A process can repeatedly select rows where the timestamp is greater than the last change timestamp it has seen. Every time the table changes (except deletes) the new/modified rows are selected by the monitoring process. However, it is not safe to use this logic with InnoDB. The problem is that the order in which changes "become visible" via a commit isn't reliably the same order that the timestamps were assigned. This is a significant problem because it prevents people migrating applications from MyISAM to InnoDB tables without potentially significant code changes. How to repeat: Here's an example where threads A and B do updates and thread C does selects looking for new changes using: SELECT * FROM table WHERE last_update >= $max_last_update_from_previous_select AND last_update < NOW() -- only deal with past time IN SHARE MODE -- see latest commits 00:00:01.99 A: update row 1 sets timestamp to 00:00:01 00:00:02.00 C: select returns nothing as nothing has been commited yet 00:00:02.01 B: update row 2 sets timestamp to 00:00:02 00:00:02.02 C: select returns nothing as nothing has been commited yet 00:00:02.03 B: commit makes row 2 update visible 00:00:02.04 C: select returns row 2 00:00:02.05 A: commit makes row 1 update visible 00:00:02.06 C: select returns nothing as row 1 timestamp is less than 00:00:02 The update to row 1 wasn't seen by the monitoring process. Suggested fix: Add a mechanism for a session to serialize writes. Something like: SET SESSION.SERIALIZE_WRITES=1 -- or GLOBAL.SERIALIZE_WRITES which would then make any attempt to modify a table get a write lock on the whole table.