data access

Question

Post reply

data access

sqlfriend

SSC Guru

Points: 52781
More actions
March 13, 2015 at 4:54 pm

#398183

I have read some good practice of SQL server database.
One of them is about data access.
It is recommended always access tables in the same order in all your stored procedures and triggers consistently. This helps to avoid deadlocks.
Can someone help to explain this?
Thanks
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Scenario 1

------------

I want A and B

You want A and B

You take A

I take B

You try to take B, but I have it

I try to take A, but you have it.

We could wait until someone gets bored and gives up A or B, but instead I pull out a knife...you lose.

Scenario 2

------------

I want A and B

You want A and B

You take A

I try to take A, but you have it. You say "hold on a second, I'm nearly done with A", then you grab B, do your thing and put them both down.

I can then take A, then B.

No knives. We all win.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Thanks, try to understand the two Scenarios, the first one must be in different order, the second one is same order, one in the first case, you pull a knife but not just wait. It is still a little abstract to me, and I cannot get it.

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 3

sqlfriends (3/13/2015)
I have read some good practice of SQL server database. One of them is about data access.
It is recommended always access tables in the same order in all your stored procedures and triggers consistently. This helps to avoid deadlocks.
Can someone help to explain this?
Thanks

I haven't heard this before and I believe the query optimizer (assuming you're talking about linking at least two tables in the same query) is going to use them in whatever sequence it finds most efficient regardless of the logical sequence in your query.

sqlfriend SSC Guru Points: 52781 More actions · Answer 4

I found this article.

But I don't know how to make the order of table the same, for if there are multiple developers, it is hard for them to know the order of others wrote.

https://technet.microsoft.com/en-us/library/ms191242(v=sql.105).aspx

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 5

I was referring to sequencing in a FROM clause as opposed to the sequence of transactions. Sorry I misunderstood.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

sqlfriends (3/16/2015)
I found this article.
But I don't know how to make the order of table the same, for if there are multiple developers, it is hard for them to know the order of others wrote.
https://technet.microsoft.com/en-us/library/ms191242(v=sql.105).aspx

There is no magic answer, you just have to try your best, decide on an order in which to update tables and try to stick to it.

Peer code review will help with enforcing that.

Remember that this is really about being organised within a transaction.

If you are updating Table1 in a separate transaction from Table2 and there are no relationships to be maintained, then the order you do it is less important.

If you are making updates to multiple tables within the same transaction, then keeping to the same sequence/order will help.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

sqlfriend SSC Guru Points: 52781 More actions · Answer 7

Thanks,

But I see the example in the graphic in the link is two transactions.

https://technet.microsoft.com/en-us/library/ms191242(v=sql.105).aspx

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

Yes, it is two transactions, each updating two tables.

So, it is (two tables being updated within the same transaction) X 2

In this situation, updating the tables in the same order helps to avoid deadlocks.

If Transaction 1 was updating only Supplier and Transaction 2 was updating only Part, then there is no order within each transaction to worry about.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

sqlfriend SSC Guru Points: 52781 More actions · Answer 9

sqlfriend

SSC Guru

Points: 52781

March 16, 2015 at 5:18 pm

#1784385

Thanks, that makes sense now.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

jshahan (3/16/2015)
I haven't heard this before and I believe the query optimizer (assuming you're talking about linking at least two tables in the same query) is going to use them in whatever sequence it finds most efficient regardless of the logical sequence in your query.

The guidance isn't talking about within a statement (where the QO can and will change table orders), it's talking about order of statements within a batch/procedure.

If you put an insert into Orders after an update to Customers, the insert into Orders will always, without exception run after the update to Customers. The QO can't change the order that statements run in within a batch.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 11

Gail

jshahan (3/16/2015)
I was referring to sequencing in a FROM clause as opposed to the sequence of transactions. Sorry I misunderstood.