Databases Reference
In-Depth Information
Figure 10-17. Two transactions update data, but without a shared lock to serialize the updates
• It makes it harder to move servers around in the replication topology.
• It wastes key space by potentially introducing gaps between numbers.
• It doesn't help unless all your tables have
AUTO_INCREMENT
primary keys, and it's
not always a good idea to use
AUTO_INCREMENT
primary keys universally.
You can generate your own nonconflicting primary key values. One way is to create a
multicolumn primary key and use the server ID for the first column. This works well,
but it makes your primary keys larger, which has a compound effect on secondary keys
in InnoDB.
You can also use a single-column primary key, and use the “high bits” of the integer to
store the server ID. A simple left-shift (or multiplication) and addition can accomplish
this. For example, if you're using the 8 most significant bits of an unsigned
BIGINT
(64-
bit) column to hold the server ID, you can insert the value
11
on server 15 as follows:
mysql>
INSERT INTO test(pk_col, ...) VALUES( (15 << 56) + 11, ...);
If you convert the result to base 2 and pad it out to 64 bits wide, the effect is easier to see:
mysql>
SELECT LPAD(CONV(pk_col, 10, 2), 64, '0') FROM test;
+------------------------------------------------------------------+
| LPAD(CONV(pk_col, 10, 2), 64, '0') |
+------------------------------------------------------------------+
| 0000111100000000000000000000000000000000000000000000000000001011 |
+------------------------------------------------------------------+
