|
Hello everyone,
Two basic questions about transactions,
1. For single SQL insert/update/delete statement, are there any needs or benefits to wrap into a transaction?
2. I wrote T-SQL like this,
BEGIN TRY
Begin Transaction
...
commit
END TRY
BEGIN CATCH
rollback
...
END CATCH
my question is, is it a must to call commit explicitly just before end try statement? Any means to commit transaction automatically after end try statement?
regards,
George
|
|
|
|
|
George_George wrote: For single SQL insert/update/delete statement, are there any needs or benefits to wrap into a transaction
It's always inside a transaction. Either implicit or explicit. If you don't want to be able to rollback the operation then it wouldn't matter, if you have logical error handling etc, then explicit transaction would be needed.
George_George wrote: is it a must to call commit explicitly just before end try statement? Any means to commit transaction automatically after end try statement
Basically commit point canbe anywhere, even at client side. However if you explicitely start a transaction, you have to explicitely end it. If you don't execute commit, database will roll back the transaction at the end of the session (unless other transaction handling is occurring after this transaction).
|
|
|
|
|
Thanks Mika,
1.
"If you don't want to be able to rollback the operation then it wouldn't matter, if you have logical error handling etc, then explicit transaction would be needed." -- I am confused. Could you show me a sample about the benefit please? My detailed confusion is even if I do not start transaction explicitly, I could still catch the error and handles it. 
2.
"Basically commit point canbe anywhere, even at client side." -- what do you mean client? No matther what kinds of API you are using or even using Management Studio directly, I think we are all "client" of database engine.
regards,
George
|
|
|
|
|
George_George wrote: if I do not start transaction explicitly, I could still catch the error and handles it
Yes you can if the error is 'technical' and SQL Server sees it as an error. But what happens if the error is logical. Consider following imaginary scenario.
- insert into a table
- check that some business rule apply
- if business rule is violated, undo the insert
The previous is very simple and it wouldn't be a problem to just change the order of the operations. However, this is not always the case.
Since there's typically no harm in using explicit transactions, personally I've decided to use them always.
George_George wrote: what do you mean client? No matther what kinds of API you are using or even using Management Studio directly, I think we are all "client" of database engine.
I meant a scenario where your try catch is inside a stored procedure and a client application (whatever it is) calls the procedure. Ending point can be at the calling side so the procedure is actually just a part of a bigger transaction scope.
|
|
|
|
|
Thanks Mika!
"I meant a scenario where your try catch is inside a stored procedure and a client application (whatever it is) calls the procedure. Ending point can be at the calling side so the procedure is actually just a part of a bigger transaction scope." -- so in this scenario, client code (e.g. ADO.Net) starts the transaction, but what is the practice of writing store procedure code in this way? Do we also need try-catch and begin transaction/rollback/commit code in store procedure?
BTW: I have this confusion because I as a store procedure developer -- not sure whther client will start another transaction or not. So, how could I write code in store procedure which could handle both client starts transaction or store procedure self-start transaction?
regards,
George
|
|
|
|
|
How the transaction scope is defined is a matter of opinion. I think most people use the logic that stored procedure starts and ends the transaction. However, there are downsides especially if rollback is executed because in that case the whole transaction is rolled back, even if it's started outside the stored procedure.
So this means that you actually cannot decide alone what transaction scope will be used. The application may be affected by your decisions. You could use @@TRANCOUNT to investigate if transaction is started outside the procedure and in the logic of your stored procedure you would execute rollback based on the initial transaction count.
There's a very good article you should read: http://www.codeproject.com/KB/database/sqlservertransactions.aspx[^]
|
|
|
|
|
George_George wrote: 1. For single SQL insert/update/delete statement, are there any needs or benefits to wrap into a transaction?
No, there are no benefits in this case.
George_George wrote: Any means to commit transaction automatically
This tells you all about it MSDN[^]
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thanks Ashfield!
Two more comments/questions after learning,
1.
I want to confirm with you that if we call begin transaction, do you mean the only way to commit transaction is to explicit call commit?
2.
If I do not use begin transaction explicitly using begin transaction statement (the default behavior is to auto-commit for each successful statement), no other configurations to disable auto-commit for successful each statement -- means we could have chance to commit later in a batch manually explicitly?
regards,
George
|
|
|
|
|
George_George wrote: I want to confirm with you that if we call begin transaction, do you mean the only way to commit transaction is to explicit call commit?
Correct
George_George wrote: means we could have chance to commit later in a batch manually explicitly?
No - the default autocommit will have happened
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thanks Bob,
Sorry one more question, there are 3 modes, autocommit, explicit and implicit. How to disable/enable implicit mode? 
regards,
George
|
|
|
|
|
I don't think you can, but you will need to check the documentation
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I am using the following code to Add up the difference of two time fields but It's not showing the correct record when the hours part increases above 24 means It shows the correct addition only if It's between 24 hours. For example
WaitingTimeTotal (hh:mm:ss)
----------------
03:18:46
03:25:06
________
06:43:52 which is correct but
WaitingTimeTotal (hh:mm:ss)
----------------
03:18:46
03:25:06
16:23:46
07:24:20
________
wrong answer!!!
The code i am using is
convert(varchar,convert(DateTime, SUM(convert(real, ConfTime - EnterTime))),108)WaitingTimeTotal
Can you please guide me. Thanks in advance !
AliAmjad(MCP)
First make it Run THEN make it Run Fast!
modified on Thursday, January 15, 2009 1:28 AM
|
|
|
|
|
If you want to have total hour amount, this could be one way.
I used inline views to both generate a test data (inner one) and to ease the summary (the outer one).
select convert(varchar(50), datepart(hour, datesum) + (datepart(day, datesum) - 1) * 24)
+ ':' + convert(varchar(50), datepart(minute, datesum))
+ ':' + convert(varchar(50), datepart(second, datesum))
from (select SUM(convert(real, datecolumn)) as datesum
from (select CONVERT(datetime, '03:18:46') as datecolumn
union
select CONVERT(datetime, '03:25:06') as datecolumn
union
select CONVERT(datetime, '16:23:46') as datecolumn
union
select CONVERT(datetime, '07:24:20') as datecolumn) alias1
) alias2
Now the result is 30:31:57. Was this what you were after?
|
|
|
|
|
Hi, i just attended an interview and was asked about the difference between MS SQL and ORACLE ? plz help by providing the striking features between these.
Thanx,Subin
|
|
|
|
|
Well, if you have to ask this then you obviously have not grasped one of the important concepts of being a developer - DO YOUR OWN RESEARCH! Google gave about 1.5 million hits for "compare oracle sql server"
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Comparing two different products this way is IMO impossible. Instead of comparing blindly features or capabilites or products in the overall product suites, you should concentrate on the requirements. If you can define what you or the customer needs, then there would be a starting point to conversation considering if one product can fulfill a requirement better than another.
|
|
|
|
|
|
Don't expect a call-back.
"Well, we're getting "F"'d at work. WPF, WCF, and WWF... WTF?" --John Simmons
|
|
|
|
|
Hi all
I have tried to do one general procedure for insert,update,delete.
first i am trying insert.I have one problem:how to pass dynamic parameters and values in stored procedure?
Thanks
monika
|
|
|
|
|
Could you specify a little more, what do you mean by dynamic parameters. The amount of parameters or the amount of data passed using a parameter, dynamic datatype etc.
If it's possible to describe the scenario it would help.
Mika
|
|
|
|
|
If your intention is to create dynamic SQL strings in a general procedure and execute them, I would not recommend it. First, creating dynamic strings in a .NET enviornment is a bad thing unless you use the stringbuilder class. Second, by creating strings and having the SQL engine execute them will force the SQL engine to parse and develop an execution plan for each statement, this could lead to high CPU usage on your DB server depending on how many SQL statements are being processed per second. You should try to use parameterized queries or stored procedures when dealing with a DB engine, then each time a similar query is requested the statement is already parsed and an execution plan has already been choosen, it will just substitue the parameters in and return your rows. In general, dynamic SQL bad. This is true for almost all DB engines not just MS-SQL server.
Just something to think about ...
|
|
|
|
|
If you're using sql2k8 you can use Table Value Parameters.
If you're using sql2k5 you can simulate this by passing a list to a stored procedure using XML parameters.
Ryan
|
|
|
|
|
monika_vasvani wrote: I have tried to do one general procedure for insert,update,delete.
Bad practice. The first time the stored proc runs it gets optimised - say it happens to be an insert. The next time it runs it will use the same query plan (as it will be cached), but you may be doing an update, so the optimal query plan would be totally different. This can lead to performance issues. If you want a single proc to call for all the actions have one, but have it call a different proc for insert, another proc for update eetc.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hello everyone,
I create the following table,
create table ABC (a varchar unique);
then create the following store procedure,
CREATE PROCEDURE [dbo].[prc_AddABC]
AS
BEGIN
INSERT INTO [dbo].[ABC]
values ('a');
SELECT @@ERROR AS ABCERROR;
END -- end of store procedure
After execute the procedure twice, which will cause insert duplicate value 'a' on unique column a error.
What I got from Management Studio is,
ABCERROR 2627
return value -4
My question is, why @@error is not the same as return value? what does the message and return value mean? Where could I find
related documents to read their meanings?
thanks in advance,
George
|
|
|
|
|
@@error gives you the error number as SQL Server generates it. You can use it to identify the problem that occured in the database. Described: http://msdn.microsoft.com/en-us/library/ms188790(SQL.90).aspx[^]
George_George wrote: return value -4
Depends where you got this from. If you got this in an exception at client side, it most likely describes the error that the library sees (OleDbCommand, SqlCommand etc.) If thats the case, you can find the SQL Server error number inside the exception.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
