Prevent concurrent inserts in sql

Question

There is a table tblStudents which contain information about student_courses. Every new student is assigned a CourseNo (varchar)for a course depending on course type and location type. eg:

 CourseNo              Course       Location    StudentID   Date
NET_MUMBAI_001      Net     Mumbai       100        2/6/2015
NET_MUMBAI_002      Net     Mumbai       101        2/6/2015
NET_DELHI_001       Net     Delhi        100        2/6/2015
JAVA_DELHI_001      Java        Delhi        101        2/6/2015
JAVA_DELHI_002      Java        Delhi        102        2/6/2015
JAVA_DELHI_003      Java        Delhi        122        2/6/2015
JAVA_DELHI_004      Java        Delhi        112        2/6/2015

When a new student is being enrolled, it checks for the last highest used number (002 in this case for Mumbai and Net) for that combination of course and location and generates a new course no. (NET_MUMBAI_003). When there are concurrent inserts, the number being generated is duplicated. How can the same be prevented?

Answer 1

Thanks for the inputs.

Used the following 2 things in sync for achieving the same. 1. Applied unique index 2. Used isolazation level as serializable with update lock