1

Let suppose I have a query that runs multiple times because it is in a while loop which inserts data into a table.

INSERT INTO <table>( psuiKey, group_id) VALUES ('54ds8a784daEd','1');

The problem is when two users from different computers add data at same time. So what is happening is user 1 adds data to the table to looks like this:

psuiKey       group_id 
54ds8a784daEd  1
54ds8a785daEd  1

and at the same time user 2 adds data where group_id is 2, but the data looks like this:

psuiKey       group_id 
54ds8a784daEd  1
54ds8a785daEd  1
54ds8a786daEd  1
54ds8a787daEd  1

instead of:

psuiKey       group_id 
54ds8a784daEd  1
54ds8a785daEd  1
54ds8a786daEd  2
54ds8a787daEd  2

So in short if two users add the same data at same time so group_id is not changing when I find max group_id using:

SELECT MAX(group_id) + 1

So SQL can't able to update it when two users add data at the same time.

Note I cant use group_id as primary key and I also use datetime.

So any help would be helpful!

  • 2
    This is exactly the reason why you never use SELECT MAX(groupid) + 1 to determine the next identifier, if you have more than one connection to the database at a time. – mustaccio Jan 23 '18 at 13:03
  • So what could i have to try. did sql provide any functionality to resolve this. or have any solution – Web Infos Jan 23 '18 at 13:05
  • Your basic problem is that your design cares about an internal column. If you are using group Id outside this table, then you are facing the classic error of using natural keys in your schema design. – clifton_h Jan 23 '18 at 13:41
  • any important link would be useful @Paparazzi – Web Infos Jan 23 '18 at 13:50
0

There are many alternatives you could attempt, such as enforcing an exclusive table lock on the SELECT query (thus delaying one entry until after the other).

My personal favorite would be to enforce staging - production tables just like many of the testing apps I manage.

This way, it doesn’t matter the timing or order. Remember the group_ID column is a database column, so it’s best not to get lost in the details over a value whose only purpose is to enforce differentiating groups

0

Given PRIMARY KEY (psuiKey) (or UNIQUE), this will do part of what you want:

INSERT INTO <table>
        ( psuiKey, group_id )
        VALUES ('54ds8a784daEd','1')
    ON DUPLICATE KEY UPDATE
        group_id = group_id + 1

But you example show two rows with idental psuiKey values. This does not make a lot of sense, and you have provided no way to distinguish any of the 4 rows apart. Have you held back some details? You mentioned a datetime, but have not explained its relevance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.