Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Update generates wrong SQL when running parallel #298

Open
MitarNikolic opened this issue May 17, 2022 · 8 comments
Open

Update generates wrong SQL when running parallel #298

MitarNikolic opened this issue May 17, 2022 · 8 comments
Assignees
Labels
under investigation Issue is being analyzed

Comments

@MitarNikolic
Copy link

Hello everyone,

I created a XUnit test which uses DapperExtensions to update two different tables in the database.
This works perfectly fine as long as you execute the two updates one after another.
As soon as you try running them parallel, tho it generates the following exception:

System.Data.SqlClient.SqlException
Must declare the scalar variable "@u_2".

Looking at the SQL in the server profiler of "Microsoft SQL-Server management studio" you can see that wrong SQL gets generated.

Kind regards
Mitar

@valfrid-ly valfrid-ly added the under investigation Issue is being analyzed label May 25, 2022
@valfrid-ly valfrid-ly self-assigned this May 25, 2022
@urodriguez
Copy link

urodriguez commented Jun 3, 2022

Any Updates for this?

I have the same issue,

After run this code:

await DbConnection.UpdateAsync(aggregateRoot, DbTransaction);

I got this is the exception:

'DbConnection.Update(aggregateRoot, DbTransaction)' threw an exception of type 'System.Data.SqlClient.SqlException' Class: 15 ClientConnectionId: {2e64d6d8-18eb-4c31-be0f-8d788125156b} Data: {System.Collections.ListDictionaryInternal} ErrorCode: -2146232060 Errors: {System.Data.SqlClient.SqlErrorCollection} HResult: -2146232060 HelpLink: null InnerException: null LineNumber: 1 Message: "Must declare the scalar variable \"@u_13\"." Number: 137 Procedure: "" Server: "REMOVED_FOR_SECURITY" Source: "Core .Net SqlClient Data Provider" StackTrace: "...."

Same as MitarNikolic, when I look at the SQL in the server profiler of "Microsoft SQL-Server management studio" you can see that wrong SQL gets generated.

FYI:
This only happens after migrate to:
"DapperExtensions" Version="1.7.0"
The exception is fixed for the prev version:
"DapperExtensions" Version="1.6.3"

But unfourtunally you get the warning:
Severity Code Description Project File Line Suppression State
Warning NU1701 Package 'DapperExtensions 1.6.3' was restored using '.NETFramework,Version=v4.6.1, .NETFramework,Version=v4.6.2, .NETFramework,Version=v4.7, .NETFramework,Version=v4.7.1, .NETFramework,Version=v4.7.2, .NETFramework,Version=v4.8' instead of the project target framework '.NETCoreApp,Version=v3.1'. This package may not be fully compatible with your project.

@valfrid-ly
Copy link
Collaborator

Hi @urodriguez , just to help me, did you try to use the Sync update?? I'm asking because the SQL Generation is the same for both of them and if one works and the other don't I'll have a more restricted area to look for.

Also it would help to send me the code example with the mappings

@urodriguez
Copy link

I didn't.
because using the sync update version is not performant for me.
thats why tried to use the async version on 1.7 but it didnt work.
however it worked for 1.6.3

@valfrid-ly
Copy link
Collaborator

@urodriguez I asked about the test not for performance but for helping me to find the issue. If the same happens in the sync version it's 1 thing... if only in asysnc its something else

@valfrid-ly
Copy link
Collaborator

Investigating this issue I could notice that version 1.6.3, even having a Async method always used the SYNC implementor.

I'm still looking to fix it but I really need information to find the issue.

@skovsende
Copy link

skovsende commented Apr 13, 2023

This is the same as #306, and I have it as well. Below is a short program that causes the bug - at least on my machine in LinqPad, which throws with the message "Must declare the scalar variable "@u_3."

void Main()
{
        // Remember to run the sql at the bottom to create the tables
	var connectionString = "***INSERT CONNECTIONSTRING HERE***";
	var iterationCount = 50;

	Init();

	var task1 = Task.Run(() =>
	{
		var conn = new SqlConnection(connectionString);
		conn.Open();
		for (int i = 0; i < iterationCount; i++)
		{
			conn.Update(new TestA { Id = 1, Prop1 = 1, Prop2 = 2, Prop3 = 3 });
		}
	});

	var task2 = Task.Run(() =>
	{
		var conn = new SqlConnection(connectionString);
		conn.Open();
		for (int i = 0; i < iterationCount; i++)
		{
			conn.Update(new TestB { Id = 1, Prop1 = "1", Prop2 = "2" });
		}
	});

	Task.WaitAll(task1, task2);

	void Init()
	{
		var conn = new SqlConnection(connectionString);
		conn.Open();
		
		conn.Insert(new TestA { Prop1 = 1, Prop2 = 2, Prop3 = 3 });
		conn.Insert(new TestB { Prop1 = "1", Prop2 = "2" });
	}
}

public class TestA
{
	public long Id { get; set; }
	public long Prop1 { get; set; }
	public long Prop2 { get; set; }
	public long Prop3 { get; set; }
}

public class TestB
{
	public long Id { get; set; }
	public string? Prop1 { get; set; }
	public string? Prop2 { get; set; }
}

public class TestAMapper : ClassMapper<TestA>
{
	public TestAMapper()
	{
		Table("TestA");
		AutoMap();
	}
}

public class TestBMapper : ClassMapper<TestB>
{
	public TestBMapper()
	{
		Table("TestB");
		AutoMap();
	}
}
/*
create table dbo.TestA
(
    Id    int identity,
    Prop1 int,
    Prop2 int,
    Prop3 int
)
go


create table dbo.TestB
(
    Id    int identity,
    Prop1 nvarchar(50),
    Prop2 nvarchar(50)
)
go
*/

@anuviswan
Copy link

Any update on this issue ?

@skovsende
Copy link

Pretty sure this project is more or less abandoned :/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
under investigation Issue is being analyzed
Projects
None yet
Development

No branches or pull requests

5 participants