CODEWITHSUNDEEP
sql
user3057678
user3057678

Reputation: 305

SQL lock row,table on insert and select

I am trying to achieve some sort of lock on sql. To explain what am i doing simple: One table with Id int autoincrement as PK, and one field Data varchar(max) non-clustered IX

Now i have some C# code that simlpy checks if the item isn't in the db, makes an insert The sql code that i am using behind is like:

INSERT INTO {0}.{1} WITH (TABLOCKX) VALUES(@data...)

and the select one is:

SELECT Id FROM {0}.{1} WITH (TABLOCKX) WHERE(Data = @data)

But i can see that there are items with the same value inserted multiple times TABLOCK creates deadlocks, and i dont want to use unique index because its very slow.

Is there a way to achieve this with locking?

Upvotes: 0

Views: 68

Answers (1)

Shin YoungChul
Shin YoungChul

Reputation: 103

I'm not sure it is what you want, I hope that this reply is helpful.

private void test(string aConnectionString, string aData)
    {
        using (SqlConnection sqlConnection = new SqlConnection(aConnectionString))
        {
            sqlConnection.Open();

            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            SqlTransaction sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);

            sqlCommand.Connection = sqlConnection;
            sqlCommand.Transaction = sqlTransaction;

            try
            {
                sqlCommand.CommandText = @"IF NOT EXISTS(SELECT Id FROM {0}.{1} WHERE Data = @Data)
                                           BEGIN
                                                INSERT INTO {0}.{1}
                                                SELECT @Data
                                           END";

                sqlCommand.Parameters.Add("@Data", System.Data.SqlDbType.VarChar).Value = aData;
                sqlTransaction.Commit();
            }
            catch (Exception ex)
            {
                sqlTransaction.Rollback();
            }
        }
    }

Upvotes: 1

Related Questions