Unique primary keys(PKs) for MySQL using Redis
MySQL provides an auto increment feature (AUTO_INCREMENT attribute) that can be used to generate unique identities for new rows/records.
However, this may become a problem when you are writing to more than one MySQL databases and merging the records/rows, since all separate servers will be generating their own unique keys. There are some solutions available but they have their own issues (and are MySQL specific).
I recently thought about using an external service or database table to generate IDs and will be discussing it in this tutorial.
This may be helpful to avoid primary key conflicts between DB servers/masters/shards etc.
The basic idea is very simple ie. we use Redis's INCR command to create or increment an integer key that'll serve as a centralized location for IDs. You can have multiple keys for multiple tables.
In order to avoid hitting the Redis server each time you want to insert a new row in the database, you can use the following method to get a pool of PK IDs to be used and store it in a local cache.
127.0.0.1:6379> MULTI OK 127.0.0.1:6379> INCR cntr QUEUED 127.0.0.1:6379> INCRBY cntr 50 QUEUED 127.0.0.1:6379> EXEC 1) (integer) 102 2) (integer) 152
Here is whats happening:
- Start a Redis transaction using MULTI to make sure other clients are not making any changes to the same key at the same time.
- Use 'INCR' to initialize (and/or increment by 1) the table PK ID counter ('cntr' in code above)
- Get the current PK counter value from Redis (same 'INCR' command again)
- Use 'INCRBY' to increment it by buffer size ('50' in code above)
- Execute the transaction (using 'EXEC') for atomicity and isolation, so we are sure no other Redis clients can get the same buffer of IDs.
- Store the PK IDs pool range in a local cache and use it for as long as you've IDs in local pool available. If for any reason, the pool info is lost (in case of a crash etc) then you either need to get the pool data from Redis again (leaving gaps in IDs) OR dump (before crash) and restore (during restart) the local ID buffer cache from a DB or persistent store and double check with DB that its valid/usable. Please make sure you update local cache before using the actual ID in DB to avoid any conflicts in case of restart after a crash etc.
It is recommended to initialize all such tables right in the beginning and thorough testing in test environment is highly recommended.
Did this tutorial help a little? How about buy me a cup of coffee?
Please feel free to use the comments form below if you have any questions or need more explanation on anything. I recommend thoroughy testing on a production-like test system first before moving to production.