Weird Transaction Problems

[SSJ]

Hi. I'm running in some transaction problems with SQL Server 2K + SP3.

I developed a java application to access my DB and something weird is happening. Imagine this case:

BEGIN TRANSACTION
INSERT INTO table1(ID,description) Values(10,'nothing');
SELECT * FROM table1 WHERE ID = 10;
END TRANSACTION

Now, in the SELECT statement, one row should be returned, but that doesn't happen. It says ID = 10 doesn't exist. Any change that I do during this transaction isn't visible to the next statements I execute.

If I do it without transactions it will work fine, I'll get one record in the SELECT statement.

This has nothing to do with Transaction Isolation right? This only applies to different transactions afaik.

Also my code is fine, I just tested it with another Database in other machine with SQL Server 2K and it's working.

Maybe I configured something wrong (in SQL Server instance or in Database)?

TIA

 

[JayKusch]

I beleive your code is a bit off ... Should it not be ...

BEGIN TRANSACTION
INSERT INTO table1(ID,description) Values(10,'nothing');
SELECT * FROM table1 WHERE ID = 10;
COMMIT TRANSACTION

Thanks

J. Kusch

 

[SSJ]

yes, you're right, but that wasn't my code at all it was just an example I gave to show what was going on.
I used END instead of COMMIT only on the post, the code is working fine.

Anyway I got it working now, but still don't know what happened, I just created a whole new database and transactions are working fine. It's weird, but probably something was messed up in the old one, couldn't figure what though.