MSSQL??????????????????
???????????? ???????[ 2016/5/24 15:41:00 ] ????????????? ????
????????????????????MSSQL??????????????????????????????:
??????? ????????
?????????????????????????????????й????У???????????????????????????????????????????????????????????.???????????????????????????????????Щ???????????????????????.
???????? ????????????????????
?????????????????????????????????????????????????????????????????????????á??????????????????????????????????????????????????????????????? ????????????????????????????????????????????????μ????????????????????????????У???????????????????????????????????????????? ?????????????????????????????δ????????????????????????????????????? ??·????????????????????????????????????——?????????????????????{P0??P1??P2??···??Pn}?е?P0?????????P1?????????P1??????P2?????????……??Pn?????????P0???????
?????????????????????????????????????????????Щ?????????????????????????????????????????????
???????? ??δ???????
????1) ????
????????????S??
?????????????????????????????????????У????????????????и??£??????????????????????????????????????????????????????????????SQL SERVER?????????????????????????????????????????????????????????????????乲????????????
????2.?????(X)
????????????????????????????????????????????????????????????????????????κ?????????????????3??е??????????
????3.??????(U)
??????????????????????????????????????????????????????SQL SERVER????????????????????????????????????????????????????????
????????????????????????????????????????????????????????
?????????????????????????????????????????????????ú?????????л??????serialization gate???????????????????????????????С????и??????????????????????????????????????????????????????????—???????????????????????????????????????л??????????????????????????????????????????????????????????????????
????4. ??????(IX??IU??IS)
?????????????????????????????????????????Щ??????????????????
????????????????????????????????????????????????????????????ι?????????????????????????????????????????????????????????????????????????????????????????ù????????????????????????????????????????????????????
????5. ????????(Sch_s??Sch_m??BU)
????SQL SERVER??3??????????????????????????????????????????????
????6.?????(SIX??SIU??UIX)
???????????????SQL SERVER ????????????????????????????????????SQL SERVER 2008???3??????????????SIX??SIU??UIX.???г???????SIX??????????????????????????????S???????????????IX????????????SIX??
????7.????Χ??
????????Χ??????????л??????????????????Χ??????????????????????????Χ???????????????
????SQL SERVER ????
??????д
????????
???????
????S
????Shared
????????????????????????????????????
????X
????Exclusive
??????????????????????????????е?????
????U
????Update
??????????????????????????????????????????????????
????IS
????Intent shared
??????????????????????????????????????????????????????????
????IU
????Intent update
??????????????????????????????????????????????????????????
????IX
????Intent exclusive
?????????????????????????????????????????????????????????
????SIX
????Shared with intent exclusive
???????????????й????????????????????????????У??????????????
????SIU
????Shared with intent Update
???????????????й????????????????????????????У???????????????
????UIX
????Update with intent exclusive
???????????????и????????????????????????????У??????????????
????Sch-S
????Schema stability
???????????????????????????
????Sch-M
????Schema modification
???????????????????
????BU
????Bulk Update
?????????????????????????????????в???????????????????TABLOCK??
????????????
????2) ??????
????SQL SERVER ????????????е???????????????????????????????????????????????????????????SQL SERVER ??????????????????????????Χ??
???????sys.dm_tran_locks?????????????????????У?????????????????????????????????????sys.dm_tran_locks??????????????????????????????????????????????????????????????????????????????????????????????
????CREATE VIEW dblocks AS
????SELECT request_session_id AS spid??
????DB_NAME(resource_database_id) AS dbname??
????CASE WHEN resource_type='object'
????THEN OBJECT_NAME(resource_associated_entity_id)
????WHEN resource_associated_entity_id=0 THEN 'n/a'
????ELSE OBJECT_NAME(p.object_id) END AS entity_name??
????index_id??
????resource_type AS RESOURCE??
????resource_description AS DESCRIPTION??
????request_mode AS mode??
????request_status AS STATUS
????FROM sys.dm_tran_locks t LEFT JOIN sys.partitions p ON p.partition_id=t.resource_associated_entity_id
????WHERE resource_database_id=DB_ID()
????3) ??θ???????
??????????sql server profiler ????е???????????????????????????????
????4) ????????????
??????
???·???
??????????????????
2023/3/23 14:23:39???д?ò??????????
2023/3/22 16:17:39????????????????????Щ??
2022/6/14 16:14:27??????????????????????????
2021/10/18 15:37:44???????????????
2021/9/17 15:19:29???·???????·
2021/9/14 15:42:25?????????????
2021/5/28 17:25:47??????APP??????????
2021/5/8 17:01:11