Post reply

Guidance on table valued types.

  • March 6, 2013 at 8:01 am

    #1593987

    mayur birari (3/6/2013)

    Hi @opc,

    I did some testing with the indexed tvps interms of deadlocks. I did not find any difference between non-indexed and indexed versions. I created a simple application which spaws n number of threads and then each thread would insert m number of items into the tables. The surrogate PK in our database is Guid and we generate it in .Net with Guid.NewGuid(). The deadlocks that occur are index locks mainly because of the foreign checks.

    Regards,

    Mayur

    OK, so you could not create a case where a deadlock occurred, but they can happen more often when TVPs are used if the underlying table type does not have a clustered index. You'll find this case documented (I am on my phone so don't have easy way to look it up) mostly when the TVP participates in a MERGE statement but I could also see it being common when it participates in any JOIN within a transaction too.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 6, 2013 at 8:04 am

    #1593990

    Thanks for the quick reply. What I meant to say is I was able to produce deadlocks even with the indexed tvps.

  • March 6, 2013 at 8:14 am

    #1593997

    mayur birari (3/6/2013)

    Thanks for the quick reply. What I meant to say is I was able to produce deadlocks even with the indexed tvps.

    There is guarantee it will remove them. If you have two procs that acces the same resources in a different order, both in a transaction, nothing is going to save you. All I am saying is that having a clustered index on the table type will reduce deadlocks in some situations when compared to an equivalent setup minus the clustered index.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply