Error handling in sql server . Try / catch

Things can go wrong in sql operation. So how we handle Errors in sql server.

We can detect errors in two ways

1. Try/catch
2. @@ERROR

@@ERROR

If there is no errors based on the last operation @@ERROR will be set to 0 . If something went wrong it will be set to value other than 0. How ever this not best way to handle errors in sql server.

TRY/CATCH

BEGIN TRY

-- this is where we put our codes that can be generate errors

END TRY

BEGIN CATCH

-- this where we put code to handle errors

END CATCH

Big thing in TRY block is always we’re going to assume success because only way that we’re going to get to the next line of code is if the line before is succeeded

Concurrency and locks in sql server

Transactions in sql server must be Isolated. It means that if a transaction is running and somebody else comes along and there’re trying to work with data that previous transaction working with, they’re not allowed to touch that.

Sql server handle this problem using concurrency and locks.

Concurrency is the process of managing who has access to data. 

The way that access to data is managed by locks.

Locks:

1. Shared Lock                                                                                                                                                Known as a read lock, This type of lock is obtained anytime when issue a select query. Shared lock can be shared with others who are trying to read that data.
2. Exclusive locks                                                                                                                                          Whn ever going to modify data this lock is issued. We need to make sure nobody else working with that data, reading or trying to modify that. This lock prevents others from accessing that data.
3. Intent lock                                                                                                                                                    If someone doing operation and we want make sure that nobody drops that table or nobody drops the database. This is where Intent lock is issued.

One of the issue with locks is it is bit of overhead.

Locks can be maintained at a few levels

1.Row level

2.Page level

3.Table level

Make sure that do locks as little data as possible and for as short a period of time as possible