Locking the database

Hi I'm trying to see what's locking the databas开发者_Go百科e and found 2 types of locking. Optimistic and Pessimistic Locking. I found some articles on Wiki but I would like to know more ! Can someone explain me about those locking ? We should only use locking when we need exclusive access to something? Locking only happens when we use transaction? Thanks in advance.

Kevin

Optimistic locking is no locking at all.

It works by noting the state the system was in before you started making your changes, and then going ahead and just making those changes, assuming (optimistically) that no one else will want to make conflicting updates. Just as you are about to atomically commit those changes, you would check if in the mean-time someone else has also updated the same data. In which case, your commit fails.

Subversion for example using optimistic locking. When you try to commit, you have to handle any conflicts, but before that, you can do on your working copy whatever you want.

Pessimistic locks work with real locks. Assuming that there will be contention, you lock everything you want to update before touching it. Everyone else will have to wait for you to commit or rollback.

When using a relational database with transaction support, the database usually takes care of locking internally (such as when you issue an UPDATE statement), so for normal online processing you do not need to handle this yourself. Only if you want to do maintenance work or large batches do you sometimes want to lock down tables.

We should only use locking when we need exclusive access to something?

You need it to prevent conflicting operations from other sessions. In general, this means updates. Reading data can normally go on concurrently.

Locking only happens when we use transaction?

Yes. You will accumulate locks while proceeding with your transaction, releasing all of them at the end of it. Note that a single SQL command in auto-commit mode is still a transaction by itself.

Transactions isolation levels also specify the locking behaviour. BOL refers:Transaction isolation levels control:

Whether locks are taken when data is read, and what type of locks are requested.

How long the read locks are held.

Whether a read operation referencing rows modified by another transaction:

Blocks until the exclusive lock on the row is freed.

Retrieves the committed version of the row that existed at the time the statement or transaction started.

Reads the uncommitted data modification.

The default levels are: Read uncommitted (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read)

Read committed (Database Engine default level)

Repeatable read

Serializable (the highest level, where transactions are completely isolated from one another)