Solved

SQL Server lock on T_EVENT

  • 14 November 2022
  • 8 replies
  • 92 views

I have a batch job which needs to run as soon as the rules service starts. So there’s a constraint on T_EVENT which invokes that batch job.

Sometimes, but not always, that constraint throws an error:

<ure:entry host="az048vm3001:7222" time="2022-11-07 07:46:33" source="RulesEngine:1" severity="Error">
<ure:request request-id="0"></ure:request>
<uslng:messages app-interface="http" version="1.0">
<uslng:message msg-code="60508" msg-category="Frequent End User Msg" msg-type="Error" system-msg="yes" language="default">
An error occurred when performing the following RDBMS statement:
<uslng:param name="Statement">SELECT ''
FROM CEP_PCSRD.T_EVENT t1
WHERE (t1.EVENT = :1 AND t1.SESSION_ID = :2)</uslng:param>
<uslng:param name="Param2">(STARTUP,14482352E4823)
</uslng:param>
<uslng:param name="Reason">UNIQUE_KEY_REASON</uslng:param>
</uslng:message>
<uslng:message msg-code="931" msg-category="USoft Message" msg-type="Error" system-msg="yes" source="rdbms" language="default">
<uslng:context app-interface="http">
<uslng:execution-stack />
<uslng:record>
<uslng:painted-window-name />
<uslng:table-name>unknown table</uslng:table-name>
<uslng:defined-window-name>unknown window</uslng:defined-window-name>
<uslng:primary-key>unknown pk</uslng:primary-key>
</uslng:record>
</uslng:context>
RDBMS Error:
<uslng:param name="ErrorText">
<rdbms-message>
<rdbms-message>Server: Msg 1222, Level 51. State 16, Line 1
HResult of 0x80040e31 (-2147217871) returned
Error Source: Microsoft OLE DB Driver for SQL Server
Error Description: Lock request time out period exceeded.
(8)
SELECT ''
FROM CEP_PCSRD.T_EVENT t1
WHERE (t1.EVENT = :1 AND t1.SESSION_ID = :2)

(STARTUP,14482352E4823)

---------------
</rdbms-message>
</rdbms-message>
</uslng:param>
Please contact your Application Manager.
</uslng:message>
<uslng:message msg-code="853" msg-category="USoft Message" msg-type="Error" system-msg="yes" language="default">
Cursor error in script.
Failed to open select cursor for statement:
<uslng:param name="Statement">INVOKE EU_AGENT.INITIALISE
WITH
SELECT REPLACE(f.VALUE, '$[tempdir]', (INVOKE RULESENGINE.GETPROPERTY
WITH
SELECT 'USoftTempDir'
)), CONVERT(INTEGER,t.VALUE)
FROM APP_DEFAULT_VALUES f, APP_DEFAULT_VALUES t, APP_DEFAULT_VALUES p, T_EVENT
WHERE (1 = 1 AND f.NAME = 'EU_AGENTS_FILE' AND t.NAME = 'EU_AGENTS_TOP' AND p.NAME = 'EU_AGENTS_RS' AND p.VALUE = (INVOKE SYSTEM_INFO.GETSERVICENAME

))</uslng:param>
</uslng:message>
</uslng:messages>
</ure:entry>

This only occurs when the server is booted and the rules service Windows service starts. Restarting the rules service always fixes the issue.

Anyone have any idea what might cause this?

P.S. USoft 10.0.1I, SQL Server 2016SP2, Windows Server 2019 Datacenter. 

icon

Best answer by USoft Support 14 November 2022, 15:27

View original

8 replies

Badge +1

Hi Marc,

We have discussed your findings and think one of the following could be the problem (some of them you may already have considered).

When the rules service is starting, is it starting multiple rules engines at the same time, all using the same database account? If so, this might conceivably cause the locks. On the other hand, each session gets its unique session ID, so there should be no interference between engines.

So maybe the problem arises from MS SQL Server settings. USoft requires some specific settings for MS SQL Server concerning collation and database properties. For details see: "https://community.usoft.com/installing-usoft-183/microsoft-sql-server-requirements-1098", with special attention to "Collation" and "Database properties".

Do you also encounter this problem on Oracle? Because if that is the case, this would point to a problem with settings for MS SQL Server.

Thanks for the feedback!

We have not had any similar problems on Oracle, so I share your suspicion that this is SQL Server related. I will try to experiment with the settings you mentioned, and will let you know the results.

After changing the collation to Latin1_General_100_CS_AS, we are now getting 

Cannot resolve the collation conflict between "Latin1_General_CS_AS" and "Latin1_General_100_CS_AS" in the equal to operation.

errors.

Any quick and easy solution for this? Else I’ll revert back to Latin1_General_CS_AS.

Badge +1

Hi Marc,

Thanks for the feedback on your findings!

Unfortunately, there doesn't seem to be a quick solution. As I understand it, the collation of already existing data needs to be explicitly converted if a new collation is set. I would guess this is the root cause of your problem. For more detailed information, see https://learn.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-database-collation?view=sql-server-ver16.

Yes, I was afraid that was the issue. I’m going to try recreating the database with the new collation and then reimporting the TDF file. Or else restore the previous version from backup.

Recreating the database and then importing the TDFs seems to have done the trick.

So after a week we haven’t had any more issues, so this seems to have solved the original locking issue 😀. We'll keep monitoring, and if anything changes I’ll report it here.

No more issues, so I think we can call this one solved 😀!

Reply