Microsoft.Data.Sqlite SqliteConnection 释放后文件还是被锁着 #34167
Description
Issue description
释放数据库连接后 文件未释放
Target framework
- .NET Core
- .NET Framework
- .NET Standard
Windows 10 21H1
CPU: Intel(R) Core(TM) i5-10400F CPU @ 2.90GHz 2.90 GHz
RAM: 32G
Disk: SSD
Microsoft.Data.Sqlite 7.0.3
Test.csproj
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFrameworks>net7.0;net40</TargetFrameworks>
<Nullable>disable</Nullable>
<Version>2023.02.17.1</Version>
</PropertyGroup>
<ItemGroup Condition="'$(TargetFramework)' == 'net40'">
<PackageReference Include="System.Data.SQLite.Core" Version="1.0.117" />
</ItemGroup>
<ItemGroup Condition="'$(TargetFramework)' == 'net7.0'">
<PackageReference Include="Microsoft.Data.Sqlite" Version="7.0.3" />
</ItemGroup>
</Project>Program.cs
using System;
using System.Threading.Tasks;
using System.IO;
using System.Linq;
#if NETFRAMEWORK
using System.Data.SQLite;
#else
using SQLiteConnection = Microsoft.Data.Sqlite.SqliteConnection;
#endif
namespace Wenhe.DB.SQliteTest
{
internal class Program
{
private const string DB_DIR = "C:\\SnapDb";
static void Main(string[] args)
{
if (!Directory.Exists(DB_DIR))
{
Directory.CreateDirectory(DB_DIR);
}
var dbFiles = Directory.GetFiles(DB_DIR).ToList();
dbFiles.RemoveAll(dbFile =>
{
if (!dbFile.EndsWith(".db"))
{
return true;
}
return false;
});
if (dbFiles.Count == 0)
{
Console.WriteLine("No db file");
Console.WriteLine("Press enter to exit");
Console.ReadLine();
return;
}
var oldColor = Console.ForegroundColor;
Console.ForegroundColor= ConsoleColor.Green;
Console.WriteLine($".NET Version : {Environment.Version}");
Console.WriteLine($"SQLiteConnection FullName : {typeof(SQLiteConnection).FullName} Version : {typeof(SQLiteConnection).Assembly.GetName().Version}");
Console.WriteLine();
Console.ForegroundColor = oldColor;
Task<long>[] tasks = new Task<long>[dbFiles.Count];
var tim1 = DateTime.Now;
for (int i = 0; i < dbFiles.Count; i++)
{
var connStr = string.Format("DATA source= {0}", dbFiles[i]);
var task = Task.Factory.StartNew(() => GetOneDbDataNum(connStr));
tasks[i] = task;
}
Task.WaitAll(tasks);
WriteMsg($"ALL | Finish | <{DateTime.Now - tim1}> ");
Console.WriteLine();
Console.ForegroundColor = ConsoleColor.Cyan;
Console.WriteLine("Press Enter to exit");
Console.ForegroundColor = oldColor;
Console.ReadLine();
}
/// <summary>
/// </summary>
/// <param name="connStr"></param>
/// <returns></returns>
private static long GetOneDbDataNum(string connStr)
{
long totalNum = 0;
try
{
using (SQLiteConnection conn = new SQLiteConnection(connStr))
{
conn.Open();
using (var comm = conn.CreateCommand())
{
comm.CommandText = "SELECT COUNT(*) FROM [tblSnapRecord] WHERE [PersonName] LIKE '%abc%' AND [SnapTime] >= '2022/02/06 16:34:48' AND [SnapTime] <= '2024/02/06 16:34:48'; ";
var tim1 = DateTime.Now;
WriteMsg($"{conn.ConnectionString} | ExecuteReaderAsync | Start |");
using (var reader = comm.ExecuteReader())
{
WriteMsg($"{conn.ConnectionString} | ExecuteReaderAsync | Finish | <{DateTime.Now - tim1}> ");
if (reader != null
&& reader.HasRows
&& reader.Read())
{
totalNum = (long)reader[0];
}
reader.Close();
}
}
conn.Close();
}
}
finally
{
}
return totalNum;
}
private static void WriteMsg(string msg)
{
Console.WriteLine($"{DateTime.Now:HH:mm:ss.fff} TaskId:<{(Task.CurrentId.HasValue? Task.CurrentId.Value.ToString("00"):"--")}> | {msg} ");
}
}
}CreateDb
CREATE TABLE [tblSnapRecord](
[Id] NVARCHAR DEFAULT (HEX(RANDOMBLOB(16))) PRIMARY KEY ON CONFLICT REPLACE NOT NULL ON CONFLICT FAIL,
[PersonId] NVARCHAR,
[PersonName] NVARCHAR,
[PersonState] NVARCHAR DEFAULT 'UNKNOW' COLLATE NOCASE,
[PersonType] NVARCHAR COLLATE NOCASE,
[IdentificationMode] NVARCHAR DEFAULT 'UNKNOW' COLLATE NOCASE,
[Similarity] DOUBLE DEFAULT -1,
[DeviceCode] NVARCHAR,
[DeviceName] NVARCHAR,
[LocationCode] NVARCHAR,
[PhotoUrl] NVARCHAR,
[PhotoPath] NVARCHAR,
[CardNo] NVARCHAR,
[Qrcode] NVARCHAR,
[SnapTime] DATETIME,
[AbnormalTemperature] BOOL DEFAULT 0,
[InOutType] NVARCHAR DEFAULT 'UNKNOW' COLLATE NOCASE,
[Mask] NVARCHAR DEFAULT 'UNKNOW' COLLATE NOCASE,
[Glass] NVARCHAR DEFAULT 'UNKNOW' COLLATE NOCASE,
[Smile] NVARCHAR DEFAULT 'UNKNOW' COLLATE NOCASE,
[Temperature] FLOAT DEFAULT 0,
[HealthInfo] NVARCHAR,
[IsUpload] BOOL DEFAULT 0,
[UpdateTime] TIMESTAMP DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f','now','localtime')),
[CreateTime] TIMESTAMP DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f','now','localtime'))
) WITHOUT ROWID;
CREATE INDEX [tblSnapRecord_PersonName_CardNo_PersonId_Index] ON [tblSnapRecord]([PersonName],[CardNo],[PersonId]);
CREATE INDEX [tblSnapRecord_PersonName_Index] ON [tblSnapRecord]([PersonName]);
CREATE INDEX [tblSnapRecord_PersonName_CardNo_SnapTime_Index] ON [tblSnapRecord]([PersonName],[CardNo],[SnapTime] DESC);
CREATE INDEX [tblSnapRecord_PersonName_SnapTime_Index] ON [tblSnapRecord]([PersonName],[SnapTime] DESC);
CREATE INDEX [tblSnapRecord_PersonId_PersonName_Index] ON [tblSnapRecord]([PersonId],[PersonName]);
CREATE INDEX [tblSnapRecord_PersonState_Index] ON [tblSnapRecord]([PersonState]);
CREATE INDEX [tblSnapRecord_PersonName_PersonId_SnapTime_Index] ON [tblSnapRecord]([PersonName],[PersonId],[SnapTime] DESC);
CREATE INDEX [tblSnapRecord_PersonId_Index] ON [tblSnapRecord]([PersonId]);
CREATE INDEX [tblSnapRecord_PersonName_CardNo_Index] ON [tblSnapRecord]([PersonName],[CardNo]);
CREATE INDEX [tblSnapRecord_CardNo_PersonId_SnapTime_Index] ON [tblSnapRecord]([CardNo],[PersonId],[SnapTime] DESC);
CREATE INDEX [tblSnapRecord_PersonId_CardNo_Index] ON [tblSnapRecord]([PersonId],[CardNo]);
CREATE INDEX [tblSnapRecord_PersonName_CardNo_PersonId_SnapTime_Index] ON [tblSnapRecord]([PersonName],[CardNo],[PersonId],[SnapTime] DESC);
CREATE INDEX [tblSnapRecord_DeviceName_Index] ON [tblSnapRecord]([DeviceName]);
CREATE INDEX [tblSnapRecord_CardNo_Index] ON [tblSnapRecord]([CardNo]);
CREATE INDEX [tblSnapRecord_CardNo_SnapTime_Index] ON [tblSnapRecord]([CardNo],[SnapTime] DESC);
CREATE INDEX [tblSnapRecord_SnapTime_Index] ON [tblSnapRecord]([SnapTime] DESC);
CREATE INDEX [tblSnapRecord_PersonId_SnapTime_Index] ON [tblSnapRecord]([PersonId],[SnapTime] DESC);
CREATE INDEX [tblSnapRecord_IsUpload_Index] ON [tblSnapRecord]([IsUpload]);
CREATE TRIGGER [UpdateSnapRecordUpdateTime]
AFTER UPDATE
ON [tblSnapRecord]
BEGIN
UPDATE [tblSnapRecord] SET [UpdateTime] = (STRFTIME('%Y-%m-%d %H:%M:%f','now','localtime'))
WHERE [tblSnapRecord].[Id] = old.[Id];
END;
插入数据 每个库插入30万数据
INSERT INTO [tblSnapRecord]([Id], [PersonId], [PersonName], [PersonState], [DeviceCode], [DeviceName] ,[CardNo],[SnapTime], [InOutType])VALUES(HEX(RANDOMBLOB(16)), 'c8789495db7f48bb8578aa77fe390d4d', 'abceefg', 'AUTH', 'df44f9ce98b94c6d9b7af9de2541ad33', 'test_device','167540423517670578','2023-02-06 16:34:48.000', 'IN_OUT');