KB484260: Metadata database deadlock occurs when a user logs in frequently in a short span of time

Product Owner, Principal • MicroStrategy

This article addresses the deadlock issue when logging in frequently in a short amount of time. This article also provides a solution to avoid this behavior.

Description

A metadata database deadlock may occur when you log in repeatedly in a short span of time. Operations like user login can become delayed. In the

DSSErrors.log

, you can many error messages with the following:
Execute Query failed. Deadlocked
For each metadata database type, the error messages can differ. The following are examples of errors encountered during internal testing.

PostgreSQL:


2020-04-30 08:48:20.777 [HOST:env-202399laiouse1][SERVER:CastorServer][PID:25214][THR:139933024380672][MSql][Error][UID:0][SID:0][OID:0] Error executing SQL: INSERT INTO DSSMDLNKPROP VALUES ('6376871211EA8AA700000080EF355FC5', '45FE761011D53367C0002E894C78604F', 3, 1, '1;128;0D3C86230BAB7EC9964C160A78379D48A86ECE8667B992D2EBFCA4876DDAE18C4A4FCA2F7021DC7221A609EB6098DAE87F52C44637464E121F0143B592B2EABD', 8)
2020-04-30 08:48:22.048 [HOST:env-202399laiouse1][SERVER:CastorServer][PID:25214][THR:139933039089408][Database Classes][Error][UID:0][SID:0][OID:0] Execute Query failed. Deadlocked.

SQL Server 2012:


2020-05-13 02:56:23.338 [HOST:env-206772laiouse1][SERVER:CastorServer][PID:8699][THR:140215826523904][Database Classes][Error][UID:0][SID:0][OID:0] Execute Query failed. Deadlocked.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [40001:1205: on SQLHANDLE] [Strategy][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Transaction (Process ID 177) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Connection String: DSN=sqlserver;UID=123;DB=123;PORT=1433;HOST=11.11.11.11;. SQL Statement: DELETE FROM DSSMDLNKPROP WHERE LINKITEM_ID='0E37A67C-943A-11EA-8000-0000432D55EF'; DELETE FROM DSSMDLNKITEM WHERE LINKITEM_ID='0E37A67C-943A-11EA-8000-0000432D55EF'.

OR

2020-05-13 02:56:23.450 [HOST:env-206772laiouse1][SERVER:CastorServer][PID:8699][THR:140215826523904][Database Classes][Error][UID:0][SID:0][OID:0] Execute Query failed. Deadlocked.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [40001:1205: on SQLHANDLE] [Strategy][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Transaction (Process ID 177) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Connection String: DSN=sqlserver;UID=123;DB=123;PORT=1433;HOST=11.11.11.11;. SQL Statement: SELECT a1.LINKITEM_ID, a1.OBJECT_ID, a2.OBJECT_ID FROM DSSMDLNKITEM a1 INNER JOIN DSSMDLNKITEM a2 ON a1.LINKITEM_ID=a2.LINKITEM_ID WHERE a1.LINK_ID='C63668E4-9EEB-4814-8FC3-0A12ADBD316E' AND a1.PROJECT_ID='38A06230-11D2-2D44-6000-718E67019608' AND a1.OBJECT_TYPE=34 AND a1.OBJECT_ID='CD8869DF-5324-4034-A8E8-C6539FD341C2' AND a2.OBJECT_TYPE=36 AND a2.OBJECT_ID='38A06230-11D2-2D44-6000-718E67019608'.

OR

2020-05-13 02:56:28.352 [HOST:env-206772laiouse1][SERVER:CastorServer][PID:8699][THR:140215811815168][Database Classes][Error][UID:0][SID:0][OID:0] OdbcPreparedStatement::ExecQuery failed. Deadlocked.
Error type: Odbc error. Odbc operation attempted: SQLExecute. [40001:1205: on SQLHANDLE] [Strategy][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Transaction (Process ID 265) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Connection String: DSN=sqlserver;UID=123;DB=123;PORT=1433;HOST=11.11.11.11;. SQL Statement: SELECT LINKITEM_ID, PROPSET_ID, PROP_ID, PROP_VAL, PROP_TYPE FROM DSSMDLNKPROP WHERE LINKITEM_ID=? ORDER BY LINKITEM_ID ASC, PROPSET_ID ASC, PROP_ID ASC, VAL_SEQ ASC.

Why is this happening?

This is a design limitation where if the same user logs in frequently in a short time, it will result in a list of threads for operations like select, delete, or insert, concurrently with the same row. Thus, a deadlock may occur.

Solution

The deadlock error is addressed in Strategy 2020 Update 2. Upgrade to Strategy 2020 Update 2 to take advantage of the fix.
For versions prior to Strategy 2020 Update 2, do the following:
Under Server Definition > Security > Account Lockout Policy, set the Lock after value to -1.

Comment

0 comments

Details

Knowledge Article

Published:

May 14, 2020

Last Updated:

July 10, 2020

Description

A metadata database deadlock may occur when you log in repeatedly in a short span of time. Operations like user login can become delayed. In the

DSSErrors.log

Execute Query failed. Deadlocked

PostgreSQL:

2020-04-30 08:48:20.777 [HOST:env-202399laiouse1][SERVER:CastorServer][PID:25214][THR:139933024380672][MSql][Error][UID:0][SID:0][OID:0] Error executing SQL: INSERT INTO DSSMDLNKPROP VALUES ('6376871211EA8AA700000080EF355FC5', '45FE761011D53367C0002E894C78604F', 3, 1, '1;128;0D3C86230BAB7EC9964C160A78379D48A86ECE8667B992D2EBFCA4876DDAE18C4A4FCA2F7021DC7221A609EB6098DAE87F52C44637464E121F0143B592B2EABD', 8) 2020-04-30 08:48:22.048 [HOST:env-202399laiouse1][SERVER:CastorServer][PID:25214][THR:139933039089408][Database Classes][Error][UID:0][SID:0][OID:0] Execute Query failed. Deadlocked.

SQL Server 2012:

2020-05-13 02:56:23.338 [HOST:env-206772laiouse1][SERVER:CastorServer][PID:8699][THR:140215826523904][Database Classes][Error][UID:0][SID:0][OID:0] Execute Query failed. Deadlocked. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [40001:1205: on SQLHANDLE] [Strategy][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Transaction (Process ID 177) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Connection String: DSN=sqlserver;UID=123;DB=123;PORT=1433;HOST=11.11.11.11;. SQL Statement: DELETE FROM DSSMDLNKPROP WHERE LINKITEM_ID='0E37A67C-943A-11EA-8000-0000432D55EF'; DELETE FROM DSSMDLNKITEM WHERE LINKITEM_ID='0E37A67C-943A-11EA-8000-0000432D55EF'. OR 2020-05-13 02:56:23.450 [HOST:env-206772laiouse1][SERVER:CastorServer][PID:8699][THR:140215826523904][Database Classes][Error][UID:0][SID:0][OID:0] Execute Query failed. Deadlocked. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [40001:1205: on SQLHANDLE] [Strategy][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Transaction (Process ID 177) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Connection String: DSN=sqlserver;UID=123;DB=123;PORT=1433;HOST=11.11.11.11;. SQL Statement: SELECT a1.LINKITEM_ID, a1.OBJECT_ID, a2.OBJECT_ID FROM DSSMDLNKITEM a1 INNER JOIN DSSMDLNKITEM a2 ON a1.LINKITEM_ID=a2.LINKITEM_ID WHERE a1.LINK_ID='C63668E4-9EEB-4814-8FC3-0A12ADBD316E' AND a1.PROJECT_ID='38A06230-11D2-2D44-6000-718E67019608' AND a1.OBJECT_TYPE=34 AND a1.OBJECT_ID='CD8869DF-5324-4034-A8E8-C6539FD341C2' AND a2.OBJECT_TYPE=36 AND a2.OBJECT_ID='38A06230-11D2-2D44-6000-718E67019608'. OR 2020-05-13 02:56:28.352 [HOST:env-206772laiouse1][SERVER:CastorServer][PID:8699][THR:140215811815168][Database Classes][Error][UID:0][SID:0][OID:0] OdbcPreparedStatement::ExecQuery failed. Deadlocked. Error type: Odbc error. Odbc operation attempted: SQLExecute. [40001:1205: on SQLHANDLE] [Strategy][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Transaction (Process ID 265) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Connection String: DSN=sqlserver;UID=123;DB=123;PORT=1433;HOST=11.11.11.11;. SQL Statement: SELECT LINKITEM_ID, PROPSET_ID, PROP_ID, PROP_VAL, PROP_TYPE FROM DSSMDLNKPROP WHERE LINKITEM_ID=? ORDER BY LINKITEM_ID ASC, PROPSET_ID ASC, PROP_ID ASC, VAL_SEQ ASC.