Error Handling.

#61

I'd recommend invoking the transactions and stored procedures in a
BEGIN TRANSACTION statement inside of a stored procedure, not from
client code. The SQLS engine will take care of managing locks if your
sprocs are accessing resources in the same order. Blocking and
deadlocking can occur if Tran1 locks Table1 and needs Table2 to
commit, and Tran2 locks Table2 and needs Table1 to commit. If both
Tran1 and Tran2 access Table1 and Table2 in the same order, then Tran2
will go into the wait queue until Tran1 finishes, which will likely be
milliseconds. Extending the transactional boundaries outside of the
server is less efficient and more prone to problems.

--Mary

On Tue, 25 Mar 2008 09:25:04 -0700, Rogers
<Rogers (AT) discussions (DOT) microsoft.com> wrote:

Quote:

Thanks Mary,

Right now the developer is calling Begin transaction and calling the sps and
if any error occurs then it will rollback the transaction. Can you please
tell me if simultaneus access on the same stored procedure, should I do any
locking hint for that ?

Thanks

"Mary Chipman [MSFT]" wrote:

sorry, sent too soon.

You need to do both.

Error handling on the client prevents you from sending invalid
commands to the server, saving network bandwith and avoiding
triggering server-side errors, which are expensive. Treat all user
input as untrusted by validing input parameters, etc.

Error handling in your sprocs is necessary for performance, security
and data integrity. So you need to validate the data passed to the
server a second time -- you have to protect against an attack that
happens outside of the boundaries of your application.

--Mary

On Tue, 25 Mar 2008 07:48:01 -0700, Rogers
Rogers (AT) discussions (DOT) microsoft.com> wrote:

I would appreciate if anyone can let me know which approach is the best for
doing error handling.

From APPLICATION or FROM Database.

Application calls all the stored procedure but application is doing error
handling, I am pretty much concern about locking and all that? can anyone
give me an idea about error handling from application side.

Thanks

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Error Handling.

microsoft.public.sqlserver.clients microsoft.public.sqlserver.clients

Re: Error Handling. - 03-26-2008 , 09:24 AM