I have a question to understand how to optimize a database. We have a SQL Server and the main data is ordered vertically. So a record has these columns

ID  version  fieldindex  fieldvalue

and ID, version, fieldindex is the primary key.

So if you want to load a logical recordset you have to load all lines of an ID version. One "record" could contain of somewhat 60 lines. I'm afraid that this causes some problems in the performance of the database.

There are around 10 users working in parallel in the application and we are getting deadlocks very often. We even get deadlocks inserting new lines, so normally a record that isn't persistent can't be locked.

So my question is, how does SQL Server lock records? Is it possible that a record is locked even if I am not selecting this record particularly?

I would be glad if someone could explain how the database is working.

CodePudding user response：

1. You've got EAV, which is generally considered bad.

2. To make EAV work acceptably, you'll need the right indexing structure and possibly some care with lock hints and transactions.

Generally you'll want your clustered index to be (EntityID, AttributeId), so all the attributes for an entity are co-located. But to avoid deadlocks you may need to X lock the main Entity row when modifying the attributes, as SQL Server will otherwise use row locking on the AttributeValue table, which can lead to deadlocks, and logical inconsistencies. You can X lock it by modifying the row as the first operation in your transaction, or by reading it with an XLOCK hint.

Depending on the role of "version" in your system, it will be somewhere in the Clustered Index too. If attributes are individually versioned, then at the end. If individual Entities are viersioned then in the middle. And if a Version contains multiple Entities then as the leading column.