CODEWITHSUNDEEP
c#sql-servermultithreading
n.Stenvang
n.Stenvang

Reputation: 507

MultiThreading with SQL database

I want to create a user for a program. If the user allready exists i dont want to create the user with that username.

I got 2 threads Thread 1: handles a socket connection Thread 2: handles a SQL connection

Thread 1 sends USER INFO (name, PhoneNumber and so on) to Thread 2. I want Thread 2 to to notify Thread 1 if the USER allready exists.

Adding the USER INFO to the SQL database, is no problem. I just need that notification.

public void saveUser(string fullName, string CPR, string password, string kontakt)
       {

           ADBconn.Open();

           SqlCommand cmd = new SqlCommand("INSERT INTO ADBregister (fuldeNavn, password, borger_cprnr, kontaktPersonNummer)" + "VALUES (@fuldeNavn, @password, @borger_cprnr, @kontaktPersonNummer)", ADBconn);

           cmd.Parameters.AddWithValue("@fuldeNavn", fullName);
           cmd.Parameters.AddWithValue("@password", password);
           cmd.Parameters.AddWithValue("@borger_cprnr", CPR);
           cmd.Parameters.AddWithValue("@kontaktPersonNummer", kontakt);

           ADBconn.Close();
       }

Upvotes: 0

Views: 174

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48806

Do not use dynamic SQL. Instead, call a proc that can do both user existence checking AND error handling. For example:

BEGIN TRY

    IF (EXISTS(
                SELECT  *
                FROM    ADBregister -- see note below about NOLOCK
                WHERE   fuldeNavn = @fuldeNavn
                )
        )
    BEGIN
        ;THROW 50005, 'FullName already taken!', 2
    END

    INSERT INTO INTO ADBregister (fuldeNavn, [password], borger_cprnr, kontaktPersonNummer)
    VALUES (@fuldeNavn, @password, @borger_cprnr, @kontaktPersonNummer)

END TRY
BEGIN CATCH

    -- possible additional error handling logic
    ;THROW;
    RETURN

END CATCH

In the C# code, put the Execute in a try / catch(SqlException) where the catch block will look for both the custom error you did in the THROW as well as a more generic UNIQUE CONSTRAINT Violation error that will result in cases where this thread successfully passes the IF EXISTS at the same time another thread is committing the FullName that is being requested here. But the IF EXISTS should catch most cases.

NOTE about NOLOCK:
It is possible to have the IF EXISTS catch even more cases that are happening at the same millisecond in a highly transactional system by adding "WITH (NOLOCK)" to the FROM clause, but there are two issues with this:

  1. This will catch some entries being committed yes, but it will also produce false positives by catching entries that were attempting to commit but get rolled back for some reason (i.e. Dirty Reads). In that case the FullName would technically be available as the other thread did not commit.
  2. It won't catch all instances. There is no way to catch all instances so even if you increase the chances of catching in-use entries via NOLOCK, you still need to trap the UNIQUE CONSTRAINT violation that will occasionally happen, so you didn't save any coding / logic.

NOTE about MultiThreading:
This issue is not specific to multithreading if you have a system that can EVER have more than one process at the same time connecting to the Database. If you have a desktop app that uses a local DB (hence truly single-user), then you don't need this approach. But if it is a shared database and some other person can try to insert a new user at the same time, then even if your code is using a single thread it will still need this approach. Even if the environment is not highly transactional, it can still be that two people try to do the same thing at the exact same time, even if it is the only two actions taken by the program on a given day.

Upvotes: 1

Stinky Towel
Stinky Towel

Reputation: 778

Don't use a try/catch here for determination (business logic). Instead create a separate method that accepts the user info and queries the database to see if the user exists then return true or false. From there you can perform the insert.

Because it appears Thread 1 is handling a single user's info, don't spin off another thread just to do the SQL verify/insert routine. It's not necessary.

Upvotes: 0

Related Questions