Skip to main content
  • EN

Hi - I am experiencing a strange error when attempting to update the same table twice which I have narrowed down to be a connection lock or something similar. 

This is a part of my workbench which updates a timestamps table in SQL Server. Incoming to each of these executes effectively at the same time.

If I disable either one - I am able to update the table with no issues so I have isolated any configuration of the writers.

I have increased timeouts etc. and no luck. Doesn't look to be related to this. These queries only take a sec or so to run but looks like a connection locking issue. I am running SQL Server 2019.

Any ideas what could be causing this? Both of the FeatureWriters using the same connection - SQL Server Non-Spatial Writer.

Thanks

Error - 

2020-06-08 19:58:07| 2.6|  0.0|INFORM|Microsoft SQL Server Non-Spatial Writer: Trying to connect using Microsoft OLE DB Driver for SQL Server...

2020-06-08 19:58:37| 2.7|  0.0|WARN  |Microsoft SQL Server Non-Spatial Writer: Failed to write a feature of type `dbo.TIMESTAMPS' to the database. Provider error `(-2147217871) Query timeout expired'. SQL Command `UPDATE dbo.nTIMESTAMPS] SET :Changeset_Checked_Time_UTC]=? WHERE OBJECTID = '4''

 

0684Q00000ArFYfQAN.png

@jiriteach Try setting the transaction interval to 1 or try using the fme_db_transaction attribute. This might prevent the conflict your encountering.

Features Per Transaction

This parameter sets the number of features to be placed in each transaction before a transaction is committed to the database.

A default value of 500 is used as the transaction interval.

If this parameter is set to 0, then feature-based transactions are used. As each feature is processed by the writer, it is checked for an attribute called fme_db_transaction. The value of this attribute specifies whether the writer should commit or roll back the current transaction.

The value of the attribute can be one of COMMIT_BEFORE, COMMIT_AFTER, ROLLBACK_AFTER or IGNORE. If the fme_db_transaction attribute is not set in any features, then the entire write operation occurs in a single transaction.

@jiriteach Try setting the transaction interval to 1 or try using the fme_db_transaction attribute. This might prevent the conflict your encountering.

Features Per Transaction

This parameter sets the number of features to be placed in each transaction before a transaction is committed to the database.

A default value of 500 is used as the transaction interval.

If this parameter is set to 0, then feature-based transactions are used. As each feature is processed by the writer, it is checked for an attribute called fme_db_transaction. The value of this attribute specifies whether the writer should commit or roll back the current transaction.

The value of the attribute can be one of COMMIT_BEFORE, COMMIT_AFTER, ROLLBACK_AFTER or IGNORE. If the fme_db_transaction attribute is not set in any features, then the entire write operation occurs in a single transaction.

Thanks. I set Features Per Transaction to 1 and that worked perfectly. Thanks for the pointer.

Reply


Community Stats

30,176
Posts
114,543
Replies
37,835
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.