Problem in transaction handling in SQL server database

Question

Problem in transaction handling in SQL server database

Niraj Kumar

SSC Rookie

Points: 25
More actions
September 19, 2007 at 11:24 am

#174818

I am facing a serious problem in SQL server 2000.
I have a table called testtable
When I ruuning a query like in one editor like
Query1:
begin transaction
insert into dbo.testtable values(5,'trante', 'testfor tran')
And in second window I am running a query like
Query 2:
select * from dbo.testtable
I will not get any result till i do commit or rollback of first query.
I am not able to understand this issue
Can anybody help me immediatly. I need help urgently.....

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

This is expected behavior. let's say your transaction that modifies the data adds temporary information. E.g in case of accounts that a person has 1000000 USD if this is rolled back, this information should not have been visible in any of the other transactions. I suggest you read about transactions.

If it is acceptable for you to look at data that is "dirty"

, i.e. bay not be committed, have a look at isolation levels, and you will be able to set this.

http://msdn2.microsoft.com/en-us/library/ms173763.aspx

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 2

http://msdn2.microsoft.com/en-us/library/ms190612.aspx

is a good starting point about learning the basics about transactions.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Niraj Kumar SSC Rookie Points: 25 More actions · Answer 3

I have to read committed data.

But not able to read the committed data also

what I do for that

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

Try it this way:

Query1: 
begin transaction
insert into dbo.testtable values(5,'trante', 'testfor tran')
if @@error = 0 commit else rollback

Niraj Kumar SSC Rookie Points: 25 More actions · Answer 5

I hope I not made my question very clear.

My question is:

In my table already I have some data like 1,'test','niraj test'

Now the situation is:

User1 from one system run the query and not committed or rollback the transaction:

begin transaction

insert into dbo.testtable values(5,'trante', 'testfor tran')

Then on other system user2 running the query

Select * from testtable

The problem is User2 is not able to see the committed data.

My question is why this happening as user2 just want to read the committed data.

Since in my application this situation occurs often.

Please help me what I should do for this.

Bob Fazio SSChampion Points: 10794 More actions · Answer 6

I suspect that you are used to the same behavior that exists in Oracle for transactions. SQL Server blocks READS. Oracle does not.

Now you are posting to the 2005 admin forum. If you have 2005, there is a way to setup SQL Server to operate the same way Oracle Does.

ALTER DATABASE <database name>

SET READ_COMMITTED_SNAPSHOT ON

WITH <termination>;

ALTER DATABASE <database name>

SET ALLOW_SNAPSHOT_ISOLATION ON;

Niraj Kumar SSC Rookie Points: 25 More actions · Answer 7

Actually I was facing the problem in sql server 2000. I found there is no way in SQL server 2000; except the dirty read.

Any how my company planning to upgrade to SQL server 2005. In SQL server 2005 I can use isolation level SNAPSHOT or SET READ_COMMITTED_SNAPSHOT ON.

I already found the solution .

Thanks

Niraj Kumar SSC Rookie Points: 25 More actions · Answer 8

I fount the solution.

Actually I was looking for the Transaction which behaves like ORACLE.

Like While Read Pessimetic

abd while Update Opptimistic

This is available in SQL server 2005 by introducing two new isolation: Read committed isolation with row version, and Snapshot Isolation.

Thanks to one who helped me.:D

Toby Harman SSCarpal Tunnel Points: 4168 More actions · Answer 9

The other solution you might try would be to look at the locking for the INSERT statement.

if this was restricted to a ROW level lock (rather than page or table) then you should be able to read the previously committed data with any locking level.