[RESOLVED] Help on Unique identifier

tnylsej

Hi all..

I have a table to store information of printers. Every printer has an unique Printer ID in the format, USG1-PTRxxx or USG2-PTRxxx where USG1 or USG2 indicates the location of the printer and x denotes a number.

Whenever user adds a printer to the table, the Printer ID will "auto-increase" according to the location..
for eg.

The table currently has Printer ID, USG1-PTR001. The next printer the user adds from the location USG1 will have a Printer ID, USG1-PTR002. However, if the user adds a printer from USG2, the printer will have a Printer ID, USG2-PTR001 instead of USG2-PTR002.
Is there a way to do this without the user having to enter the Printer ID himself?

Roger_Ramjet

This sort of thing can look simple to solve at first, but there are significant problems in a multi-user environment.

If you want no gaps in any number sequence then you cannot use autoincrement as they will inevitably have them.

Looking up the last number and then generating the next one in code will fail when you have concurrent inserts; ie I look up the number just before someone else inserts a new record so my calculated id has been used already by the time my insert runs. To overcome this you have to use transactions and table locks, which means you will need to use an InnoDB table for this.

The simplest way in the end will be to create your own version of the autoinc process.

Create an InnoDB table with 2 columns, this will store the last number used at each location.

table location_index
prefix : to hold each location prefix eg USG1
last number: which will store the last number used for that location

On insert you start a transaction on that table, lock the row for the given location, select the last number and add 1 to calculate the next printer id.
Insert the new printer record with the calculated id into the main printer table.
Select the new record and check it is correct.
Only then do you update the last number column in the index table and release the lock.

Simple to do in mainstream clent/server databases that support stored procedures, but a major problem in the php/mysql world because transactions are very difficult to implement.

void_function

You can lock for read and write even with MyISAM tables.

And keeping own version of autoinc is good advice in this case. Or you can count, it actually doesn't matter as long as you update within locked environment.

Roger_Ramjet

void_function wrote:
You can lock for read and write even with MyISAM tables.

And keeping own version of autoinc is good advice in this case. Or you can count, it actually doesn't matter as long as you update within locked environment.

But myisam tables are NOT transaction-safe, only innodb and bdb tables are transaction-safe

from the manual:

Transaction-safe tables (TSTs) have several advantages over non-transaction-safe tables (NTSTs):

*

  They are safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup plus the transaction log.
*

  You can combine many statements and accept them all at the same time with the COMMIT statement (if autocommit is disabled).
*

  You can execute ROLLBACK to ignore your changes (if autocommit is disabled).
*

  If an update fails, all of your changes are reverted. (With non-transaction-safe tables, all changes that have taken place are permanent.)
*

  Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads.

tnylsej

Thanks all.. =D I shall try the method Roger Ramjet posted

But am I able to export InnoDB table to another SQL server? If yes, how do I do so?

void_function

http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html

and

http://dev.mysql.com/doc/refman/4.1/en/internal-locking.html

As you can see, MyISAM tables can be locked and while locked no other thread can access them.

No need to have InnoDB tables. You don't need transactions, only locking access while you update a counter.

tnylsej

Thanks void_function I shall try to lock a MyISAM table.. as it is easier to port to other SQL server

Roger_Ramjet

No, the table structure and contents are as easy, or difficult, whichever engine you choose.

Now, in an ideal world you would only need table locks. But it is the first law on computing that whatever can go wrong will go wrong. That is why transaction-safe technology was invented. You are going to update 2 tables, and all future processing is dependant on both of those updates succeeding. If you just lock the index table and do the insert, even if you check that the insert was correct, you can still have the update of the index fail. The next insert into the printers table for that location is going to then duplicate an issued ID. 🙁
Except that it will fail because of your unique key on the column.

So, do you start writing your own transaction-safe procedure that will check the printers table against the index table and delete the insert when they are out of step, or are you just going to use what has already been invented for this purpose.

In the end one has to wonder just how much traffic will be involved here: I doubt that it will be enough for the performance advantages of MyISAM to matter.

Besides, being familiar with, and adept at the use of transactions is a valuable skill that will pay great dividends in the years to come.

void_function

I just think that transactions and InnoDB tables are overkill for simple increment management. They are good when you need to transact with sensitive data, and you can rollback or commit, or when you need per-row locking. With MyISAM that's impossible, true.

I also agree with the fact that the use of transactions is valuable skill, but I am also aware that many newbies read these threads and I don't want them to falsely conclude that InnoDB and transactions are the only way to go. Nor to get into a habit of killing a fly with a bazooka.

Second law of computing: KISS - Keep It Simple Stupid.

😃

ahundiak

There is a little trick that I use in low intensity enviroments that seems to work well. Suppose you have a sequence table with name and next columns. So you start by
SELECT next FROM sequences WHERE name='USG1-PTR';
Then turn around and update the table with
UPDATE sequences SET next='$next+1' WHERE name='USG1-PTR' AND next = '$next';

The update will fail if another process has managed to grab and update the next value between the time you selected it and the time you updated it. In which case you just repeat the process. As long as you don't have the proverbial "hundreds of updates per second" problem then you can be certain that your key will be unique without resorting to table locks.

Of course transactions are better but sometimes they are just not available or are overkill.