You must Sign In to post a response.
  • Category: SQL Server

    How to avoid duplicate insert during concurrent users in sql

    Hi,

    I have a web application developed in asp.net & SQL. During insert by concurrent users duplicate refno records are getting generated. I tried getting the Max(ID) also checked the exists, but when the procedure is executed concurrently the refnos are getting generated with the same no. but different id.

    Please advice whether I have to use any lock while inserting the records in sql.

    with regards,
    shyam
  • #763903
    Hi,

    Use lock it will help you for locking table until you release the table.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #763905
    Hai Shyam,
    There could be many ways through which you can restrict the insertion of the duplicate records:
    1. You can use the select statement before insert to check for the inserting record and if so then increment the id value and then insert. In this case, during the insertion time, it will check and if there is any record, it will create the new id.

    https://bytes.com/topic/sql-server/answers/650958-duplicate-key-insertion-error-when-concurrent-users-insert-same-data-into-table

    2. You can use Time-stamp and based on the time-stamp the data will get inserted.
    3. Using of Concurrency(use of Locking, Blocking and Row Versioning) and Transactions

    https://www.simple-talk.com/sql/t-sql-programming/row-versioning-concurrency-in-sql-server/

    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #763915
    1. you can use Transaction Isolation Levels

    READ UNCOMMITTED
    READ COMMITTED (Default)
    REPEATABLE READ
    SERIALIZABLE
    SNAPSHOT

    2. you can use Statement Isolation Level

    READ COMMITTED SNAPSHOT

    According to your requirement you can handle any one of the above. That will handle your concurrency.

    Following is the sample.

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    Go

    BEGIN TRANSACTION
    <Write your query>

    By Nathan
    Direction is important than speed

  • #763917
    Hi,

    Thanks for your response. I will check with the above given methods and will confirm.

    Thanks and Regards,
    Shyam

  • #763927
    To get unique ID do not get depend upon MAX function, instead you need to keep one Identity column in your table and when you insert any record you need to fetch AutoIncremented at same time, it will avoid duplicate insertion
    see below insert syntax to fetch auto-incremented id
    INSERT INTO TABLE ( NAME) VALUES ('test')
    SELECT @@IDENTITY

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #764012
    I tried the above concepts, but the problem is not solved. When I tried to check in c# coding in debug mode, the cursor is going to each line twice and I am not able to get the max ID after insert and the same max no gets assigned to both the records. Since i have given primary key duplicate records will not be saved but the max no with other branch details get saved. eg. BR1/ENQ/12345/2015-16, BR2/ENG/12345/2015-16

    regards,
    shyam


Sign In to post your comments