I want to make SQLTransaction as global and use it checking the State.
But then where there are Only Select queries are going to fire, it will open transaction.
So, Does it locks database/tables when Only Select query comes in SQLTransaction.
If you have another successful way of doing this, Please suggest.
Thanking you.
tatsA select typically only takes shared lock and releases the lock as soon as the select is done. If you want to an exclusive lock, you will have to use "tablockx" hint. Please see book online for detail.|||Thank you for your suggestion.|||
Just to top it....
SELECT COL1 FROM TBL2 WITH(NOLOCK) WHERE COL2 = @.VAL
|||You should avoid this technique or be sure to understand what is going on behind the scenes. Many people beginning with TSQL somewhere read that (NOLOCK) will not produce any problems, because one can read without careing about any locking, on the one side this is true, but you can′t be sure to have the most recent data, because you will do dirty ready on the database.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||
What are you trying to accomplish? Every statement locks something, at least the schema so you can't drop a table while the user is executing a statement :)
Also, EVERY statement is in a transaction already, the difference between starting one using the client and just the implicit one that starts for every statement is that you might never reach the code to close it if the user gets bored and decides they want to go to lunch.
What locks are taken and how long locks are held is determined by the isolation level. The default is read committed (though your client defautl CAN BE DIFFERENT!) Note too that all of the locks I will talk about are shared locks for data that you read NOT updated data. Updated data requires locks to be held until the end of a transaction.
READ UNCOMMITTED - No locks issued or obeyed other than schema locks
READ COMMITTED - Locks are (generally) taken only as long as needed to protect the lowest level thing you are working with (like a row in a table.) Generally speaking it "crabs" through the objects grab a lock, release a lock. Another user can modify data you have already read
REPEATABLE READ - LIke Read Committed, only it doesn't release lock it takes. Prevents another user from modifying data you have already read, doesn't prevent user from creating new rows (referred to as Phantoms).
SERIALIZABLE - Like Repeatable Read, only it also places a lock on Ranges of data, so if you say WHERE between 0 and 100, a range lock is places on 0 and 100 to say no other user can create data in that range either.
SNAPSHOT (2005) - Readers look at only committed data, and if other users have locked portions of the data, they will only see the database as it was when they started their command. Really cool, but uses more tempdb than would otherwise.
So the only real concern about executing your select in a transaction is that you will run the risk of a user failure locking other users until cleanup occurs and the connection is noticed to be stale and not in use.
I generally would suggest that you execute your transactions in a single batch and put the BEGIN TRAN and COMMIT TRAN in the same batch if possible and using TRY...CATCH blocks around statements if you have 2005. Then you lower the risk of hanging a transaction. The connection might still exist, but the commit/rollback will certainly execute.
