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?

[`AUTO_INCREMENT`](https://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html) — Martin Thoma, Jun 02 '15 at 10:50
See here: http://stackoverflow.com/questions/10991894/auto-increment-primary-key-in-sql-server-management-studio-2012 — rghome, Jun 02 '15 at 11:30
CourseNo cannot be a primary key. I have a different column for primary key — RohanDsouza, Jun 02 '15 at 11:34
Personally I'd store the info separately. I'd add another column called something like CourseId, make it an IDENTITY(1,1) INT or BIGINT. Then, the Course, Location and CourseId are available separately. If you wanted to build a composite out of them and store it in another column you certainly could. Keeping track of the numbers on the end of a varchar() field is just messy. We personally use the method I describe in most of our datamart tables. — Patrick Tucci, Jun 02 '15 at 18:27

score 0 · Answer 1 · answered Jun 18 '15 at 06:32

0

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

answered Jun 18 '15 at 06:32

RohanDsouza

412
2
16

Prevent concurrent inserts in sql

1 Answers1