从select插入时表与行锁 [英] Table vs. row locks when inserting from select
问题描述
我们必须在SQL Server 2005 db（表A）中更改表中nvarchar字段的大小，并且由于表中的数据量，此修改将导致一些严重的访问问题。为了解决这个问题，我创建了一个具有相同模式的表B，除了较大的nvarchar字段。一旦我将数据从A复制到B，我将删除A并重命名B.
我的问题是关于行与表锁。如果我这样做：
INSERT INTO B（[field1]，[field2]）
SELECT [field1]，[field2] FROM A
我填充B时是否会在A上有表级锁定，或者我只会遇到行级锁定？如果我有一个表锁，可以通过说''SELECT [field1]，[field2] FROM A with（nolock）''来避免它吗？
我'还会补充说我可以访问填充A的服务，并且可以在运行查询时将其关闭，这样我就不会丢失数据。
< blockquote>正如我所见，你不需要锁定任何东西。
如你所说，你可以停止服务dml。这样做是肯定的！
A）如果不使用第二个表，你不能改变数据类型吗？请参阅： http://sqlserverplanet.com/ddl/alter-table-alter-column [ ^ ]
B）如果需要进行一些数据转换，您可以在同一个表中添加一个新字段，并使用UPDATE来填充它;比删除原始字段;比重新命名新字段
C）你仍然可以使用你没有锁定的方法
We have to change the size of a nvarchar field in a table in our SQL Server 2005 db (table A), and because of the amount of data in the table this modification will cause some serious access problems. To remedy this, I''ve created a table B with the same schema, except for the larger nvarchar field. Once I copy data from A to B, I will delete A and rename B.
My question is about row versus table locks. If I do this:
INSERT INTO B ([field1],[field2])
SELECT [field1],[field2] FROM A
Will I have a table level lock on A while I fill B, or will I only experience row-level locks? If I have a table lock, can I avoid it by saying ''SELECT [field1],[field2] FROM A with (nolock)''?
I''ll also add that I have access to the service that fills A, and can shut it down while I run the query so that I don''t lose data.
As I see, you don''t need to lock anything.
As you stated, you can stop the service doing dml. Do it for sure!
A) Can''t you change datatype without using a second table? See: http://sqlserverplanet.com/ddl/alter-table-alter-column[^]
B) If some data transformation is needed, you could add a new field to the same table, and use UPDATE to fill it up; than delete original field; than rename new field
C) You can still use the approach drafted by you without lock
这篇关于从select插入时表与行锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
