0

I am developing C# application which is executing ~30 sql queries every second... I have multiple threads which do that. My problem is that when I try to query the sql server with SELECT .... it returns different results.

Example: If run both SELECT * FROM Users and SELECT * FROM Jobs at the same time from different threads i get responses like:

SELECT * FROM Users -> Job colums with Job values

SELECT * FROM Jobs -> User colums with User values

I am using Dapper like this:

using (var connection = new SqlConnection(_msSqlProvider.ConnectionString))
            {
                connection.Open();
                return connection.Query<User>(
                    @"SELECT JobId 
                        FROM Users
                        WHERE Id = @userId
                        ORDER BY Id ASC",
                    new {userId});
            }

My application logs look like (i.e. the query is returning not User model data):

An exception occured while getting user's job: A parameterless default constructor or one matching signature (System.Int32 Id, System.Int32 UserId, System.Decimal Salary, System.DateTime UpdatedAt) is required for SampleApp.User materialization

My application is very different from this and complex but this should be good as example...

Solution: If anyone reading this question is interested in what was the solution - enabling MARS and connection pooling resolved my issue...

Viktor Kynchev
  • 343
  • 2
  • 10
  • 1
    Maybe have a look at this: https://stackoverflow.com/questions/21917836/connection-pool-one-process-many-threads – Evan Trimboli Sep 24 '17 at 21:06
  • The exception in your application log isn't about wrong result sets from queries - it's about being able to instantiate the `new` anonymous type at runtime. – Chris Walsh Sep 24 '17 at 21:21
  • Instead of using JobId in your select query, check with select * as you are trying to materialize with your User type though you are selecting JobId – Akash KC Sep 24 '17 at 21:30
  • Sounds like you are not thread safe. – Namphibian Sep 24 '17 at 22:28
  • @EvanTrimboli - I am using only 2-3 connections at the same time – Viktor Kynchev Sep 25 '17 at 07:51
  • @ChrisWalsh - This was just an example with Users and Jobs tables, my application is much more complex... The models are perfectly fine - if I run the queries only from one thread it works just fine. The problem is that from this query, mssql (or the connection) returns the row from Jobs table instead of only JobId from Users table... – Viktor Kynchev Sep 25 '17 at 07:54
  • @Namphibian yeah, it looks so... but how I can be thread safe? I need to execute these queries at the same time(the application logic won't let me execute them in queue) – Viktor Kynchev Sep 25 '17 at 07:56
  • For thread safety globals needs to go out and you can use mutexes, semaphores, critical locks and so on to lock shared globals. Also you would need to make sure that the classes themselves are thread safe. Its a large topic. – Namphibian Sep 25 '17 at 21:35

1 Answers1

0

Must use different sql reader or sql command for right solution. Try this;

        double vId;
        SqlDataReader dr = null;
        if (db.OpenDR(ref dr, string.Format("select JobId from dbo.Users where Id={0}", vUSERID)))
        {
            if (dr.Read()) vId = dr["JobId"].dToDouble();
            dr.Close();
        }
        return vId;
Jack
  • 26
  • 3