SQLite not ThreatSafe with ForEach -Parallel?

[Gill-Bates]

I am using a SQlite Database and want to insert Items ForEach -Parallel.

For me it seems, the Module is not Threat Safe:

My Code:

$allItems | ForEach-Object -Parallel {
        Import-Module "SimplySql" -Force
        Open-SQLiteConnection -DataSource $using:database
        Invoke-SqlUpdate -Query $query
}

2024-09-06T10:52:09Z   [Error]   ERROR: An item with the same key has already been added. Key: default
Exception             : 
    Type       : System.ArgumentException
    Message    : An item with the same key has already been added. Key: default
    TargetSite : 
        Name          : ThrowAddingDuplicateWithKeyArgumentException
        DeclaringType : [System.ThrowHelper]
        MemberType    : Method
        Module        : System.Private.CoreLib.dll
    Source     : System.Private.CoreLib
    HResult    : -2147024809
    StackTrace : 
   at System.Collections.Generic.Dictionary`2.TryInsert(TKey key, TValue value, InsertionBehavior behavior)
   at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
   at SimplySql.Engine.Logic.OpenAndAddConnection(baseConnectionDetail connDetail)
   at SimplySql.Cmdlets.OpenSQLiteConnection.ProcessRecord()

$PSVersionTable
Name                           Value
----                           -----
PSVersion                      7.4.5
PSEdition                      Core
GitCommitId                    7.4.5
OS                             Microsoft Windows 10.0.20348
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

SimplySQL Version: 2.0.4.75

[mithrandyr]

@Gill-Bates Yeah -- the module is probably not thread-safe. When you use any of the Open-*Connection cmdlets, you are creating a connection to the database and that connection is stored at the PowerShell session level, i.e. it is globally available. Theoretically, loading the module in separate runspaces (which is what -Parallel of ForEach-Object does) should work. And it does for version 1.9.0. Apparently, as soon as I migrated the module from script to binary, then the data is leaking across runspaces (but I'm not sure why...). So, in this situation you will need to open each connection with a unique name.

Here is a possible workaround, using [guid]::NewGuid() to generate a unique name for each iteration.

$allItems | ForEach-Object -Parallel {
        Import-Module "SimplySql" -Force
        $cn = [guid]::NewGuid().ToString()
        Open-SQLiteConnection -DataSource $using:database -cn $cn
        Invoke-SqlUpdate -Query $query -cn $cn
        Close-SqlConnection -cn $cn
}

[mithrandyr]

@Gill-Bates a follow-up, in version 2.0 the module moved from being a script module to a binary module, and the connections cache is now contained inside a DLL. Because DLLs can only be loaded one per powershell session, they are available across all runspaces, which is why the connections are leaking across. Again, using unique connection names for in your foreach-parallel should resolve -- were you able to confirm?

[Gill-Bates]

Thanks @mithrandyr ! I have solved it like you suggested!

$data | ForEach-Object -Parallel {

    [string]$dbSession = (New-Guid).Guid
    Open-SQLiteConnection -DataSource $using:database -ConnectionName $dbSession

    Invoke-SqlUpdate -cn $ConnectionName -Query ...

    Close-SqlConnection -ConnectionName $dbSession 
}