I have a script to synchronize files in a server folder with documents in a ProjectWise folder. I have several thousands of documents to sync so I have 'powershelled' the task.
One of my commands is to create a new document version:
Get-PWDocumentsBySearch -FolderPath $pwfolder -FileName $theFile | New-PWDocumentVersion -VersionString $verString | Out-Null
This will happily run for long periods of time, and then fall over for no apparent reason:
New-PWDocumentVersion : Error changing document 'FILENAME' to new version. Error creating new version of document.At SCRIPTNAME1:548 char:97+ ... e $theFile | New-PWDocumentVersion -VersionString $verString | Out-N ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (Version:String) [New-PWDocumentVersion], Exception + FullyQualifiedErrorId : ErrorID: Database transaction deadlock error.,PWPS_DAB.NewPWDocumentVersion
What is a database transaction deadlock error and how to prevent it? For the record, the $verString is set to "P02" and the previous version is "P01".
Thanks for any help.
A deadlock error occurs when a the row of the database is locked by one thread and another thread is attempting to lock the row. Since ProjectWise transactions are pooled into one thread from the Integration server, I would assume that either another program is accessing the database at the same time as ProjectWise. You need to involve your DBA to determine what thread has the lock and is causing the deadlock to occur.
Thank you. That is a mystery as I can't think what other program would be accessing at the same time.
As it's managed service - I imagine our DBA is Bentley; PowerShell queries however get directed to the forum. Bit stuck on this one!
I don't know what might be causing the deadlock, but if you want to script around it, you can use a Try/Catch block to catch the error, maybe add a pause and then retry.
try { Get-PWDocumentsBySearch -FolderPath $pwfolder -FileName $theFile | New-PWDocumentVersion -VersionString $verString | Out-Null } catch { write-error "create new version failed, retrying" start-sleep -seconds 10 Get-PWDocumentsBySearch -FolderPath $pwfolder -FileName $theFile | New-PWDocumentVersion -VersionString $verString | Out-Null }
details on try/catch here:
https://www.vexasoft.com/blogs/powershell/7255220-powershell-tutorial-try-catch-finally-and-error-handling-in-powershell
and here:
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_try_catch_finally?view=powershell-6
probably slightly more performant to capture the file in a variable:
$fileObj = Get-PWDocumentsBySearch -FolderPath $pwfolder -FileName $theFile try { $fileObj | New-PWDocumentVersion -VersionString $verString | Out-Null } catch { write-error "create new version failed, retrying" start-sleep -seconds 10 $fileObj | New-PWDocumentVersion -VersionString $verString | Out-Null }
I like this - thank you - seems the best way forward.