concurrency issue when two users inserting some values.

Question

When two users trying to inser for same record at same time then two rows are generating instead of updating other one.

But same scenario is working fine in case of update.but we can not use locks.

If there is any other any way then please advise in vb.net (sql) 

Answer 1

I must admit that I don't quite understand your code. However, it is not quite clear which column of which table exactly here has unique index / primary key declared.

 

Also not clear how exactly, are you reproducing scenario of two inserts at identical time.

 

Also, not clear how a duplicate row can possibly get inserted when unique key is defined.

 

Answer 2

Hi Nitin,

yes, we have created unique index for the below sample table:

 

 

DECLARE @falics TABLE (Id int, faliId int, TimeId int)

INSERT INTO Table1

( ForecastLineItemId

, TimeId

, CurrencyAmt

, CreateUserId

, CreateDttm

, UpdateUserId

, UpdateDttm)

OUTPUT INSERTED.ForecastLineItemCellId, INSERTED.ForecastLineItemId, INSERTED.TimeId INTO @falics

SELECT

ForecastLineItemId

, TimeId

, CurrencyAmt

, CreateUserId

, CreateDttm

, UpdateUserId

, UpdateDttm

FROM @adjustmentCellsTable

ORDER BY ForecastLineItemId, TimeId

SELECT Id FROM @falics ORDER BY faliId, TimeId

and we have the below sample insert query: 

sb.AppendLine("INSERT INTO Table1 ")

sb.AppendLine(" (ForecastVersionId ")

sb.AppendLine(" ,[CostCollectorId] ")

sb.AppendLine(" ,AdjustmentDesc ")

sb.AppendLine(" ,AdjustmentTypeCd ")

sb.AppendLine(" ,ElementCategoryId ")

sb.AppendLine(" ,CurrencyCd ")

sb.AppendLine(" ,AdjustmentAmt ")

sb.AppendLine(" ,SpeculativeInd ")

sb.AppendLine(" ,CreateUserId ")

sb.AppendLine(" ,CreateDttm ")

sb.AppendLine(" ,UpdateUserId ")

sb.AppendLine(" ,UpdateDttm ")

sb.AppendLine(" ,LastUpdateDttm) ")

sb.AppendLine(" VALUES ")

sb.AppendLine(" (@ForecastVersionId ")

sb.AppendLine(" ,@CostCollectorId ")

sb.AppendLine(" ,@AdjustmentDesc ")

sb.AppendLine(" ,@AdjustmentTypeCd ")

sb.AppendLine(" ,@ElementCategoryId ")

sb.AppendLine(" ,@CurrencyCd ")

sb.AppendLine(" ,@AdjustmentAmt ")

sb.AppendLine(" ,0 ")

sb.AppendLine(" ,@CreateUserId ")

sb.AppendLine(" ,@CreateDttm ")

sb.AppendLine(" ,@UpdateUserId ")

sb.AppendLine(" ,@UpdateDttm ")

sb.AppendLine(" ,@LastUpdateDttm ) ")

sb.AppendLine("SET @ForecastLineItemId = SCOPE_IDENTITY() ")

 

 

Answer 3

Please elaborate a bit. Share some code may be from back-end. 

 

How exactly are managing to test this scenario.

 

Which column you want to be unique? Have you already created primary key / unique index  on that column?