Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2015
    Posts
    3

    Unanswered: Incrementing counters

    Hi,

    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:

    Code:
    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.

    Any suggestions would be appreciated.

    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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!
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2015
    Posts
    3
    Quote Originally Posted by gvee View Post
    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?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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.
    George
    Home | Blog

  5. #5
    Join Date
    Aug 2015
    Posts
    3
    Quote Originally Posted by gvee View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •