0

table :employee

Fields: eid(primary key ), empno(unique key), empname

here the empNo is manually generated in the code for Ex. Accounts001.. when 2 users enter employee data with 0 time difference. During 'Add' the emp num generated for both the inserts will be same i.e., Accounts001 and Accounts001, whereas, it should be unique i.e., Accounts001 and Accounts002..

When the add is done.. the unique key constraint acts just fine.. even though the manually generated empno is same. the db is updated with unique numbers..

I would like to know how exactly does mysql handle this condition as the empno is generated by us manually.. does it come back to the code n regenrate or queue up the request?

I hope the question is clear..

Thanks in advance

Stephan
  • 41,764
  • 65
  • 238
  • 329
Archana
  • 53
  • 1
  • 9
  • Mysql doesn't bother about it if you don't give `unique` attribute to it.. It gives different `eid` for each of them.. – Bhuvan Rikka Sep 26 '12 at 12:32
  • After the insert is the data in the table two rows of Accounts001 or is it one of Accounts001 and Accounts002? – John D Sep 26 '12 at 12:34
  • @Archana How did you check to insert two records at a time with same details?. – Ravichandran Jothi Sep 26 '12 at 12:35
  • yes eid is unique but that is auto increment and mysql does that.. but empId is generated in the code.. and it sends the same empno to both the rows.. but as i have given unique constraint somehow it adds the next number.. i wanted to know how mysql manages that? does it come back to the code? – Archana Sep 26 '12 at 12:38
  • This has nothing to do with MySQL. Your question is unclear, you wonder how MySQL calculates the next number for the primary key? – N.B. Sep 26 '12 at 12:39
  • the tool is hosted on cloud in the company server.. the users enter data at the same time.. both the users get the same empno – Archana Sep 26 '12 at 12:41
  • And **how** do you generate `empno`? That's the question. – N.B. Sep 26 '12 at 12:44
  • @N.B not the primacy key.. the unique key? Does it hit a duplicate error? – Archana Sep 26 '12 at 12:44
  • the empno is generated in the code.. for ex.. if the last empno in the db is 001.. then if 2 users login at the same time both will get 002.. but once the data is entered the empno will be entered as 002 003.. do you think the milisecond delay also counts.. am i ignoring that? – Archana Sep 26 '12 at 12:46
  • @Archana there seems to be a misunderstanding here. I'll try to clear it up. Primary key is incremented by using a counter that MySQL stores for each table. That counter is incremented every time something happens with the INSERT command (it can get used for the PK, it can get discarded). The other `unique` constraint that you calculate depends on something you receive back from the db. I assume you are querying the table for `MAX(column_name)`, you retrieve it and you increment it by 1 - and here lies the problem. – N.B. Sep 26 '12 at 12:46
  • Since at the time when 2 users queried the db, both got the same number for last `empno`. The application isn't aware of 2 connections, so your app increments the same number by 1. That's why you get the same number. Yes, there is a delay between communicating with MySQL, there always will be and the method you are using to generate that `empno` is not working for you. You must change your approach. – N.B. Sep 26 '12 at 12:48
  • Yes i am clear about he primary key.. There was come confusion about unique key.. I was ignoring the mili second delay.. I think it is entering data only of one user.. and the other user gets duplicate error.. Thanks for the quick reply – Archana Sep 26 '12 at 12:49
  • ok yes that is what is happening.. Both are recognised as same user and same empno is sent to them.. Thanks for clearing my doubt – Archana Sep 26 '12 at 12:53

0 Answers0