SQL Server 2008中的事务问题
我正在使用 SQL Server 2008 Enterprise。并使用ADO.Net + C# + .Net 3.5 + ASP.Net作为客户端访问数据库。当我访问 SQL Server 2008 表时，我总是从 C# + ADO.Net 代码调用存储过程。
我的问题是，如果我的客户端 C# + ADO.Net 代码没有任何事务控制（我的意思是开始/结束事务），并且我在存储的 sql 中也没有任何事务控制（我的意思是开始/结束事务）程序代码。那么我的问题是，每个插入/删除/更新/选择语句将充当单个事务？这是正确的吗？例如，在下面的存储过程中，删除/插入/选择将充当3个单个事务？
create PROCEDURE [dbo].[FooProc]
(
@Param1 int
,@Param2 int
,@Param3 int
)
AS
DELETE FooTable WHERE Param1 = @Param1
INSERT INTO FooTable
(
Param1
,Param2
,Param3
)
VALUES
(
@Param1
,@Param2
,@Param3
)
DECLARE @ID bigint
SET @ID = ISNULL(@@Identity,-1)
IF @ID > 0
BEGIN
SELECT IdentityStr FROM FooTable WHERE ID = @ID
END
I am using SQL Server 2008 Enterprise. And using ADO.Net + C# + .Net 3.5 + ASP.Net as client to access database. When I access SQL Server 2008 tables, I always invoke stored procedure from my C# + ADO.Net code.
My question is, if I do not have any transaction control (I mean begin/end transaction) from my client C# + ADO.Net code, and I also do not have any transaction control (I mean begin/end transaction) in sql stored procedure code. Then my question is, each single Insert/Delete/Update/Select statement will act as a single transaction? Is that correct? For example, in the following store procedure, delete/insert/select will act as 3 single transactions?
create PROCEDURE [dbo].[FooProc]
(
@Param1 int
,@Param2 int
,@Param3 int
)
AS
DELETE FooTable WHERE Param1 = @Param1
INSERT INTO FooTable
(
Param1
,Param2
,Param3
)
VALUES
(
@Param1
,@Param2
,@Param3
)
DECLARE @ID bigint
SET @ID = ISNULL(@@Identity,-1)
IF @ID > 0
BEGIN
SELECT IdentityStr FROM FooTable WHERE ID = @ID
END
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
是的，如果没有显式的事务控制，每个 SQL 语句都会包装在自己的事务中。单个语句保证整体执行或整体失败。
单个语句将在当前事务隔离级别下运行：通常
读取已提交。因此它不会读取其他语句中未提交的更改，但可能会遭受不可重复读取或幻像值的影响。Yes, without explicit transaction control, each SQL statement will be wrapped in its own transaction. The single statement is guaranteed to be executed as a whole or fail as a whole.
The single statements will run under the current transaction isolation level: normally
read committed. So it won't read uncommitted changes from other statements, but it might suffer from nonrepeatable reads, or phantom values.如果您不处理事务，则每个语句将是独立的，并且可能会干扰同时运行存储过程的其他用户。
If you don't handle transactions then each statement will be independent and might interfere with other users running the stored procedure at the same time.