Multiple threads accessing SQL database

Question

I have a server and 'x' number of clients.

When the server is listening for an inbound connection, it will create a client handler instance (a class that manages the client communication) which will be spun off in a separate thread.

Depending on the command the client sends to the server, the server may need to access a SQL database to store information about that client.

The client handler instance will 'handle' this request. The only problem is, if multiple client handlers are wanting to access the SQL database to do the exact same thing then there is potential for read / write issues.

I was thinking about exposing a static method on the server, calling it from the client handle instances, then locking the function which accesses the SQL database (either read or write).

Is this a good approach or are there better approaches?

Thanks.

SQL handles it and will keep record intact with whatever data whichever thread updates last - you won't have corruption here. However, if you're talking about having specific rules as to which data should survive in case multiple updates of various versions of data, then we'd need to understand a bit more about your data, and what constitutes survival. — LB2
– LB2, Commented Mar 19, 2014 at 19:11

mitchellt · Accepted Answer · 2014-03-19 19:14:10Z

5

Well, you DO know that SQL has locks and a ton of internal mechanisms to serialize access? That this is part of the ACID conditions ingrained in SQL since the 1950's when SQL was created? That the locking mechanism in SQL is very very fine and basically you try to solve a problem that has been solved more than 60 years ago.... because it seems you need to read a book about the basics of SQL.

Under normal circumstances (standard connection string) resource access is serialized in SQL Server (TransactionIsolationLevel serialized), but that can be tuned. I really suggest learning some SQL fundamentals.

edited Mar 19, 2014 at 19:14

mitchellt

1,0524 gold badges15 silver badges26 bronze badges

answered Mar 19, 2014 at 19:06

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiple threads accessing SQL database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related