PHP Generating unique id from database

Question

In my project, i am generating a unique id from a table of database with taking largest integer value of an attribute 'serial_key' with adding 1 to that number. It is generating the unique index to add new tuple of records.

But this mechanism failed when i deployed the application on multiple PCs in intranet or internet, it was generating the same unique id on all different machines at an instant. And i have plenty of data in the server so i have to manage the same pattern of the id, since it was constructed taking a specific format. Please suggest how to resolve this problem. Thanks.

use mysql UUID(), you can also generate new number by using timestamp + ip — Haim Evgi, Aug 26 '13 at 06:08
Actually it generates the key on the load of the page and user fill and then submit. thats why auto incrementing doesnt work. — user2431351, Aug 26 '13 at 07:37
Why generate the key, let the submit return the key value in case you need it for something else. This way the Auto Increment could be used. — Steven Scott, Aug 26 '13 at 15:31

score 0 · Answer 1 · edited May 23 '17 at 11:49

0

You can use the HiLo Algorithm, to generate unique keys. It can be tuned for performance or continous keys. If you implement it in all your clients (i guess java and php) you would get unique keys across as many keys as you like (or your database performance allows). You would also not be dependent on any database and if you tune it for throughput you wouldn't need much additional database querys.

See this SO-Answer.

edited May 23 '17 at 11:49

Community

1
1

answered Aug 26 '13 at 06:11

ssindelar

2,833
1
17
36

A better solution it to use a similar allocator table, but just allocate ranges of say 20 integers from a linear number-space. (Rather than Ambler's ugly idea of allocating the high 16- or 32-bits, which typically wastes most of the low word & generates human-unfriendly keys.) – Thomas W Aug 26 '13 at 09:06

score 0 · Answer 2 · answered Aug 26 '13 at 06:15

0

You can solve this by using an AUTO_INCREMENT attribute on your serial_key column. That way you don't have to worry about data collision. This is a common practice for primary keys.

answered Aug 26 '13 at 06:15

pauk960

300
2
8

score 0 · Answer 3 · answered Aug 26 '13 at 09:21

Proper "allocator table" design for portable DB-based key allocation: to be used in preference to Scott Ambler's misguided "hi-lo" idea.

create table KEY_ALLOC (
    SEQ varchar(32) not null,
    NEXT bigint not null,
    primary key (SEQ)
);

To allocate the next, say, 20 keys (which are then held as a range in the server & used as needed):

select NEXT from KEY_ALLOC where SEQ=?;
update KEY_ALLOC set NEXT=(old value+20) where SEQ=? and NEXT=(old value);

Providing you can commit this transaction (use retries to handle contention), you have allocated 20 keys & can dispense them as needed.

This scheme is 10x faster than allocating from an Oracle sequence, and is 100% portable amongst all databases.

Unlike Scott Ambler's hi-lo idea, it treats the keyspace as a contiguous linear numberline -- from which it efficiently allocates small chunks of configurable size. These keys are human-friendly and large chunks are not wasted. Mr Ambler's idea allocates the high 16- or 32-bits, and requires either ugly composite keys or generating large human-unfriendly key values as the hi-words increment.

Comparison of allocated keys:

Linear_Chunk       Hi_Lo
100                65536
101                65537
102                65538
.. server restart
120                131072
121                131073
122                131073
.. server restart
140                196608

Guess which keys are easier to work with for a developer or DB admin.

I actually corresponded with him back in the 90's to suggest this improved scheme to him, but he was too stuck & obstinate to acknowledge the advantages of using a linear number-line.

PHP Generating unique id from database

3 Answers3