CODEWITHSUNDEEP
sqlsql-servert-sqltransactions
Brisingr
Brisingr

Reputation: 82

Update inside a read uncommitted transaction

I am having an SP with transaction isolation level set as Read Uncommitted. For Example

Create Procedure TrailSP
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRY

UPDATE TrialTable
SET TrailColumn ='Update'
WHERE TrailID=1

--this is followed by more updates and selects

END TRY
BEGIN CATCH

RETURN -1;
END CATCH

RETURN 0;

what I want to know is that the first update I have given in the SP will it get committed instantly as it executes or will it get committed along with the rest of the logic at the end of Sp.

Upvotes: 0

Views: 2764

Answers (2)

skyde
skyde

Reputation: 2956

-Any data read inside a READ UNCOMMITTED is data that could disappear because the transaction that wrote it rollback.

-Its also possible to not see some row that have been committed because a transaction that have not yet committed and might never commit deleted it.

-Its also possible for row to be missing or be duplicated because of PageSplit.

Basically anything is possible, so the data read should never be used to compute anything that should be written to the database or you risk corrupting your database.

TLDR: never use READ UNCOMMITTED

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294247

It will get committed, as any update under any transaction isolation level, when the transaction commits. This has nothing to do with the stored procedure ending.

If the call to your procedure has a transaction, then the commit will occur when that transaction commits.

If the call to your procedure does not have a transaction but the session has enabled implicit transactions then it will commit when the application explicitly commits.

If the call to your procedure does not have a transaction and session has the auto commit transaction behavior (ie. the most common case) then transaction will commit when the UPDATE statement completes.

Enabling READ UNCOMMITTED for an UPDATE is a no-op.

Upvotes: 2

Related Questions