SQL Express restrictions

Avyayah 1,271

One of the application is giving error "Error processing XML: Transaction(Process ID 74) was deadlocked on lock resources with another process and has been chosen as deadlock victim Rerun the transaction". Currently we have sql express installed. Do you think if we install sqlserver standard edition this error will disappear?

Accepted answer

Erland Sommarskog 110.3K Reputation points

2022-09-16T21:37:44.247+00:00

Do you think if we install sqlserver standard edition this error will disappear?

If you are lucky it will. I would not hold my breath, though.

If you have problems with deadlock, you cannot just make stabs in the dark, but you need to retrieve the deadlock XML and analyse the deadlock to be able to take action.

As it happens, I am giving a talk on exactly this topic next week at the Data Platform Summit My session is scheduled for Wednesday.
Please sign in to rate this answer.

1 person found this answer helpful.
PandaPan-MSFT 1,911 Reputation points

2022-09-19T06:32:57.037+00:00

WOW @Erland Sommarskog awsome bro!!That's really cool!!!!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

2 additional answers

PandaPan-MSFT 1,911 Reputation points

2022-09-16T01:40:32.707+00:00

Hi @Avyayah ,
This occurs when two Sql Server processes are accessing the same resources, but in a different order. Therefore they end up both waiting for the other process, which is a deadlock.
There are a number of ways to prevent it, including:
•Avoid taking unneccessary locks. Review the transaction isolation level required for the query, use with (nolock) locking hint for queries where appropriate.
•Make sure that when taking locks you take locks on objects in the same order in each query.

You can see the error situation in the way like this:

And the soultion is in this link:https://sqlbak.com/academy/transaction-process-id-was-deadlocked-on-lock-resources-with-another-process-and-has-been-chosen-as-the-deadlock-victim-msg-1205

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"
Please sign in to rate this answer.
Avyayah 1,271 Reputation points

2022-09-16T03:10:21.023+00:00

@PandaPan-MSFT thanks for the quick response. So it looks like this issue is not related to using SQL Express right?

PandaPan-MSFT 1,911 Reputation points

2022-09-16T03:14:20.877+00:00

Hi @Avyayah , It seems like that it's a very common problem and can happen anyway. To me I don't think it is connected with SQL Express.

Avyayah 1,271 Reputation points

2022-09-21T15:05:19.727+00:00

@PandaPan-MSFT - can I run the query in the master database? So the tables are created in the master database. I did not find any dead locks in system health

Erland Sommarskog 110.3K Reputation points

2022-09-21T20:47:34.937+00:00

To query the system_health session, use this query:

; WITH CTE AS ( SELECT CAST(event_data AS xml) AS xml, timestamp_utc FROM sys.fn_xe_file_target_read_file( N'system_health*.xel', DEFAULT, DEFAULT, DEFAULT) WHERE object_name = 'xml_deadlock_report' ) SELECT xml.query('/event/data[1]/value[1]/deadlock[1]'), timestamp_utc FROM CTE ORDER BY timestamp_utc DESC

You can run this from any database.

The system_health session has two so-called targets, a file target and a ring buffer. The ring buffer is in-memory only, so events will be lost on server restart. Or when the ring buffer is filled with other events. The file session a lot better to use. (I don't exactly which target that Panda's screenshot leads to.)

Avyayah 1,271 Reputation points

2022-09-21T21:46:56.053+00:00

@Erland Sommarskog running the query got the error about time stamp so included them in quote but now getting error:
; WITH CTE AS (
SELECT CAST(event_data AS xml) AS xml, 'timestamp_utc'
FROM sys.fn_xe_file_target_read_file(
N'system_health*.xel', DEFAULT, DEFAULT, DEFAULT)
WHERE object_name = 'xml_deadlock_report'
)
SELECT xml.query('/event/data[1]/value[1]/deadlock[1]'), 'timestamp_utc'
FROM CTE
--ORDER BY 'timestamp_utc' DESC

Msg 8155, Level 16, State 2, Line 1
No column name was specified for column 2 of 'CTE'.

Completion time: 2022-09-21T16:43:22.5139740-05:00

Erland Sommarskog 110.3K Reputation points

2022-09-22T06:36:50.557+00:00

Sorry about that. I was oblivious and overlooked that the timestamp_utc column was added in SQL 2017, and you may be running an older version.

Here is a variation of the query which extracts the timestamp from the XML, so it runs on all versions of SQL Server (but which potentially is slower)

; WITH CTE AS ( SELECT CAST(event_data AS xml) AS xml FROM sys.fn_xe_file_target_read_file( N'system_health*.xel', DEFAULT, DEFAULT, DEFAULT) WHERE object_name = 'xml_deadlock_report' ) SELECT xml.query('/event/data[1]/value[1]/deadlock[1]'), xml.value('/event[1]/@timestamp', 'datetime2(3)') AS timestamp_utc FROM CTE ORDER BY timestamp_utc DESC

PandaPan-MSFT 1,911 Reputation points

2022-09-22T09:32:25.957+00:00

Hi @Avyayah ,
I'm sorry that I didn't see your comment. You can try @Erland Sommarskog 's soultion. I tested it and it works successfully!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Olaf Helper 44,296 Reputation points

2022-09-16T04:56:02.41+00:00

Do you think if we install sqlserver standard edition this error will disappear?

Definitive no, the error isn't related to the SQL Server edition.

as been chosen as deadlock victim Rerun the transaction

Deadlock can happen in current situation, where several process accesses same resources (tables).
The application should be design to do, what the message suggest: Rerun the transaction.
See
https://en.wikipedia.org/wiki/Deadlock
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SQL Express restrictions

2 additional answers

Your answer