Potential replication problem with auto increment on multi processor machine
am 10.06.2003 11:43:50 von Mike Hilton------_=_NextPart_001_01C32F34.CC04FE30
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Summary: Data integrity (potentially) lost as auto increment
field used as target of foreign key not replicated predictably on multi
processor slave
Description:
We have seen a problem on a previous release (32 bit binary
MySQL 4.0.5) which we are trying to reproduce on 4.0.13. The problem
occurred where we had a number of [InnoDB] tables (e.g. user_profile)
with foreign key relationships to another table (e.g. user). The foreign
key relates to an auto increment generated id field in the master (user)
table. (Under load) when these tables are replicated, the slave auto
increment generates the user id value locally, and as the slave is a
multi processor machine, the original order of row creation in the
master is not preserved when replicated. This leads to the foreign key
integrity being lost on the slave.=20
The (relevant parts) of the tables are defined as :
CREATE TABLE user (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(255)
) TYPE =3D InnoDB;
CREATE TABLE user_profile(
user_id INT(10) UNSIGNED NOT NULL,
attribute_key VARCHAR(20),
value_text VARCHAR(255)
) TYPE =3D InnoDB;
(where user_profile.user_id is then defined as a foreign key to
user.id).
What is the recommended approach for ensuring data integrity is
maintained when replicating auto increment fields to a multi processor
slave ?
[
- In theory, I guess the problem could occur if either the master or the
slave was multiprocessor - e.g. :
If user rows id 1,and 2 were to be created + an associated user profile
row with foeign key relationship :
1) Potential problem caused by multi processor master :
1.
2.
3.
4.
5.
6.
command written to binlog
7.
8.
command written to binlog
This could lead to problems, as 3. is written to the binlog before 4.
owing to race conditions, and when picked up and processed on the slave
in (binlog written) order, 3. would (incorrectly) create a row with auto
generated row id 1.
Alternatively,=20
2) Potential problem caused by multi processor slave :
1.
2.
3.
4.
5.
6.
command written to binlog
7.
8.
command written to binlog
In this case, if the slave picked the above up, and owing to race
conditions, processor B executed 4. before processor A could execute 3,
again, a user row would end up being created with an id that was
inconsistent with the appropriate 'child'/associated user_profile row.
I can't really see at the moment, how MySQL can solve this - even
locking the binlog file won't necessarily guarantee that processor race
conditions are avoided. They could try to route all auto increment
operations through a single processor, but this would suggest a
scalability roadblock (even if it was technically feasible).
A couple of workaround ideas :
(I) Could we work around this issue if the code always did a :
'SELECT id from user where user_name=3D'match_criteria>'
(with some kind of lock ?), rather than using the LAST_INSERT_ID()
function ?
(II) Perhaps we could put any code that DOES have to use LAST_INSERT_ID
within the same transaction (commit unit) as the code that creates a row
with an auto incremented value. This might be sufficient to ensure that
(foreign key) related rows at least preserve their data integrity - even
if the actual auto incremenet generated id is not the same ?
(III) [- the current workaround] - generate the key on the master
WITHOUT using auto increment, and then just replicate an INT field
(rather than an auto_increment INT field)
]
Database Architect
New Media Infrastructure
BBC New Media & Technology
Bush House
Aldwych, London, WC2B 4PH
=20
Tel : 0207 557 3988 (direct)
email :
=20
BBCi at http://www.bbc.co.uk/
This e-mail (and any attachments) is confidential and may contain=20
personal views which are not the views of the BBC unless specifically=20
stated.
If you have received it in error, please delete it from your system, do=20
not use, copy or disclose the information in any way nor act in=20
reliance on it and notify the sender immediately. Please note that the=20
BBC monitors e-mails sent or received. Further communication will=20
signify your consent to this.
------_=_NextPart_001_01C32F34.CC04FE30--