Ttyjukl

Why do Computer Science majors learn Calculus?

Counterexample: a pair of linearly ordered sets that are isomorphic to subsets of the other, but not isomorphic between them

A ​Note ​on ​N!

Sci-fi novel series with instant travel between planets through gates. A river runs through the gates

What does "rf" mean in "rfkill"?

How to stop co-workers from teasing me because I know Russian?

Please, smoke with good manners

How to set the font color of quantity objects (Version 11.3 vs version 12)?

Pressure to defend the relevance of one's area of mathematics

What is the strongest case that can be made in favour of the UK regaining some control over fishing policy after Brexit?

Is it possible to dynamically set properties of an `Object` using Apex?

How to figure out whether the data is sample data or population data apart from the client's information?

Pulling the rope with one hand is as heavy as with two hands?

Feels like I am getting dragged in office politics

How do I write a simple if-elseif protocol in Latex?

Why was Germany not as successful as other Europeans in establishing overseas colonies?

Stateful vs non-stateful app

Was it really necessary for the Lunar Module to have 2 stages?

You look catfish vs You look like a catfish

Do generators produce a fixed load?

Are Boeing 737-800’s grounded?

Why is current rating for multicore cable lower than single core with the same cross section?

Were there two appearances of Stan Lee?

How to determine the actual or "true" resolution of a digital photograph?

Lock in SQL Server and Oracle

Read Committed Snapshot Isolation vs Read Committed - pros and cons?FIFO queue table for multiple workers in SQL ServerCommit vs Fast Commit vs Commit Cleanout in Oracle DatabaseRefreshing Materialized view creates many child cursorsDetecting the locked table or row in SQL ServerCan I rely on reading SQL Server Identity values in order?Read Committed Snapshot Isolation vs Read Committed - pros and cons?Why SELECT query is waiting on HADR_SYNC_COMMIT?Strange Locking Behavior in SQL ServerOracle Database troubleshooting enq: TX - row lock contentionSQL Server Exclusive(X) lock not consistently blocking Shared(S) lock on a resource

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

2

I have been doing some tests of locking/blocking in MSSQL Server and Oracle, I noticed one difference:

In Oracle - I performed an update for one row without issuing commit or rollback and in another session I can view the underlying record, of course, I could view the last committed data and not the value that was yet to be committed.

In MSSQL Server - When I did the same operation in another session the SQL Server kept waiting for commit or rollback of the row that is getting updated.

Could somebody please explain locking mechanism between MSSQL server and Oracle.

sql-server oracle locking

share|improve this question

edited 1 hour ago

Learning_DBAdmin

752215

asked 2 hours ago

Danilo NetoDanilo Neto

1265

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Possible duplicate of Read Committed Snapshot Isolation vs Read Committed - pros and cons?
  
  – mustaccio
  2 hours ago
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  You have hit on one of the fundamental differences between Oracle and MSSQL. In fact I believe this behavior of Oracle is unique to Oracle among all RDBMS products, and many would argue that this is a key reason they consider Oracle to be a superior RDBMS. Instead of trying to make a judgement on how you think it "should" be, or trying to make one behave like the other, it's better to simply adapt best practice for whatever rdbms you are working with. "When in Rome . . . "
  
  – EdStevens
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  "Database agnostic code" is a myth. This happens to be one of the reasons.
  
  – Michael Kutz
  1 hour ago
  

  
  
  
  

add a comment | 

2

I have been doing some tests of locking/blocking in MSSQL Server and Oracle, I noticed one difference:

In Oracle - I performed an update for one row without issuing commit or rollback and in another session I can view the underlying record, of course, I could view the last committed data and not the value that was yet to be committed.

In MSSQL Server - When I did the same operation in another session the SQL Server kept waiting for commit or rollback of the row that is getting updated.

Could somebody please explain locking mechanism between MSSQL server and Oracle.

sql-server oracle locking

share|improve this question

edited 1 hour ago

Learning_DBAdmin

752215

asked 2 hours ago

Danilo NetoDanilo Neto

1265

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Possible duplicate of Read Committed Snapshot Isolation vs Read Committed - pros and cons?
  
  – mustaccio
  2 hours ago
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  You have hit on one of the fundamental differences between Oracle and MSSQL. In fact I believe this behavior of Oracle is unique to Oracle among all RDBMS products, and many would argue that this is a key reason they consider Oracle to be a superior RDBMS. Instead of trying to make a judgement on how you think it "should" be, or trying to make one behave like the other, it's better to simply adapt best practice for whatever rdbms you are working with. "When in Rome . . . "
  
  – EdStevens
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  "Database agnostic code" is a myth. This happens to be one of the reasons.
  
  – Michael Kutz
  1 hour ago
  

  
  
  
  

add a comment | 

I have been doing some tests of locking/blocking in MSSQL Server and Oracle, I noticed one difference:

In Oracle - I performed an update for one row without issuing commit or rollback and in another session I can view the underlying record, of course, I could view the last committed data and not the value that was yet to be committed.

In MSSQL Server - When I did the same operation in another session the SQL Server kept waiting for commit or rollback of the row that is getting updated.

Could somebody please explain locking mechanism between MSSQL server and Oracle.

sql-server oracle locking

share|improve this question

edited 1 hour ago

Learning_DBAdmin

752215

asked 2 hours ago

Danilo NetoDanilo Neto

1265

share|improve this question

edited 1 hour ago

Learning_DBAdmin

752215

asked 2 hours ago

Danilo NetoDanilo Neto

1265

share|improve this question

edited 1 hour ago

Learning_DBAdmin

752215

asked 2 hours ago

Danilo NetoDanilo Neto

1265

edited 1 hour ago

Learning_DBAdmin

752215

edited 1 hour ago

Learning_DBAdmin

752215

asked 2 hours ago

Danilo NetoDanilo Neto

1265

asked 2 hours ago

Danilo NetoDanilo Neto

1265

1 Answer
1

active

oldest

votes

4

This is a default behavior of SQL server, to understand more you need to invoke isolation level. What you have described above falls in isolation level called "Read Committed". If you want similar behavior like Oracle in SQL server then you need to set the isolation level as Read Committed Snapshot Isolation, basically this isolation level takes snapshot of your data and puts them in tempdb, accordingly you would see last committed record which was snapshot of last committed records. We have another isolation Read Uncommitted, this will also behave like Oracle with a big catch i.e. you would read uncommitted records and there is a fair chance that you could read a dirty record.

There is concept of nolock and readpast as lock hint in SQL server for avoiding locking however it has its own impact.

You may read more about isolation level at Microsoft site at this link.

There are tonnes of article on this subject from many SME, I am listing few of them for your reference:

1. Brent Ozar --> https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/




2. Kendra Little --> https://littlekendra.com/2016/02/18/how-to-choose-rcsi-snapshot-isolation-levels/




3. Erik Darling --> https://www.brentozar.com/archive/2018/01/heaps-deletes-optimistic-isolation-levels/




4. Paul White --> https://sqlperformance.com/2014/07/t-sql-queries/isolation-levels




5. Robert Sheldon --> https://www.red-gate.com/simple-talk/sql/t-sql-programming/questions-about-t-sql-transaction-isolation-levels-you-were-too-shy-to-ask/

There is a video from Brent Ozar(Unable to find now) where he has clearly explained the difference you have been talking about between Oracle and SQL server. For this reason, Oracle costs much more than SQL server per core.

I hope above helps. Actually this is less of a question than a very important topic and understanding of SQL server and hard to cover them in one answer however above links will definitely guide you in right direction.

share|improve this answer

answered 2 hours ago

Learning_DBAdminLearning_DBAdmin

752215

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f236889%2flock-in-sql-server-and-oracle%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

4

This is a default behavior of SQL server, to understand more you need to invoke isolation level. What you have described above falls in isolation level called "Read Committed". If you want similar behavior like Oracle in SQL server then you need to set the isolation level as Read Committed Snapshot Isolation, basically this isolation level takes snapshot of your data and puts them in tempdb, accordingly you would see last committed record which was snapshot of last committed records. We have another isolation Read Uncommitted, this will also behave like Oracle with a big catch i.e. you would read uncommitted records and there is a fair chance that you could read a dirty record.

There is concept of nolock and readpast as lock hint in SQL server for avoiding locking however it has its own impact.

You may read more about isolation level at Microsoft site at this link.

There are tonnes of article on this subject from many SME, I am listing few of them for your reference:

1. Brent Ozar --> https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/




2. Kendra Little --> https://littlekendra.com/2016/02/18/how-to-choose-rcsi-snapshot-isolation-levels/




3. Erik Darling --> https://www.brentozar.com/archive/2018/01/heaps-deletes-optimistic-isolation-levels/




4. Paul White --> https://sqlperformance.com/2014/07/t-sql-queries/isolation-levels




5. Robert Sheldon --> https://www.red-gate.com/simple-talk/sql/t-sql-programming/questions-about-t-sql-transaction-isolation-levels-you-were-too-shy-to-ask/

There is a video from Brent Ozar(Unable to find now) where he has clearly explained the difference you have been talking about between Oracle and SQL server. For this reason, Oracle costs much more than SQL server per core.

I hope above helps. Actually this is less of a question than a very important topic and understanding of SQL server and hard to cover them in one answer however above links will definitely guide you in right direction.

share|improve this answer

answered 2 hours ago

Learning_DBAdminLearning_DBAdmin

752215

add a comment | 

4

This is a default behavior of SQL server, to understand more you need to invoke isolation level. What you have described above falls in isolation level called "Read Committed". If you want similar behavior like Oracle in SQL server then you need to set the isolation level as Read Committed Snapshot Isolation, basically this isolation level takes snapshot of your data and puts them in tempdb, accordingly you would see last committed record which was snapshot of last committed records. We have another isolation Read Uncommitted, this will also behave like Oracle with a big catch i.e. you would read uncommitted records and there is a fair chance that you could read a dirty record.

There is concept of nolock and readpast as lock hint in SQL server for avoiding locking however it has its own impact.

You may read more about isolation level at Microsoft site at this link.

There are tonnes of article on this subject from many SME, I am listing few of them for your reference:

1. Brent Ozar --> https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/




2. Kendra Little --> https://littlekendra.com/2016/02/18/how-to-choose-rcsi-snapshot-isolation-levels/




3. Erik Darling --> https://www.brentozar.com/archive/2018/01/heaps-deletes-optimistic-isolation-levels/




4. Paul White --> https://sqlperformance.com/2014/07/t-sql-queries/isolation-levels




5. Robert Sheldon --> https://www.red-gate.com/simple-talk/sql/t-sql-programming/questions-about-t-sql-transaction-isolation-levels-you-were-too-shy-to-ask/

There is a video from Brent Ozar(Unable to find now) where he has clearly explained the difference you have been talking about between Oracle and SQL server. For this reason, Oracle costs much more than SQL server per core.

I hope above helps. Actually this is less of a question than a very important topic and understanding of SQL server and hard to cover them in one answer however above links will definitely guide you in right direction.

share|improve this answer

answered 2 hours ago

Learning_DBAdminLearning_DBAdmin

752215

add a comment | 

This is a default behavior of SQL server, to understand more you need to invoke isolation level. What you have described above falls in isolation level called "Read Committed". If you want similar behavior like Oracle in SQL server then you need to set the isolation level as Read Committed Snapshot Isolation, basically this isolation level takes snapshot of your data and puts them in tempdb, accordingly you would see last committed record which was snapshot of last committed records. We have another isolation Read Uncommitted, this will also behave like Oracle with a big catch i.e. you would read uncommitted records and there is a fair chance that you could read a dirty record.

There is concept of nolock and readpast as lock hint in SQL server for avoiding locking however it has its own impact.

You may read more about isolation level at Microsoft site at this link.

There are tonnes of article on this subject from many SME, I am listing few of them for your reference:

1. Brent Ozar --> https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/




2. Kendra Little --> https://littlekendra.com/2016/02/18/how-to-choose-rcsi-snapshot-isolation-levels/




3. Erik Darling --> https://www.brentozar.com/archive/2018/01/heaps-deletes-optimistic-isolation-levels/




4. Paul White --> https://sqlperformance.com/2014/07/t-sql-queries/isolation-levels




5. Robert Sheldon --> https://www.red-gate.com/simple-talk/sql/t-sql-programming/questions-about-t-sql-transaction-isolation-levels-you-were-too-shy-to-ask/

There is a video from Brent Ozar(Unable to find now) where he has clearly explained the difference you have been talking about between Oracle and SQL server. For this reason, Oracle costs much more than SQL server per core.

I hope above helps. Actually this is less of a question than a very important topic and understanding of SQL server and hard to cover them in one answer however above links will definitely guide you in right direction.

share|improve this answer

answered 2 hours ago

Learning_DBAdminLearning_DBAdmin

752215

share|improve this answer

answered 2 hours ago

Learning_DBAdminLearning_DBAdmin

752215

answered 2 hours ago

Learning_DBAdminLearning_DBAdmin

752215

answered 2 hours ago

Learning_DBAdminLearning_DBAdmin

752215