SQL Server 2000 Transactions, Commit, And Rollback.

Question

question

David 2 1 asked Apr 7, '10

SQL Server 2000 Transactions, Commit, And Rollback.

Is there a way I can issue rollbackable DML queries in SQL Server 2000 without locking tables? In Oracle I know the before image is stored in the UNDO Tablespace before committed so that if the transaction is not committed it can be rollbacked while during this other users can access and query the table being updated. However in SQL Server when I try something like the below the table will be locked until the transaction is rolledback or committed. Is there a way round doing this? I am wary about locking production database tables for DML transactions.

CREATE TABLE testtbl(                    
testcol VARCHAR(100))                    
                    
INSERT INTO testtbl VALUES('Insert Something')                    
                    
BEGIN TRANSACTION                    
UPDATE testtbl                    
SET testcol = 'Update Something'

Thanks.

t-sql sql-server-2000

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-04-09T16:03:22Z

Piotr Rodak answered Apr 9, '10

You can use SNAPSHOT transaction isolation level. It may do what you want, but the table will be locked at the commit time anyway.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question