Answered Thread Lock - Db timeout

etl2016

Active member
Joined
Jun 29, 2016
Messages
31
Programming Experience
3-5
hi,

I have spun nearly forty threads in my application, all threads accessing the DB through Thread Lock feature. Each thread is processing an estimated hundred thousand rows. I am getting DB timeout errors. I have used connection pool mechanism. I want to take a closer look and identify one of the three possible reasons.

One, the DB is not efficient enough to handle calls within a given thread (because, thread lock is already in use). If so, its connection features (or similar) need an upgrade. The connection is getting timed out in 5000ms.

Two, the thread lock is not implemented correctly.

Three, connectionpool is not implemented correctly. How do I programmatically diagnose the actual reason to pin-point the root cause?


thank you
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,575
Location
Virginia Beach, VA
Programming Experience
10+
Without seeing your code, it is really kind of hard to comment about how your databases are interacting with thread locks.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,575
Location
Virginia Beach, VA
Programming Experience
10+
Please tell me you didn't write code as:
Code:
using (var connection = new SqlConnection( ... ))
{
    connection.Open();
    lock (lockObject)
    {
        // do database operation here...
    }
}
With such a code structure above, you are holding a connection open while waiting for the thread lock to clear.

I am wondering why you would even need a thread lock. (Well implemented) databases display ACID behavior. Is there some kind of memory data structure that you are trying to keep consistent with the database and so you need the thread locking to make your memory representation also have ACID behavior?
 

etl2016

Active member
Joined
Jun 29, 2016
Messages
31
Programming Experience
3-5
thank you Skydiver

The timeout is related to this : Are you getting network or CPU bound?


The core issue looks like is around ThreadPooling / IOCP / qs

The lock I have implemented is as follows, which is not able to handle thread-theft and connection timeout scenarios: I have spun around hundred threads

C#:
static readonly object UpdateLock = new object ();
public static void UpdateDBUtility (some parameters)
{
    lock (UpdateLock)
   {
        update  the database;
   }
}
 
Last edited by a moderator:

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,575
Location
Virginia Beach, VA
Programming Experience
10+
The link you have above is with regards to Redis. You opened this question saying that you are using SQL. Are you using Redis as a front end cache for SQL?

You snippet of code above still doesn't show at which point in time you grab a connection. Do you open and use the connection within the lock? Or do you do it the wrong way like I was worried about in my first reply?
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,575
Location
Virginia Beach, VA
Programming Experience
10+
I have spun around hundred threads
And do you have the number of cores to support this? A good rule of thumb is about 2-4 threads per core.
 

etl2016

Active member
Joined
Jun 29, 2016
Messages
31
Programming Experience
3-5
"...Are you using Redis as a front end cache for SQL?... " No. It is just used to do lookup to transform a particular column in the input. "... Do you open and use the connection within the lock ...." Connection is being opened only once, prior to Threading, and is being re-used in all child threads. Redis offers a mechanism called Multiplexing. It is implemented as in : Basic Usage

"..... A good rule of thumb is about 2-4 threads per core ....." thank you. I have a setup with four cores. So, will reduce my threads from hundred to ten'sh.

thanks
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,575
Location
Virginia Beach, VA
Programming Experience
10+
Yes, that maybe true for Redis, but for the SQL connections, you should not open once and share among all the threads. For SQL connections, you need to instantiate and use it within the same thread.
 

Sheepings

Senior Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,378
Location
UK
Programming Experience
10+
A good rule of thumb is about 2-4 threads per core.
Unless you are going to try and count how many cores there are, I'd advise not exceeding what can't be accounted for.
 
Top Bottom