A ticket server is a central service that creates unique IDs, or "tickets," for use in a distributed system. In distributed systems, making sure IDs are unique across different nodes or services is difficult. A ticket server solves this problem by reliably ensuring that every ID is unique, no matter which node requests it.
For example, financial systems need unique transaction IDs to keep track of and verify payments. When a user makes a payment, the system generates a unique transaction ID. This ID is then used to log, track, and reconcile the transaction.
In this post, I am going to explain how can we implement a ticket server using MySQL auto increment column.
Auto increment column
An AUTO_INCREMENT column in MySQL is a special column that automatically generates a unique number for each new record inserted into a table. This is especially helpful for creating primary keys, ensuring uniqueness, and making data management easier.
Example
CREATE TABLE employee (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
age INT NOT NULL
);
Above statement create an employee table with an id column that automatically increments, along with name and age columns.
Let us insert a record and observe the behavior.
mysql> INSERT INTO employee (name, age) VALUES ('Hari', 35);
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM employee;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | Hari | 35 |
+----+------+-----+
1 row in set (0.00 sec)
As you can see, the id is assigned the value 1. When we insert another record, the new record will be assigned with id 2.
mysql> INSERT INTO employee (name, age) VALUES ('Ram', 36);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM employee;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | Hari | 35 |
| 2 | Ram | 36 |
+----+------+-----+
2 rows in set (0.00 sec)
Can I have control on the starting offset and increment step on the ids?
Yes, in MySQL, you can control both the starting offset and the increment step for AUTO_INCREMENT columns. To control the increment step of the AUTO_INCREMENT values, we can configure the variables auto_increment_increment and auto_increment_offset.
SET @@auto_increment_increment=10;
SET @@auto_increment_offset=5;
Above statements set the Increment Step and Offset. It set the offset to 5 and increment to value 10.
mysql> SET @@auto_increment_increment=10;
Query OK, 0 rows affected (0.01 sec)
mysql> SET @@auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT * FROM employee;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | Hari | 35 |
| 2 | Ram | 36 |
+----+------+-----+
2 rows in set (0.00 sec)
mysql>
mysql> INSERT INTO employee (name, age) VALUES ('Lahari', 24);
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> INSERT INTO employee (name, age) VALUES ('Thulasi', 18);
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> SELECT * FROM employee;
+----+---------+-----+
| id | name | age |
+----+---------+-----+
| 1 | Hari | 35 |
| 2 | Ram | 36 |
| 5 | Lahari | 24 |
| 15 | Thulasi | 18 |
+----+---------+-----+
4 rows in set (0.00 sec)
Looking at the results above, Lahari has the ID number 5, and Thulasi has the ID number 15 because we set the increase to happen in steps of 10.
Let’s see how can we use this auto increment feature to generate unique identifiers
The MySQL REPLACE INTO command is used to insert a new row into a table, or replace an existing row if a unique key or primary key constraint is violated.
Reset the increment offset and increment size to 1.
SET @@auto_increment_increment=1;
SET @@auto_increment_offset=1;
Let’s
create a table and try to understand this behavior.
CREATE TABLE test (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) UNIQUE
);
Above ddl creates a table named "test" with columns "id" (auto-increment) and "name" (with a unique constraint).
Let’s insert a record into test table.
INSERT INTO test (name) VALUES ('Hari');mysql> INSERT INTO test (name) VALUES ('Hari');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT * FROM test;
+----+------+
| id | name |
+----+------+
| 1 | Hari |
+----+------+
1 row in set (0.00 sec)
Let’s insert a new record into test table using ‘REPLACE INTO’ command.
mysql> REPLACE INTO test (name) VALUES ('Ram');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT * FROM test;
+----+------+
| id | name |
+----+------+
| 1 | Hari |
| 2 | Ram |
+----+------+
2 rows in set (0.00 sec)
In this example, a row with the same primary key or unique key (name=’Ram’) doesn't exist, REPLACE INTO behaves like an INSERT statement and adds the new row to the table.
Let’s execute the same statement ‘REPLACE INTO test (name) VALUES ('Ram');’ again.
mysql> REPLACE INTO test (name) VALUES ('Ram');
Query OK, 2 rows affected (0.00 sec)
As you see the output, you can observe that 2 rows affected. It is because, If a row with the same primary key or unique key already exists in the table, REPLACE INTO deletes the existing row and inserts the new row. Since the record with name ‘Ram’ already exits, this record is deleted and new record is created.
mysql> SELECT * FROM test;
+----+------+
| id | name |
+----+------+
| 1 | Hari |
| 3 | Ram |
+----+------+
2 rows in set (0.02 sec)
How to retrieve the most recently generated AUTO_INCRMENT value?
‘SELECT LAST_INSERT_ID();’ statement in MySQL is used to retrieve the most recently generated AUTO_INCREMENT value that was inserted into a table.
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 3 |
+------------------+
1 row in set (0.01 sec)
Let’s design ticket server using what we learned so far
CREATE TABLE tickets_table (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
ticket_key CHAR(1) UNIQUE
);
Let’s insert a record into tickets_table.
mysql> CREATE TABLE tickets_table (
-> id BIGINT AUTO_INCREMENT PRIMARY KEY,
-> ticket_key CHAR(1) UNIQUE
-> );
Query OK, 0 rows affected (0.13 sec)
mysql>
mysql> INSERT INTO tickets_table (ticket_key) VALUES ('a');
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM tickets_table;
+----+------------+
| id | ticket_key |
+----+------------+
| 1 | a |
+----+------------+
1 row in set (0.00 sec)
Now, when a client request for new token, we are going to execute below SQL queries.
REPLACE INTO tickets_table (ticket_key) VALUES ('a');
SELECT LAST_INSERT_ID();
After executing REPLACE INTO tickets_table (ticket_key) VALUES ('a');, the row with ticket_key = 'a' will be deleted and a new row will be inserted with a new id. This returns the id of the newly inserted row is returned by 'SELECT LAST_INSERT_ID();'
mysql> REPLACE INTO tickets_table (ticket_key) VALUES ('a');
Query OK, 2 rows affected (0.01 sec)
mysql>
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT * FROM tickets_table;
+----+------------+
| id | ticket_key |
+----+------------+
| 2 | a |
+----+------------+
1 row in set (0.00 sec)
However, there's a potential issue relying on a single MySQL instance to generate IDs creates a single point of failure. To address this, we can use two MySQL instances. One instance will generate IDs with even numbers, and the other will generate IDs with odd numbers.
For the first server
SET @@auto_increment_increment = 2;
SET @@auto_increment_offset = 1;
For the second server
SET @@auto_increment_increment = 2;
SET @@auto_increment_offset = 2;
By setting auto_increment_increment to 2, both servers will increment IDs by 2. The auto_increment_offset ensures that one server starts at 1 (odd numbers) and the other at 2 (even numbers), thus avoiding collisions and distributing the ID generation between the two servers.
References
https://code.flickr.net/2010/02/08/ticket-servers-distributed-unique-primary-keys-on-the-cheap/
No comments:
Post a Comment