Sql isolation levels, Read and Write locks

Question

A bit lame question but I got confused... Difference between isolation levels as far as I understood is how they managed their locks (http://en.wikipedia.org/wiki/Isolation_(database_systems)). So as mentioned in the article there are Read, Write and Range locks but there is no definition what they are itself. What are you allowed to do and what not. When I googled for it there was nothing concrete and instead I got confused with new terms like Pessimistic Lock an Optimistic Lock, Exclusive lock, Gap lock and so on. I'd be pleased if someone give me a short overview and maybe point me a good bunch materials to enlighten myself.

My initial question which started the research of isolation levels was:

What happens when I have concurrent inserts (different users of web app) into one table when my transactions isolation level is READ_COMMITED. Is the whole table locked or not? Or generally what happens down there :) ?

Thanks in advance !

No I hoped it is generic concept ... No concrete RDBMS involved. — EnTrERy, May 26 '13 at 14:25

score 1 · Answer 1 · answered May 26 '13 at 14:38

What happens when I have concurrent inserts (different users of web app) into one table when my transactions isolation level is READ_COMMITED.

"Read committed" means that other sessions cannot see the newly inserted row until its transaction is committed. A SQL statement that runs without an explicit transaction is wrapped in an implicit one, so "read committed" affects all inserts.

Some databases implement "read committed" with locks. For example, a read lock can be placed on the inserted row, preventing other tractions from reading it. Other databases, like Oracle, use multiversion concurrency control. That means they can represent a version of the database before the insert. This allows them to implement "read committed" without locks.

Could you go in more details for the different lock types - read, write and range ? — EnTrERy, May 26 '13 at 14:43

score 0 · Answer 2 · answered Dec 15 '16 at 08:27

0

With my understanding, isolation level will decide how and when the locks are to be acquired and released.

Ref: http://aboutsqlserver.com/2011/04/28/locking-in-microsoft-sql-server-part-2-locks-and-transaction-isolation-levels/

answered Dec 15 '16 at 08:27

Anto Varghese

3,131
6
31
38

score -2 · Accepted Answer · answered May 26 '13 at 19:08

-2

This is what I was looking for ...

http://en.wikipedia.org/wiki/Two-phase_locking

answered May 26 '13 at 19:08

EnTrERy

151
2
3
11

Are you sure this answers your question? It seems to be unrelated. – usr May 26 '13 at 20:06

Sql isolation levels, Read and Write locks

3 Answers3