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!
Since we have blocking issues (very similar) with a quite performant VM hosting MSSQL and the Integration server, I would love to know if you chased this one down for support from the Bentley database side. Sure they can say they don't support PWPS_DAB but they should be able to answer what the DB is doing. Since ours is hosted by us for now, it's on us to find the issue which we suspect is our Isilon disk array system yet cannot prove thus far.
An alternate to Kevin's solution is to build constant "kindness" AKA delays into your code in case the code is overtaxing the design/servers. Kevin's just retries once. PW also has Jobs (or similar - not scheduled tasks) that you could explore to enqueue failed files which are reprocessed until done. That concept/object suite is on my to-do list to learn.