I am porting an existing system to PHZP/MySQL. They use automatic counters to assign a sequential code to things. The automatic counter could be for customers, properties that belong to a customer, contacts of a customer, etc. Since we are dealing with counters that are not table dependent, an autoincrement doesn't work.
I have a table of counters that looks like this:
CREATE TABLE `counters` (
`id` varchar(36) NOT NULL,
`idcompanies` varchar(36) NOT NULL,
`cvariable` varchar(40) NOT NULL,
`ivalue` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_counters_compunique` (`idcompanies`,`cvariable`),
KEY `idx_counters_cvariable` (`cvariable`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
The basic idea is that when I need a variable (e.g., next "Customer" code for a particular company) I would increment the value and then return the incremented value.
Here's my question. I want to ensure that:
1) The same code is not given out twice, and
2) I don't create a deadlock situation.
I was thinking of a trigger on the customer table on insert that would automatically get the next customer ID but if two rows are inserted at the same time (unlikely, I know, but still possible), I could have a problem here.
I am using innodb so I could theoretically do a transaction but am not sure how I would write something like this. Obviously, some kind of canned routine would be best.
Not sure why you can't use an autoincrement?
What you're attempting to achieve sounds like you'd be setting up a ticking time bomb!
I hear the ticking time bomb issue. Here's why I think autoincrement won't work.
I have a table of customers. Within the table, I have customers belonging to three different companies. Each company has its own sequence which is maintained separarely. An autoincrement, increments for the entire table, no?
If you have to have continuous values [within each subset], then your design is wrong.
An "ID" should have no meaning - it is a surrogate key and the value is arbitrary.
This is not my design. This is existing data and the client refers to it this way so we need. Having said that, in my redesign, each row HAS a unique, system generated ID which is what I use internally. I still need to generate these IDs for the client.