Skip to main content
Solved

Microsoft SQL Server Non-Spatial Writer Connection Lock?

jiriteach
Forum|alt.badge.img

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.[TIMESTAMPS] SET [Changeset_Checked_Time_UTC]=? WHERE OBJECTID = '4''

 

0684Q00000ArFYfQAN.png

Best answer by markatsafe

@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.

View original
Did this help you find an answer to your question?

M
Forum|alt.badge.img+2

@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
Forum|alt.badge.img
markatsafe wrote:

@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


Helpful Members This Week

Community Stats

31,031
Posts
117,605
Replies
38,810
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.