-1

I have a live web application(.NET), in which I have a facility for making transactions. When more no. of users make any transactions at the same time, duplicate/same transaction ID gets generated for all the users who're all making transactions. Is there any way to avoid creating same ID ? I tried the following solutions but nothing helps.

1.Mutex
2.Table lock(SQL)
3.Generating Transaction ID at the time of inserting into the table.
Vishwanath
  • 9
  • 1
  • 4
    How can we answer without seeing what you are doing to generate the transaction id? Right now, the answer to your question is: yes, there is a way to avoid creating same ID. – Crowcoder Jan 01 '18 at 12:54
  • What database are you using? You need to use a database that is designed for multi-users like SQL Server. To prevent duplicate IDs in SQL server you need a stored procedure that assigns unique ID. If you have a Group of servers then each server must assign ID so there are not duplicates. – jdweng Jan 01 '18 at 12:57
  • Yes I am using SQL only. But I want to generate the ID manually(MAX + 1) not auto-generated, because I am appending some characters to it. – Vishwanath Jan 01 '18 at 13:09
  • 1
    That's the worst way you could do it. "SQL" doesn't mean Microsoft SQL Server, but I'm going to assume that's what you mean. You probably want to create and use a Sequence. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql – Crowcoder Jan 01 '18 at 13:15

2 Answers2

0

Use a database to generate the ID. Some possible examples:

SQL Server has an auto increment feature. Oracle has a sequence feature

This will ensure your ID to be unique.

You mention SQL, so you have a database accessible.

Ctznkane525
  • 7,297
  • 3
  • 16
  • 40
0

Option 1

SQL Server already has an auto-increment feature that is both guaranteed to generate a unique ID and is efficient.

If you want to "add letters to that ID", the simplest solution is to add a separate varchar field that contains those letters, and then format them for display as a single number in your application (either by using a SQL query to do so or string.Format.

ID   | IDText                                Application Display
------------------                     -----------------------
1    | MyLabel                               1-MyLabel
2    | MyLabel                               2-MyLabel
3    | FooBar                                3-FooBar
4    | SomeText                              4-SomeText

So, in the above, ID would be an auto-increment int field and IDText would be a varchar (or char if you want a fixed length).

As you can see, even if the same string such as "MyLabel" were added at the same time, you would get a different ID to append to it so you in effect have a unique ID.

Option 2

Use a stored procedure to contain the following logic in a single transaction with BEGIN TRANSACTION/END TRANSACTION:

  1. Query for the highest ID
  2. Add 1 to that ID
  3. Create a new record with the new ID
  4. Return that ID

The transaction will guarantee that the number cannot be duplicated. This is less efficient for creating IDs, but more efficient at looking them up than the 2-column approach.

NightOwl888
  • 55,572
  • 24
  • 139
  • 212
  • 1
    how will transaction "guarantee that the number cannot be duplicated"? – Igor Jan 01 '18 at 15:48
  • This seems to repeat a common misconception regarding transactions vs locking. See for instance [https://stackoverflow.com/questions/21468742/is-a-single-sql-server-statement-atomic-and-consistent](https://stackoverflow.com/questions/21468742/is-a-single-sql-server-statement-atomic-and-consistent) – peterG Jan 01 '18 at 21:05