I planned to use two similar MySQL databases which are distributed geographically. I have a script which autogenerates primary key for a table using following pseudocode

var x = select max(id) from tablename; return (x + 1);

At some scenario, the common scripts can be run concurrently from both location.

say, the max(id) is 5000

test run from location 1 using DB1 get 5001 test run from location 2 using DB2(similar copy of DB1) may also get the

same id "5001", which will create problem.

I can avoid this by var x = select max(id) from tablename group by location having location='?'; return (x+1)

in this case, I am using (id,location) as the primary key. But, this doesn't seems to be a good design.

I am still in analysis & design phase. Yet to implement the design. I am bit new to database.

How can I sync two database such that there are no any conflicts? Any utilities available for this? Is there any other approach to auto-generate primary key?