CODEWITHSUNDEEP
sql-serverdb2migrationlocking
sagosto
sagosto

Reputation: 35

IBM DB2 to SQL Server

I am migrating from IBM DB2 to SQL Server and have a question regarding conversion. What is the SQL Server equivalent of: 

SELECT * 
FROM MyTable with RS USE AND KEEP EXCLUSIVE LOCKS

Upvotes: 0

Views: 147

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88996

Apparantly RS means

Read stability (RS) The read stability isolation level locks only those rows that an application retrieves during a unit of work. RS ensures that any qualifying row read during a UOW cannot be changed by other application processes until the UOW completes, and that any row changed by another application process cannot be read until the change is committed by that process. Under RS, access to uncommitted data and non-repeatable reads are not possible. However, phantom reads are possible.

DB2 Isolation levels

So that is like READ COMMITTED in SQL Server. This is in contrast to SERIALIZABLE or HOLDLOCK which issues range locks on rows not actually present, preventing inserts by other sessions of rows match the predicates of the SELECT query.

And "USE AND KEEP EXCLUSIVE LOCKS" forces reads using X locks.

So the equivalent in SQL Server would be

select * from MyTable with (xlock)

Which will need to be in a transaction, as X locks are always held until the end of the transaction.

Upvotes: 2

Related Questions