Unique primary keys(PKs) for MySQL using Redis

Category:

Tutorials > Database Management Systems > MySQL > Tips and Tricks

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?

Buy me a coffee at ko-fi.com

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I do not guarantee a response.

IMPORTANT: You must thoroughy test any instructions on a production-like test environment first before trying anything on production systems. And, make sure it is tested for security, privacy, and safety. See our terms here.

Comments (write a comment):

Maybe a better way would be to use UUID instead of PKs, they have a lots of advantages over ID increment.
Either way, your article will be shared in our next RedisWeekly! ( http://redisweekly.com ) Posted by: Redsmin { on Apr 24, 2016

@Redsmin: I dont know about "better way" but thats certainly a possibility.

However:
With UUIDs there is no way to tell if a record is inserted before other (need timestamp etc).

Then, integer comparisons should be much faster (eg. in JOINs, WHERE clauses etc) compared to UUID. You can use less than, greater than comparisons as well.

Also, IDs should take less space, in turn taking less memory and disk space (including memory for temp tables etc. during query execution). This also reduces the size of indexes. For Innodb, PK is also included in secondary indexes, so that will add to size again.

The list goes on. And, you don't need Redis to generate UUIDs. Also, I am aware of techniques to simplify/optimize UUID storage (beyond the scope here). Posted by: Geeksww on Apr 29, 2016

leave a comment on tutorial leave a comment