CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
anonxen
anonxen

Reputation: 824

What is wrong with the below tsql code

BEGIN TRAN

SELECT * FROM AnySchema.AnyTable
WHERE AnyColumn = SomeCondition

COMMIT

I know the transaction is not required here because it is just a select but just want to know how bad a programming it is and whether it is going to be an overhead on the DB engine.

Upvotes: 0

Views: 141

Answers (3)

paparazzo
paparazzo

Reputation: 45096

I think you would be taking a holdlock and most likely a tablock
table hints

That is not always a good thing as you would block any update or deletes (maybe even inserts)

It would be better to let SQL decide what level of of locks to take. Most likely pagelocks. I would stay away from nolock as bad stuff can happen.

On a select on a single table just let the optimizer do it's thing.

Upvotes: 0

Jonathan Allen
Jonathan Allen

Reputation: 70317

That's actually a good question. To understand what's going on, you need to know about SET IMPLICIT_TRANSACTIONS.

When ON, the system is in implicit transaction mode. This means that if @@TRANCOUNT = 0, any of the following Transact-SQL statements begins a new transaction. It is equivalent to an unseen BEGIN TRANSACTION being executed first:

When OFF, each of the preceding T-SQL statements is bounded by an unseen BEGIN TRANSACTION and an unseen COMMIT TRANSACTION statement. When OFF, we say the transaction mode is autocommit. [this is the default]

If your T-SQL code visibly issues a BEGIN TRANSACTION, we say the transaction mode is explicit.

https://msdn.microsoft.com/en-us/library/ms187807.aspx

Since the SQL Server would have created a transaction for you, manually doing doesn't actually change anything. The exact same thing would have happened either way.

Summary: What you are doing isn't 'wrong' because it has no effect, but unnecessary and confusing to the reader.

Upvotes: 0

Surajit Biswas
Surajit Biswas

Reputation: 809

You may use transaction on SELECT statements to ensure nobody else could update / delete records of the table of while the bunch of your select queries are executing.

Using WITH(NOLOCK):

Anyways, you may also use WITH(NOLOCK) for t_sql

SELECT * FROM AnySchema.AnyTable WITH(NOLOCK) WHERE AnyColumn = SomeCondition

WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. Here stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk.

TRANSACTION Block :

Using TRANSACTION block will not cause much of extra DB overload but if you keep the same type practice on, and suppose , at any SQL block you forget (you / your developers may forget, right ?) to close the transaction, then other processes can't work on the same table.

Anyways, it depends on what type of application you are using. If very frequent update and select things are there , it is advised not to use such transaction blocks. If medium level of updates and select are there, occurs next to each other, you may use transaction blocks for select (but ensure to close the transaction).

Upvotes: 2

Related Questions