57,062
社区成员
发帖
与我相关
我的任务
分享mysql事务与锁
最近在研究mysql高性能书籍，有下面的问题没弄懂。
mysql在开启事务后（默认的级别），会对表上锁吗，如果上锁，在什么时候锁表，又在什么时候释放锁。
mysql在开启事务后（默认的级别），会对表上锁吗，如果上锁，在什么时候锁表，又在什么时候释放锁。
...全文
请发表友善的回复…
发表回复
吖君君 2013-07-29
- 打赏
- 举报
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <BOB>
-- Create date: <>
-- Description: <从虚表导入实表 研究生资料>
-- =============================================
ALTER PROCEDURE [dbo].[InsertFinishedPracticalTeachingWorkloads_FromVitual]
@Creator nvarchar(50)
AS
begin
declare @DelID int --需要在虚表中删除的数据ID值
declare @BackID int
declare @PracticeCategory int
declare @Definition nvarchar(50)
declare @StartTime datetime
declare @EndTime datetime
declare @Professional nvarchar(100)
declare @Number int
declare @Coeffcient float
declare @ScheduleClassHours float
declare @StandardClassHours float
declare @StaffID int
declare @Remark nvarchar(1000)--以上参数是可以由外部传入的参数
declare @StaffWorkloadID int
declare @WorkloadType int
declare @State int
declare @TableName nvarchar(80)
declare @WorkTaskID int
declare @StaffName nvarchar(100)
declare @DepartmentID int
set @WorkloadType =113
set @State=1
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
SET ANSI_WARNINGS OFF
declare @AllIsRightData int --所有虚表数据是否正确值
declare @IsRightData int --确认导入时执行数据检查
set @AllIsRightData=1
declare CheckData cursor for
select IsRight from FinishedPracticalTeachingWorkloads_Virtual where Creator=@Creator
open CheckData
fetch from CheckData into @IsRightData
while(@@fetch_status=0)
begin
if(@IsRightData=0)
BEGIN
set @AllIsRightData=0
RAISERROR('还存在未修改完的错误数据,请检查后重新导入!',16,1)
Break
END
fetch next from CheckData into @IsRightData
end
close CheckData
deallocate CheckData
declare @LoginStaffName nvarchar(20)
select @LoginStaffName=Contact.Name from Contact inner join Staff ON
Contact.ContactID=Staff.ContactID WHERE Staff.StaffID=@Creator
if(@AllIsRightData=1)
BEGIN
select @WorkTaskID=ID from WorkTasks
where ParentID is null and TaskStatus in (1,2)
declare SureInsertAll cursor for
select ID,PracticeCategory,Definition,StartTime,EndTime,Professional,Number,Coeffcient,ScheduleClassHours,StandardClassHours,StaffName,DepartmentID,StaffID
from FinishedPracticalTeachingWorkloads_Virtual where Creator=@Creator
set transaction isolation level serializable
begin tran
alter table StaffWorkLoads----------------------
disable trigger InsertSumTeachWorkloads----------
alter table FinishedPracticalTeachingWorkloads----------------------
disable trigger SumFinishedPracticalTeachingWorkloadsInsert----------
open SureInsertAll
fetch from SureInsertAll into @DelID,@PracticeCategory,@Definition,@StartTime,@EndTime,@Professional,@Number,@Coeffcient,@ScheduleClassHours,@StandardClassHours,@StaffName,@DepartmentID,@StaffID
while(@@fetch_status=0)
BEGIN
select @StaffID=StaffID from staff inner join contact
on staff.contactid=contact.contactid
where contact.name=@StaffName and contact.departmentid=@DepartmentID
if exists(
select ID from StaffWorkLoads
where WorkTaskID=@WorkTaskID and WorkloadType=@WorkloadType and StaffID=@StaffID
)
begin
select @StaffWorkloadID=ID from StaffWorkLoads
where WorkTaskID=@WorkTaskID and WorkloadType=@WorkloadType and StaffID=@StaffID
end
else
begin
insert into StaffWorkLoads(StaffID,WorkTaskID,WorkloadType,Workload,State,UsedFlag,Creator,CreateTime,Editor,EditTime,Remark)
values (@StaffID,@WorkTaskID,@WorkloadType,0,1,1,@Creator,getdate(),@Creator,getdate(),null)
set @StaffWorkloadID=IDENT_CURRENT('StaffWorkLoads')
IF( @@ERROR <> 0 )
GOTO Cleanup
end
insert into FinishedPracticalTeachingWorkloads
(
StaffWorkloadID,WorkloadType,PracticeCategory,Class,StartTime,EndTime,Professional,Number,Coeffcient,ScheduleClassHours,StandardClassHours,State,UsedFlag,Creator,CreateTime,Editor,EditTime,
Remark,DataOrigin
)
values
(
@StaffWorkloadID,@WorkloadType,@PracticeCategory,@Definition,@StartTime,@EndTime,@Professional,@Number,@Coeffcient,@ScheduleClassHours,@StandardClassHours,2,1,@Creator,getdate(),@Creator,getdate(),
null,1
)
set @BackID=IDENT_CURRENT('FinishedPracticalTeachingWorkloads')
set @TableName='FinishedPracticalTeachingWorkloads'
-- 更新工作流数据。
DECLARE @InstanceID uniqueidentifier
SET @InstanceID = NEWID()
INSERT INTO eIvy_WorkflowInstances
(ID, WorkflowName, EntityID, CurrentState)
VALUES
(@InstanceID, @TableName, @BackID, 2)
INSERT INTO eIvy_WorkflowInstanceTracings
(TracingID, InstanceID, Transit, Executer, ExecuteTime, Remark)
VALUES
(NEWID(), @InstanceID, 11,@LoginStaffName, GETDATE(), NULL)
IF( @@ERROR <> 0 )
GOTO Cleanup
/*insert into WorkloadAssign
(Entity,EntityID,StaffID,Workload,SelfRank,State,UsedFlag,Creator,CreateTime,Editor,EditTime,Remark)
values
(@TableName,@BackID,@StaffID,0,1,1,1,@Creator,getdate(),@Creator,getdate(),@Remark)*/
delete from FinishedPracticalTeachingWorkloads_Virtual where ID=@DelID
IF( @@ERROR <> 0 )
GOTO Cleanup
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
fetch next from SureInsertAll into @DelID,@PracticeCategory,@Definition,@StartTime,@EndTime,@Professional,@Number,@Coeffcient,@ScheduleClassHours,@StandardClassHours,@StaffName,@DepartmentID,@StaffID
END
close SureInsertAll
deallocate SureInsertAll
alter table FinishedPracticalTeachingWorkloads-----
enable trigger SumFinishedPracticalTeachingWorkloadsInsert-----
alter table StaffWorkLoads--------------------
enable trigger InsertSumTeachWorkloads----------------
commit tran
END
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN -1
end
这段代码为什么插入到虚表的时候是截断的插入的呀，不是连续插入的，导致不能一次性吧数据全部插入
lee1473336489 2013-07-02
- 打赏
- 举报
你说得是对的..但是我有个疑问
1。 重复读级别是如何解决 提交读级别的“不可重复读”这个问题的..
2。 版本控制是自动开启的，还是需要手动设置..
Regan-lin 2013-07-02
- 打赏
- 举报
麻烦你帮我看下，我这样理解对不：
提交读，是在修改数据结束后立马释放锁，所以会出现两次读取的内容不一致。
可重复读，是在事务结束后释放锁，所以不会出现不可重复读。
在同一个可重复读的事务中，比如先查询，后更新同一行数据，查询时会上共享锁，更新时共享锁变成排他锁。
但是别的事务修改时，这个共享锁是不会变排他锁的。
[/quote]
重复读会出现两次读取的数据不一样！
你最后那个应该是查询是共享锁，更新时就会采用2段锁协议，来保证其正确执行！这是我的理解！[/quote]
百度了下两段锁协议，简单点说就是上锁解锁吧？ 重复度通过版本并发控制解决了两次读取数据不一样的情况[/quote]
为什么不用两段锁来解决呢，一定要用并发来解决呢?两段锁不是更好理解更好操作?[/quote]
是这样的，上锁容易死锁需要rollback，版本并发控制，是不需要上锁的，优点就不言而喻了啊。你查查hibernate的乐观锁。[/quote]其实你只要注意用好共享和独享锁，不会出现你说的容易死锁的情况的！
yumenfeiyu945 2013-07-02
- 打赏
- 举报
不是事务开启的时候加锁，是当你进行了写操作，或者对于select加上了加锁语句后才会加锁，在Serializable级别下select也会加上share lock
lee1473336489 2013-07-02
- 打赏
- 举报
麻烦你帮我看下，我这样理解对不：
提交读，是在修改数据结束后立马释放锁，所以会出现两次读取的内容不一致。
可重复读，是在事务结束后释放锁，所以不会出现不可重复读。
在同一个可重复读的事务中，比如先查询，后更新同一行数据，查询时会上共享锁，更新时共享锁变成排他锁。
但是别的事务修改时，这个共享锁是不会变排他锁的。
[/quote]
这和两阶段段锁也没有关系
mysql/innodb的提交读和可重复读的实现不是通过锁来实现的(其他数据库MSSQL可能是).
[/quote]
那为什么我使用可重复读出现了死锁呢？ 你说不是锁，那是什么实现的?提交读加的是瞬时共享锁，所以容易出现两次读结果不一样。
trainee 2013-07-02
- 打赏
- 举报
麻烦你帮我看下，我这样理解对不：
提交读，是在修改数据结束后立马释放锁，所以会出现两次读取的内容不一致。
可重复读，是在事务结束后释放锁，所以不会出现不可重复读。
在同一个可重复读的事务中，比如先查询，后更新同一行数据，查询时会上共享锁，更新时共享锁变成排他锁。
但是别的事务修改时，这个共享锁是不会变排他锁的。
[/quote]
这和两阶段段锁也没有关系
mysql/innodb的提交读和可重复读的实现不是通过锁来实现的(其他数据库MSSQL可能是).
lee1473336489 2013-07-02
- 打赏
- 举报
麻烦你帮我看下，我这样理解对不：
提交读，是在修改数据结束后立马释放锁，所以会出现两次读取的内容不一致。
可重复读，是在事务结束后释放锁，所以不会出现不可重复读。
在同一个可重复读的事务中，比如先查询，后更新同一行数据，查询时会上共享锁，更新时共享锁变成排他锁。
但是别的事务修改时，这个共享锁是不会变排他锁的。
[/quote]
重复读会出现两次读取的数据不一样！
你最后那个应该是查询是共享锁，更新时就会采用2段锁协议，来保证其正确执行！这是我的理解！[/quote]
百度了下两段锁协议，简单点说就是上锁解锁吧？ 重复度通过版本并发控制解决了两次读取数据不一样的情况[/quote]
为什么不用两段锁来解决呢，一定要用并发来解决呢?两段锁不是更好理解更好操作?[/quote]
是这样的，上锁容易死锁需要rollback，版本并发控制，是不需要上锁的，优点就不言而喻了啊。你查查hibernate的乐观锁。
Regan-lin 2013-07-02
- 打赏
- 举报
麻烦你帮我看下，我这样理解对不：
提交读，是在修改数据结束后立马释放锁，所以会出现两次读取的内容不一致。
可重复读，是在事务结束后释放锁，所以不会出现不可重复读。
在同一个可重复读的事务中，比如先查询，后更新同一行数据，查询时会上共享锁，更新时共享锁变成排他锁。
但是别的事务修改时，这个共享锁是不会变排他锁的。
[/quote]
重复读会出现两次读取的数据不一样！
你最后那个应该是查询是共享锁，更新时就会采用2段锁协议，来保证其正确执行！这是我的理解！[/quote]
百度了下两段锁协议，简单点说就是上锁解锁吧？ 重复度通过版本并发控制解决了两次读取数据不一样的情况[/quote]
为什么不用两段锁来解决呢，一定要用并发来解决呢?两段锁不是更好理解更好操作?
lee1473336489 2013-07-02
- 打赏
- 举报
麻烦你帮我看下，我这样理解对不：
提交读，是在修改数据结束后立马释放锁，所以会出现两次读取的内容不一致。
可重复读，是在事务结束后释放锁，所以不会出现不可重复读。
在同一个可重复读的事务中，比如先查询，后更新同一行数据，查询时会上共享锁，更新时共享锁变成排他锁。
但是别的事务修改时，这个共享锁是不会变排他锁的。
[/quote]
重复读会出现两次读取的数据不一样！
你最后那个应该是查询是共享锁，更新时就会采用2段锁协议，来保证其正确执行！这是我的理解！[/quote]
百度了下两段锁协议，简单点说就是上锁解锁吧？ 重复度通过版本并发控制解决了两次读取数据不一样的情况
lee1473336489 2013-07-02
- 打赏
- 举报
麻烦你帮我看下，我这样理解对不：
提交读，是在修改数据结束后立马释放锁，所以会出现两次读取的内容不一致。
可重复读，是在事务结束后释放锁，所以不会出现不可重复读。
在同一个可重复读的事务中，比如先查询，后更新同一行数据，查询时会上共享锁，更新时共享锁变成排他锁。
但是别的事务修改时，这个共享锁是不会变排他锁的。
[/quote]
重复读会出现两次读取的数据不一样！
你最后那个应该是查询是共享锁，更新时就会采用2段锁协议，来保证其正确执行！这是我的理解！[/quote]
什么叫做两段锁协议？
lee1473336489 2013-07-02
- 打赏
- 举报
非常感谢..
trainee 2013-07-02
- 打赏
- 举报
关于MVCC, 前人的研究结果
http://bbs.chinaunix.net/forum.php?mod=viewthread&tid=1945994
trainee 2013-07-02
- 打赏
- 举报
mvcc多版本控制是myisam/innodb实现事务的一个内部方法,是自动开启.
原理是每个事务开启,会产生各自的数据快照,以达到事务隔离的目的.
至于是如何实现的,深入研究要看源码.
lee1473336489 2013-07-01
- 打赏
- 举报
你说的基础是什么..我玩数据库都一年多的，基本的语句都会吧。
lee1473336489 2013-07-01
- 打赏
- 举报
麻烦你帮我看下，我这样理解对不：
提交读，是在修改数据结束后立马释放锁，所以会出现两次读取的内容不一致。
可重复读，是在事务结束后释放锁，所以不会出现不可重复读。
在同一个可重复读的事务中，比如先查询，后更新同一行数据，查询时会上共享锁，更新时共享锁变成排他锁。
但是别的事务修改时，这个共享锁是不会变排他锁的。
rucypli 2013-07-01
- 打赏
- 举报
另外先被研究mysql高性能的了 来点数据库基础的东西吧先 呵呵
Rotel-刘志东 2013-07-01
- 打赏
- 举报
不同的存储引擎锁的类型不一样的。innodb是行级锁，myISAM是表锁。
rucypli 2013-07-01
- 打赏
- 举报
innodb支持到行级别锁
myisam只有表级锁
一般事务完成即可释放锁
lee1473336489 2013-07-01
- 打赏
- 举报
那会自动使用mvcc吗，就是每次查询的时候比较下版本号
ACMAIN_CHM 2013-07-01
- 打赏
- 举报
innodb 会尽可能使用行锁。比如有索引时。
加载更多回复（3）
