将两列归拢连接成一列,须要在意的是列的格式必得是NVARCHA路虎极光可能VARCHARubicon类型

1 背景

 

1 SELECT ','+Convert(NVARCHAR(30), call_uuid, 0 ) +','+agent_code 
2 as PerDateFROM cc_log_call_eco where agent_code='1001'

1.1 报告急察方意况

近年整合治理笔记,筹算全体搬迁到EVEQX56NOTE。收拾到锁那生龙活虎部分,里边刚好有个和谐记录下来的案例,重新收十分享下给大家。图片 1

某日晚上,收到报告急察方短信,DB死锁卓殊,单分钟死锁1二十一个。

死锁的xml文件如下:

 1 <deadlock-list>
 2 <deadlock victim="process810b00cf8">
 3 <process-list>
 4 <process id="process810b00cf8" taskpriority="0" logused="0" waitresource="RID: 13:1:1541136:62" waittime="7682" ownerId="3396587959" transactionname="UPDATE" lasttranstarted="2016-01-08T12:03:51.067" XDES="0xa99746d08" lockMode="U" schedulerid="41" kpid="17308" status="suspended" spid="108" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-01-08T12:03:51.067" lastbatchcompleted="2016-01-08T12:03:51.067" lastattention="1900-01-01T00:00:00.067" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="test-server" hostpid="1433" loginname="xinysu" isolationlevel="read committed (2)" xactid="3396587959" currentdb="13" lockTimeout="4294967295" clientoption1="671098976" clientoption2="390200">
 5 <executionStack>
 6 <frame procname="adhoc" line="7" stmtstart="214" stmtend="484" sqlhandle="0x020000003acf4f010561e479685209fb09a7fd15239977c60000000000000000000000000000000000000000">
 7 UPDATE FinanceReceiptNoRule SET NowSeqValue=@ReturnNum,ISRUNNING='0',LastWriteTime=GETDATE() WHERE IsRunning='1' AND SeqCode=@SeqCode </frame>
 8 </executionStack>
 9 <inputbuf>
10 declare @SeqCode varchar(60)
11 declare @ReturnNum bigint
12 set @SeqCode='CGJS20160106'
13 while(1=1)
14 begin
15 UPDATE FinanceReceiptNoRule SET NowSeqValue=@ReturnNum,ISRUNNING='0',LastWriteTime=GETDATE() WHERE IsRunning='1' AND SeqCode=@SeqCode
16 end </inputbuf>
17 </process>
18 <process id="process18fd5d8cf8" taskpriority="0" logused="248" waitresource="KEY: 13:72057594040090624 (b3ade7c5980c)" waittime="4" ownerId="3396522828" transactionname="user_transaction" lasttranstarted="2016-01-08T12:03:05.310" XDES="0x18c1db63a8" lockMode="U" schedulerid="57" kpid="16448" status="suspended" spid="161" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-01-08T12:03:58.737" lastbatchcompleted="2016-01-08T12:03:33.847" lastattention="2016-01-08T12:03:33.850" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="test-server" hostpid="1433" loginname="xinysu" isolationlevel="read committed (2)" xactid="3396522828" currentdb="13" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
19 <executionStack>
20 <frame procname="adhoc" line="6" stmtstart="210" stmtend="400" sqlhandle="0x020000001b4f23368af7bba99098c10dec46585804f1b4ce0000000000000000000000000000000000000000">
21 Update dbo.FinanceReceiptNoRule Set [IsRunning]='1' where SeqCode=@SeqCode and IsRunning='0' </frame>
22 </executionStack>
23 <inputbuf>
24 declare @SeqCode varchar(60)
25 declare @ReturnNum bigint
26 set @SeqCode='CGJS20160106'
27 while(1=1)
28 begin
29 Update dbo.FinanceReceiptNoRule Set [IsRunning]='1' where SeqCode=@SeqCode and IsRunning='0' 
30 end
31 </inputbuf>
32 </process>
33 </process-list>
34 <resource-list>
35 <ridlock fileid="1" pageid="1541136" dbid="13" objectname="fin_test.dbo.FinanceReceiptNoRule" id="lock51e8a3980" mode="X" associatedObjectId="72057594040025088">
36 <owner-list>
37 <owner id="process18fd5d8cf8" mode="X" />
38 </owner-list>
39 <waiter-list>
40 <waiter id="process810b00cf8" mode="U" requestType="wait" />
41 </waiter-list>
42 </ridlock>
43 <keylock hobtid="72057594040090624" dbid="13" objectname="fin_test.dbo.FinanceReceiptNoRule" indexname="PK_FINANCERECEIPTNORULE" id="lock7b2c6bc80" mode="U" associatedObjectId="72057594040090624">
44 <owner-list>
45 <owner id="process810b00cf8" mode="U" />
46 </owner-list>
47 <waiter-list>
48 <waiter id="process18fd5d8cf8" mode="U" requestType="wait" />
49 </waiter-list>
50 </keylock>
51 </resource-list>
52 </deadlock>
53 </deadlock-list>

报表构造跟模拟数据如下:

 1 --涉及表格:
 2 CREATE TABLE [dbo].[FinanceReceiptNoRule](
 3 [SeqCode] [varchar](60) NOT NULL,
 4 [NowSeqValue] [bigint] NULL,
 5 [SeqDate] [varchar](14) NOT NULL,
 6 [IsRunning] [varchar](1) NULL,
 7 [LastWriteTime] [datetime] NULL,
 8 [Prefix] [varchar](4) NULL
 9 ) ON [PRIMARY]
10 GO
11 --数据模拟
12 INSERT [dbo].[FinanceReceiptNoRule] ([SeqCode], [NowSeqValue], [SeqDate], [IsRunning], [LastWriteTime], [Prefix]) VALUES (N'TEST20150108', 1469, N'20150108', N'0', CAST(N'2015-01-08 05:05:49.163' AS DateTime), N'TEST')
13 GO
14 INSERT [dbo].[FinanceReceiptNoRule] ([SeqCode], [NowSeqValue], [SeqDate], [IsRunning], [LastWriteTime], [Prefix]) VALUES (N'TEST20150109', 1377, N'20150109', N'0', CAST(N'2015-01-09 04:50:26.610' AS DateTime), N'TEST')
15 GO
16  
17 ALTER TABLE [dbo].[FinanceReceiptNoRule] ADD CONSTRAINT [pk_FinanceReceiptNoRule] PRIMARY KEY NONCLUSTERED 
18 (
19 [SeqCode] ASC
20 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
21 GO

以此是网络朋友++C++在群里问的一个有关MySQL的难题,本篇文章实验测量试验情状为MySQL
5.6.20,事务隔开等第为REPEATABLE-READ
,在演示难题前,大家先思虑测量检验情形。希图二个测验表test以致五个仓库储存进度循环往test表里面插入记录。

 

1.2 怎么着监督

抓获死锁有各种格局能够捕获,这里介绍2种:SQL SE宝马X3VER
Profiler工具跟Extended
Events。Profiler相对比较耗财富,不过出于只监察和控制死锁那生机勃勃项,所以品质影响不是极大,其可视化分界面比较简单上手;Extended
伊夫nts开销能源比较少,实时记录到尾数第二个死锁,同不常间供给SQL语句来剖判查询记录文件。

怎么使用 Profiler监察和控制?
打开 SSMS,点击<工具>,选择 <SQL Server Profiler>,如下图。

图片 2

报到到要求监控的DB实例,填写相应的追踪属性,首先是<常规>页面,如下图。这里注意2个地方,第意气风发,选用<TSQL-Locks>模板,这么些模板即能够用来监督死锁,也能够拿来阅览锁申请与自由景况,非常详细,有事没事能够多拿来看SELECT UPDATE
DELETE等语句对锁的提请及释放情状;第二,监察和控制结果存款和储蓄,提议能够存放到有个别表格中去,方便依期解析与总计。

图片 3

随后填写<事件选拔>项,只要求接收 <deadlock graph>
Events,其余都无需打勾,最终点击运维就可以起来监控了。

 图片 4

能够用一个千古常用的例证来检查是或不是监察和控制正常,开3个查询窗口,依照以下依次实行则会产生产资料源占用及报名互斥招致死锁,试行完第5步,等待1-3s则发出死锁。脚本提供如下:

图片 5图片 6

 1 --session 1
 2 CREATE TABLE Test_DL(
 3 id int not null primary key ,
 4 name varchar(100));
 5 
 6 INSERT INTO Test_DL(id,name) select 1,'a';
 7 INSERT INTO Test_DL(id,name) select 2,'b';
 8 
 9 --session2 2 2 2 2 2 2 2 2 2 
10 BEGIN TRANSACTION
11 UPDATE Test_DL SET Name='a-test' WHERE ID=1
12 
13 --session3 3 3 3 3 3 3 3 3 3 
14 BEGIN TRANSACTION
15 UPDATE Test_DL SET Name='b-test' WHERE ID=2
16 
17 --session2 2 2 2 2 2 2 2 2 2 
18  SELECT * FROM Test_DL WHERE ID=2
19 
20 --session3 3 3 3 3 3 3 3 3 3
21  SELECT * FROM Test_DL WHERE ID=1

效仿死锁SQL

图片 7

监督检查到的死锁分界面如下:

图片 8

何以使用Extended 伊夫nts监察和控制?

创立扩充事件监察和控制的台本如下:(扩张事件十分赞,二〇一二版帮衬可视化操作,感兴趣的能够上
MSDN驾驭:

1 CREATE EVENT SESSION [DeadLock] ON SERVER 
2 ADD EVENT sqlserver.xml_deadlock_report 
3 ADD TARGET package0.event_file(SET filename=N'F:\events\deadlock\deadlock.xel',max_file_size=(20)),
4 ADD TARGET package0.ring_buffer(SET max_events_limit=(100),max_memory=(10240),occurrence_number=(50))
5 WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
6 GO

查询SQL如下,这里要求注意:查询是基于buffer依然基于filer剖判,日常buffer存款和储蓄的个数都以个别的,例如上文大家只分红了4M存款和储蓄,file剖析则是欧洲经济共同体的,但是要看保留的文件个数。这里我们给出buffer的询问SQL如下,file的询问我们感兴趣的能够入手写下。

DECLARE @deadlock_xml XML
SELECT @deadlock_xml=(
                       SELECT 
                              ( 
                                SELECT
                                      CONVERT(XML, target_data)
                                FROM sys.dm_xe_session_targets st
                                JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
                                WHERE s.name = 'deadlock' AND st.target_name = 'ring_buffer'
                              ) AS [x]
                       FOR XML PATH('') , TYPE
                      )

SELECT 
dateadd(hour,+6,tb.col.value('@timestamp[1]','varchar(max)')) TimePoint,
tb.col.value('(data/value/deadlock/process-list/process/executionStack/frame)[1]','VARCHAR(MAX)') statement_parameter_k,
tb.col.value('(data/value/deadlock/process-list/process/executionStack/frame)[2]','VARCHAR(MAX)') statement_k,
tb.col.value('(data/value/deadlock/process-list/process/executionStack/frame)[3]','VARCHAR(MAX)') statement_parameter,
tb.col.value('(data/value/deadlock/process-list/process/executionStack/frame)[4]','VARCHAR(MAX)') [statement],
tb.col.value('(data/value/deadlock/process-list/process/@waitresource)[1]','VARCHAR(MAX)') waitresource_k,
tb.col.value('(data/value/deadlock/process-list/process/@waitresource)[2]','VARCHAR(MAX)') waitresource,
tb.col.value('(data/value/deadlock/process-list/process/@isolationlevel)[1]','VARCHAR(MAX)') isolationlevel_k,
tb.col.value('(data/value/deadlock/process-list/process/@isolationlevel)[2]','VARCHAR(MAX)') isolationlevel,
tb.col.value('(data/value/deadlock/process-list/process/@waittime)[1]','VARCHAR(MAX)') waittime_k,
tb.col.value('(data/value/deadlock/process-list/process/@waittime)[2]','VARCHAR(MAX)') waittime,
tb.col.value('(data/value/deadlock/process-list/process/@clientapp)[1]','VARCHAR(MAX)') clientapp_k,
tb.col.value('(data/value/deadlock/process-list/process/@clientapp)[2]','VARCHAR(MAX)') clientapp,
tb.col.value('(data/value/deadlock/process-list/process/@hostname)[1]','VARCHAR(MAX)') hostname_k,
tb.col.value('(data/value/deadlock/process-list/process/@hostname)[2]','VARCHAR(MAX)') hostname
FROM @deadlock_xml.nodes('//event') as tb(col)

本条SQL能够查询的出特别详细的能源争夺意况,如若想要有效的运用扩大事件,提议大家详细查看下官方网址的xml语法(SQL
SEQashqaiVECR-V对xml的支撑也是棒棒哒,期望二零一四版中的json援助)

图片 9

是还是不是很清晰,如数家珍,有了这几个就足以去剖判拉!

 

  PerDate
1 ,980408102500000,1001
2 ,980414313125000,1001
3 ,980415770937500,1001
4 ,980416111875000,1001

2 分析

依据xml文件内容依然扩充事件的监察和控制内容,都能够整理为以下消息(开始的非凡死锁剖析):

图片 10

查看事务1及事务2的执行布置如下:

图片 11

 

 结合表格及实施安顿,能够大致推测死锁过程:

会话1:

  • 依附主键SeqCode查找到键值所在的
    索引页 Index_Page,找到该页下边的keyhashvalue 键值行
    Index_key,对Index_Page持有IU锁,对Index_key持有U锁;
  • 出于该表是堆表,bookmark lookup是透过
    RID查找 ,即经过行标志符查找,找到奇骏ID所对应的行数据所在的
    数据页 
    Data_Page,然后在该页面上找到安德拉ID指向槽号上的行数据,对该行数据持有U锁;
  • 其有时候,已经查找到了亟待更新的行数据,能够把数据页
    Data_Page上的IU锁 晋级为IX锁,奥迪Q7ID指向的行数据
    从U锁晋级为X锁,升级实现后,释放索引页跟键值行下边包车型客车IU锁及U锁。
  • 则此时,会话1 持有 Data_Page
    上的IX锁、RID行上的 X锁.

那一个历程中,适逢其时会话2张开如此的锁申请:

  • 寻觅事务第22中学兼有锁财富图片 12是哪些索引,可以按照sys.partitions
    能够查见到72057594038910976是主键pk_FinanceReceiptNoRule,主键列是:SeqCode。
  • 遵执照主人键SeqCode查找到键值所在的
    索引页 Index_Page,找到该页下边包车型客车
    键值行
    Index_key,对Index_Page持有IU锁,对Index_key持有U锁;
  • 鉴于该表是堆表,bookmark lookup是经过
    RID查找 ,即通过行标记符查找,找到奥迪Q5ID所对应的行数据所在的
    数据页 
    Data_Page,然后在该页面上找到LANDID指向槽号上的行数据,准备该行数据持有U锁,可是发现安德拉ID行上被会话1持有了X锁,导致其申请
    U锁 Timeout。
  • 则此时 会话2 持有
    Index_Page上的IU锁、Index_key上的U锁、Data_Page上的IU锁,请求
    RID行的 U锁。

风流罗曼蒂克旦那时候,会话1中又进行了一遍update操作(同三个作业中):

  • 依靠主键SeqCode查找到键值所在的 索引页
    Index_Page,找到该页上面包车型大巴 键值行
    Index_key,对Index_Page持有IU锁,准备对Index_key持有U锁,不过开掘Index_key被会话2持有了U锁。

那就是说这时死锁就生出了(详见下图卡塔尔(قطر‎:

  • 会话1 持有 Data_Page
    上的IX锁、RID行上的 X锁,申请 Index_key
    的U锁(等待会话2释放)
  • 会话2 持有
    Index_Page上的IU锁、Index_key上的U锁、Data_Page上的IU锁,必要景逸SUVID行的 U锁(等待会话1刑释)

图片 13

CREATE TABLE test

(

  `id` int(11) primary key not null,

  `name` char(255) 

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

 

 

delimiter &&

drop procedure if exists prc_insert;

 

create procedure prc_insert(in  cnt int)

begin

declare i int;

set i=1;

while i < cnt do

    insert into test(id, name) select i,  CONCAT('name',i) from dual;

    

    set i = i+1;

 

end while;

end &&

 

delimiter ;

3 解决

想艺术除去TucsonID查找,直接index就找到数据,就不会发生那几个死锁,也正是,在主键上边重新确立聚焦索引,放任原先的非集中索引主键。因为如此清除了奥迪Q3ID的U锁申请与富有,直接是保证X锁
直至事务停止,同不时候能够直接依照主键来纠正键值所在的数据页,减弱的奥迪Q3ID查询行的时间。

改进后的实行陈设如下:

图片 14

其锁申请假释的流水生产线如下(详见截图卡塔尔:

  • 依附主键SeqCode查找到键值所在的
    索引页 Index_Page,找到该页上边的keyhashvalue 键值行
    Index_key,对Index_Page持有IU锁,对Index_key持有U锁;
  • 出于该表已然是聚焦索引表,主键所在的页上包蕴行数据,则足以直接 对Index_Page持有IU锁升级为IX锁,对Index_key持有U锁进级为X锁,制止了WranglerID各种找行数据的锁申请

图片 15

 

 

在线程ID为14的对话中,开启事务,然后实施查询test的SQL语句

 

mysql> select connection_id() from dual;

+-----------------+

| connection_id() |

+-----------------+

|              14 |

+-----------------+

1 row in set (0.00 sec)

 

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from test;

 

 

然后在线程ID为12的对话中,循环往表test里面插入1000000记录

 

mysql> select connection_id() from dual;

+-----------------+

| connection_id() |

+-----------------+

|              12 |

+-----------------+

1 row in set (0.00 sec)

 

mysql> call prc_insert(1000000);

Query OK, 1 row affected (8 min 32.11 sec)

 

 

在实行循环插入的近年来里(SQL试行要求几分钟时间),大家在线程ID
为14的对话中频频施行select * from
test那么些SQL语句,你会开采该SQL的实行时间变长。那么引起SQL语句实践时间变长的案由是何等啊?
如何分解得通吗?

 

 

图片 16

 

刚开首钻探的时候,以为MySQL会像ORACLE那样会在UNDO的回滚段中生出大批量UNDO记录,最终导致SQL语句会像ORACLE那样产生额外的黄金时代致性读,发生额外的IO,进而诱致试行时间变长。
前面测验开采,其实对于MySQL来说,INSERT操作在事情提交前只对当前业务可以知道,由此发生的Undo日志可以在作业提交后一贯删除,而这里使用是电动提交格局。用“MySQL手艺内部原因:InnoDB存款和储蓄引擎”里面提供的剧本py_innodb_page_info.py测量检验注脚。也是的确那样(UNDO日志的高低变化超级小,时而拉长,时而变小)。其实MySQL里面多版本出现调节(MVCC)的贯彻机制跟Oracle还是差别的。不可能生搬硬套Oracle下的那套理论。

 

 

[root@DB-Server kerry]# python py_innodb_page_info.py /data/mysql/ibdata1

Total number of page: 4864:

Insert Buffer Free List: 32

Insert Buffer Bitmap: 1

System Page: 130

Transaction system Page: 1

Freshly Allocated Page: 1326

Undo Log Page: 3224

File Segment inode: 6

B-tree Node: 142

File Space Header: 2

[root@DB-Server kerry]# python py_innodb_page_info.py /data/mysql/ibdata1

Total number of page: 4864:

Insert Buffer Free List: 32

Insert Buffer Bitmap: 1

System Page: 130

Transaction system Page: 1

Freshly Allocated Page: 1326

Undo Log Page: 3223

File Segment inode: 6

B-tree Node: 143

File Space Header: 2

[root@DB-Server kerry]# python py_innodb_page_info.py /data/mysql/ibdata1

Total number of page: 4864:

Insert Buffer Free List: 32

Insert Buffer Bitmap: 1

System Page: 130

Transaction system Page: 1

Freshly Allocated Page: 1326

Undo Log Page: 3213

File Segment inode: 5

B-tree Node: 154

File Space Header: 2

[root@DB-Server kerry]# python py_innodb_page_info.py /data/mysql/ibdata1

Total number of page: 4864:

Insert Buffer Free List: 32

Insert Buffer Bitmap: 1

System Page: 130

Transaction system Page: 1

Freshly Allocated Page: 1326

Undo Log Page: 3205

File Segment inode: 5

B-tree Node: 162

File Space Header: 2

[root@DB-Server kerry]# python py_innodb_page_info.py /data/mysql/ibdata1

Total number of page: 4864:

Insert Buffer Free List: 32

Insert Buffer Bitmap: 1

System Page: 130

Transaction system Page: 2

Freshly Allocated Page: 1326

Undo Log Page: 3240

File Segment inode: 5

B-tree Node: 127

File Space Header: 1

 

实际上InnoDB的多版本现身调节(MVCC),“高品质MySQL”这本书中有那般意气风发段描述:

 

 

InnoDB的MVCC,是透过每行记录前面保存的八个暗藏的列来达成的。
那多个列贰个保存了行的创办时间,一个保存行的过期时间(或删除时间),当然存款和储蓄的实际不是实际上的日子值,而是系统版本号(System
version
number),每从前一个新的事情,系统版本号都会自行递增,事务开端每天的种类版本号会作为职业的本子号,用来和查询到的每行记录的版本号举办相比较。下边看一下在REPEATABLE
READ隔开分离等第下, MVCC是何等具体操作的。

 

SELECT

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注