A team on my company is facing an issue where the connection parameter Max Pool Size is not being respected when there a too many locks.
The context where this behavior happens is too big, so i made a simple console application to reproduce it.
Code: Select all
class Program
{
static async Task Main(string[] args)
{
var connectionString = "Data Source=127.0.0.1;Port=5432;Database=MaxPoolSize;User ID=postgres;password=admin;Max Pool Size=5";
PostgreSqlLicense.Validate(connectionString);
var recordId = Guid.NewGuid();
var connection = new PgSqlConnection(connectionString);
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = $"INSERT INTO public.\"Records\" (\"Id\", \"Text\") VALUES ('{recordId}'::uuid, 'Teste')";
await command.ExecuteNonQueryAsync();
await connection.CloseAsync();
var tasks = new List<Task>();
foreach (var operation in Enumerable.Range(1, 30))
{
tasks.Add(Task.Run(async () =>
{
try
{
var connection = new PgSqlConnection(connectionString);
await connection.OpenAsync();
var transaction = connection.BeginTransaction();
var sqlText = $"UPDATE public.\"Records\" SET \"Text\" = 'text-{operation}' WHERE \"Id\" = '{recordId}'";
var command = new PgSqlCommand(sqlText, connection, transaction);
await command.ExecuteNonQueryAsync();
await command.DisposeAsync();
}
catch (Exception e)
{
Console.WriteLine("Operation {0} failed", operation);
throw;
}
}));
}
await Task.WhenAll(tasks);
Console.WriteLine("Done!");
}
}
The table I'n using is very simple. It's table named "Records" with Primary Key "Id" type uuid and a column "Text" type text.
The application starts with 6 connections (one of them being the connection that was opened to validate the license), which already exceeds the configured limit. After one minute it goes to 10, after another minute 14, then 18, then 19, then all connections time out and the line Task.WhenAll (tasks) throws.
The connections timeout is expected, but exceeding Max Pool Size was not.
It is possible to see when the Max Pool Size has been reached, the code stops on the OpenAsync method, but than after a minute and some connections timeouts, it allows more connections.