Here is the test application I did to reproduce the deadlock constantly C. I discovered that the problem arise when the clustered index is not the same as the primary key. I have a large table with an ID identity primary key field where clustered index has been unchecked and I created a separate clustered index on a datetime field for faster queries.
That is my conclusion for the moment. Any comments? That causes data fragmentation so it even makes selects slower. Anyway, I think you have the solution, just create clustered index on identity column.
I am still interested in a repro script that I can post on the private MVP newsgroups. Recently Paul Randal joined there. Paul is dev lead of the SQL Server storage engine. Maybe you can make him aware of your problem by sending him a PM via his profile.
Nothing else would I do with the repro script. Even if you think you solved your problem for the moment, think about it again. So, you only have a kludgy workaround at the moment. The first process holds an exclusive lock on the key it has updated. This blocks the second process.
I guess the lock is needed to prevent other processes changing the index that is accessed for the update. In this case the shared lock must be converted to exclusive. If two processes does this at the same time, there is a conversion deadlock. Henrik Staun Poulsen Sreejith Nair Sreejith Nair 4, 4 4 gold badges 27 27 silver badges 43 43 bronze badges. Triggers can accomplish this assuming the updatedby value can be determined.
Although not "out-of-the-box", this is often done via triggers like Alex suggested. You should edit your question to explain that you can't use Triggers and why. It was clearly mentioned as out-of-the-box. Do you still think it is worth listing exclusion list of database objects? Add a comment.
Active Oldest Votes. Make step 1 run this code: Insert into dbo. CreatedDate HIH. Improve this answer. Thanks for your post. I am behind out-of-the-box solution. Your approach is not clear to me fully though.
I've edited my reply a bit, and here is also a link to a post that I've written: dba. UpdateTrans ON [dbo]. Alex Alex John Bell John Bell 2, 1 1 gold badge 14 14 silver badges 23 23 bronze badges.
Sorry I can't use triggers because of transnational size. Zerotoinfinity Zerotoinfinity 5, 31 31 gold badges silver badges bronze badges. The SQL statement above would insert a new record into the 'Persons' table. The 'Personid' column would be assigned a unique value. You will have to create an auto-increment field with the sequence object this object generates a number sequence.
It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access. Yesterday I was rewriting some code from Entity Framework 6 to Entity Framework 2 for testing and discovered unexpected behavior.
In Entity Framework 6 you can create an entity and set the primary key property to whatever value you want, because in case the ID is generated in database i.
0コメント