The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions
this is mention on
http://dev.mysql.com/doc/mysql/en/information-functions.html
i am using this in java code
the code i am providing is
public static String add(Affiliate affiliatedata) throws Exception
{
Connection con =null;PreparedStatement affiliateinfo =null;PreparedStatement affiliateId =null;
String msg = "";
try
{
// ConnectionManager cm = ConnectionManager.getInstance();
con = DBConnectionManager.getConnection();
affiliateinfo = con.prepareStatement("INSERT INTO affiliate (Name, SubAffiliate, AffiliateMemberId, MemberIP, LogDate) VALUES (?, ?, ?, ?,Now())");
//System.out.println("INSERT INTO affiliate (Name, SubAffiliate, AffiliateMemberId, MemberIP, LogDate) VALUES ('"+affiliatedata.getName()+"', '"+affiliatedata.getSubAffiliate()+"', '"+affiliatedata.getAffiliateMemberId()+"', '"+affiliatedata.getMemberIP()+"',Now())");
affiliateinfo.setInt(1, affiliatedata.getName());
affiliateinfo.setInt(2, affiliatedata.getSubAffiliate());
affiliateinfo.setString(3, affiliatedata.getAffiliateMemberId());
affiliateinfo.setString(4, affiliatedata.getMemberIP());
affiliateinfo.executeUpdate();
msg = "1";
affiliateId = con.prepareStatement("select LAST_INSERT_ID()");//"select max(affiliateId) from `affiliate` where name = ? and AffiliateMemberID = ?");
//affiliateId.setInt(1, affiliatedata.getName());
//affiliateId.setString(2, affiliatedata.getAffiliateMemberId());
ResultSet rs = affiliateId.executeQuery();
while (rs.next())
{
affiliatedata.setAffiliateId(rs.getInt(1));
}
System.out.println("max affiliateid-> " +affiliatedata.getAffiliateId());
con.close();
}
catch (Exception ex)
{
System.out.println("Error in Affiliate addition: " + ex);
msg = "2";
}
finally // don't forget to do this in any case
{
if (affiliateinfo != null) affiliateinfo.close();
if (affiliateId != null) affiliateId.close();
if (con != null) con.close();
}
return msg;
}
so is it sure that multiple clients executing add() method will get their respective last_insert_id()