| Author |
Topic |
|
dnagahawatte
Starting Member
24 Posts |
 Posted - 2010-04-03 : 08:17:33
|
| HiI have a calls table where i have to insert records to it from a webservice. Call_ref is the primary key. To find the next Call_ref. i do Select max(Call_Ref)+1 from calls. Now i need to lock/reserve this Call_ref untill i insert date. This is a bulk insert as well.Otherwise another user might insert a call with that number directly using the system.Can you please HELP? Thanks you |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-03 : 08:36:56
|
| I think your best bet would be to create a table to store the "Next available number", and then update that when you want a new number.Any other user / process can then also get a "next number" and will not block other users / processes getting numbers.The only downside is that if you do no use your number (application crashes / user changes their mind) then there will be a gap in the numbers. However, this is "normal" in systems that use IDENTITY columns too, so best to just accommodate it if there is any issue with gaps in your organisation.The other benefit is that instead of getting the "next available number" (i.e. just add one to the current value) you can also get a range of next-numbers (add N to the current value), and then you can use that range of numbers for a batch-insert. |
 |
|
|
dnagahawatte
Starting Member
24 Posts |
Posted - 2010-04-03 : 10:37:08
|
| What if i get the next available number with in the same stored procedure that i use to insert data.?Can another process triger (take that number) while my stored procedure running? Can insert data to a table happen concurrently??Please advice.. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-03 : 13:06:34
|
| I agree Tara, but if I understood the O/P correctly he wants to get the number "up front" of the insert (I expect that could be worked around, but sometimes its nice to give the number on a data entry screen for the operator to use on paper-based system, or where importing a batch of records (harder to deduce the IDENTITY's that have been allocated to a whole batch) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dnagahawatte
Starting Member
24 Posts |
Posted - 2010-04-03 : 13:42:03
|
| Cant answer that question, cos ,System is already in place.(developped ages ago) Im trying to get calls from another database using webservices and insert in to this database.Will my Stored procedure option work?? |
|
|
|
dnagahawatte
Starting Member
24 Posts |
Posted - 2010-04-03 : 15:40:32
|
| HOW abt TABLOCKX ?? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-04 : 05:19:05
|
| I wouldn't use a database lock that was depending on some client-side application, or worse - a "person"!! - making a decision / further processing. |
|
|
|
|
Lock and insert