Hi, I have thought about the following SQL statements:
INSERT INTO A(a1, a2)
SELECT b1, udf_SomeFunc(b1)
FROM B
Where udf_SomeFunc makes a select on table A. As I understand, first, a shared lock is set on A (I am talking just about table A now), then, after this lock is released, an exclusive lock is obtained to insert the data. The question is: is it possible, that another transaction will get the exclusive lock on table A, just before the current transaction takes its exclusive lok on A?
Food for thought
create table test(id int)
insert test values(1)
GO
Now in one window run this
begin tran
insert into test
select * from test with (holdlock, updlock)
waitfor delay '00:00:30'
commit
while that is running open another connection and do this
begin tran
insert into test
select * from test with (holdlock, updlock)
commit
as you can see the second insert doesn't happen until the first transaction is complete
now take out the locking hints and observer the difference
Your question is assuming that an X lock will be taken on table A, which may not be the case depending on the volume of data being inserted. If we follow your assumption that A is going to be X locked by the insert and that this statement is running at the default isolation of READ COMMITTED, then yes it is possible that another transaction could take a blocking lock prior to the insert.
It would probably be prior to the select statement actually reading the rows in B or A (via the function). The sequence would probably be an IS at table level on A and B (source), followed by an IX on A (target). IS at page and row level would then follow for A and B, followed by IX at page level for A at the page where the insert will occur.
The exact sequence of locking events can be viewed using a profiler trace and would probably be easier to understand than reading my textual description!
Various approaches could be taken to prevent that from happening... is this an academic question or can you include more detail as to where this is occurring?