在SQL服务器和数据库锁中使用外键 [英] Using foreign key in SQL server and DB locks

问题描述

我一直在使用SQL Server很长一段时间，当表格之间的逻辑连接存在时，我总是使用FK和索引。例如：

$ b

  MyTable1 
 {
 ID BIGINT IDENTITY（1，1）NOT NULL，
 SomeData NVARCHAR（255）NOT NULL ，
 MyFK BIGINT NULL  - 这是一个FK到MyTable2.ID 
} 
 
 MyTable2 
 {
 ID BIGINT IDENTITY（1，1）NOT NULL，
 SomeData NVARCHAR（255）NOT NULL 
} 
  

问题
当我在MyTable1上执行一些更新MyFK的批量更新操作
并同时向MyTable2执行insert语句时，我们会挂起，直到发生超时或更新完成，并且锁发布。

据我所知当插入到具有FK的表中时，数据库引擎需要锁定相关的表来验证FK，是问题的根源。

• 删除表上的锁定升级选项 http://msdn.microsoft.com/en-us/library/ms184286 ％28v = sql.105％29.aspx




• 将索引上的锁更改为基于行而不是基于页的
  http://msdn.microsoft.com/zh-cn/ -us / library / ms189076％28v = sql.105％29.aspx

我去死锁和糟糕的表现。

当我删除了FK，这一切运作良好，但有数据损坏的风险。

问题：

1. 是否有推荐的使用FK的地方，哪里不要？ / li>
   
2. 您可以提供任何其他解决方案，但删除FK以解决我的问题吗？
   

以防万一 page asasfrob found down down，the answer it was：

定义父表（TableA）上的主键作为非集群
索引。一旦你这样做，这个问题将不会发生，因为查找
对非聚集索引发生，并且不会被锁定
状态，因为PK列没有被修改。

I have been working with SQL Server for a long time I always use FKs and indexes when a logic connection between tables exist

Example:

MyTable1
{
    ID      BIGINT IDENTITY (1, 1)  NOT NULL,
    SomeData    NVARCHAR(255)       NOT NULL,
    MyFK        BIGINT          NULL -- this is a FK to MyTable2.ID
}

MyTable2
{
    ID      BIGINT IDENTITY (1, 1)  NOT NULL,
    SomeData    NVARCHAR(255)       NOT NULL
}

Now to the problem, When I execute some bulk update operations on MyTable1 that update MyFK, and in the same time execute an insert statements to MyTable2, we hang till a timeout occur or the update is done and the locks are released.

As far as I know when inserting to a table that has FKs, the DB engine needs to get a lock on the relevant table to validate the FK, and that is the source for the problem.

Things I tries to resolve the problem:

• removed the lock escalation option on the table http://msdn.microsoft.com/en-us/library/ms184286%28v=sql.105%29.aspx

• Changing the locks on the index to be row based and not page based http://msdn.microsoft.com/en-us/library/ms189076%28v=sql.105%29.aspx

Both solutions lead me to deadlocks and bad performance.

When I removed the FK, it all worked well, but there is the risk of data corruption.

Questions:

1. Is there any recommended set of rules on where to use a FK and where not to?
2. Can you offer me any other solution but removing the FK to overcome my problem?

解决方案

Just in case the page asasfrob found goes down, the answer it gave was:

Define the primary key on the parent table (TableA) as a non-clustered index. Once you do that, the issue won’t occur since the look-up will occur against the non-clustered index and that won’t be in a locked state since the PK column is not being modified.

这篇关于在SQL服务器和数据库锁中使用外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！