|
Порядок блокировки таблиц не соответствует плану запроса
#40046398
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Москва
Сообщения: 9 956
|
|
msLex,
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
use tempdb;
set ansi_nulls, quoted_identifier, xact_abort, nocount on;
go
create table dbo.t1 (id int constraint PK_t1 primary key);
create table dbo.t2 (id int);
insert into dbo.t1 (id) values (1);
insert into dbo.t2 (id) values (2);
update statistics dbo.t1 with rowcount = 10000;
update statistics dbo.t2 with rowcount = 10000;
select 't1', %%lockres%%, id from dbo.t1;
select 't2', %%lockres%%, id from dbo.t2;
go
set statistics xml on;
dbcc traceon(1200, 3604, -1);
select
t.id
from
(
select id from dbo.t1 with (repeatableread)
union all
select id from dbo.t2 with (repeatableread)
) t (id)
order by
t.id
option
(merge join);
dbcc traceoff(1200, 3604, -1);
set statistics xml off;
alter table dbo.t1 drop constraint PK_t1;
select 't1', %%lockres%%, id from dbo.t1;
print '';
print '----------------------------------------------------------------------';
print '';
set statistics xml on;
dbcc traceon(1200, 3604, -1);
select
t.id
from
(
select id from dbo.t1 with (repeatableread)
union all
select id from dbo.t2 with (repeatableread)
) t (id)
order by
t.id
option
(merge join);
dbcc traceoff(1200, 3604, -1);
set statistics xml off;
go
drop table dbo.t1, dbo.t2;
go
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
Process 70 acquiring IS lock on OBJECT: 2:498816839:0 (class bit2000000 ref1) result: OK
Process 70 acquiring IS lock on OBJECT: 2:466816725:0 (class bit2000000 ref1) result: OK
Process 70 acquiring IS lock on PAGE: 2:4:224 (class bit2000000 ref0) result: OK
Process 70 acquiring S lock on RID: 2:4:224:0 (class bit2000000 ref0) result: OK
Process 70 acquiring IS lock on PAGE: 2:5:168 (class bit2000000 ref0) result: OK
Process 70 acquiring S lock on KEY: 2:4035225289354772480 (8194443284a0) (class bit2000000 ref1) result: OK
Process 70 releasing lock reference on KEY: 2:4035225289354772480 (8194443284a0)
Process 70 acquiring IS lock on OBJECT: 2:498816839:0 (class bit2000000 ref1) result: OK
Process 70 acquiring IS lock on OBJECT: 2:466816725:0 (class bit2000000 ref1) result: OK
Process 70 acquiring IS lock on PAGE: 2:5:168 (class bit2000000 ref0) result: OK
Process 70 acquiring S lock on RID: 2:5:168:0 (class bit2000000 ref0) result: OK
Process 70 acquiring IS lock on PAGE: 2:4:224 (class bit2000000 ref0) result: OK
Process 70 acquiring S lock on RID: 2:4:224:0 (class bit2000000 ref0) result: OK
|
|
|
