Skip to main content
Database Administrators

Return to Answer

Clarify a few points.
Source Link
Richard
Richard
  • 1
  • 9
  • 42
  • 62

You can have tenseveral different connections reading from the same datasource at any given time. But to insure that no one changes that data while they're reading it, they take out a Read Lock.

Once a connection has a read lock on a piece of data, all other connections must wait until they release theirthe Read Lock is released before they can write the data. Others can, however, take out Read Locks of their own on that same piece of data.

When a connection has an exclusive lock on the data, no other connections may read from the data. This helps prevent dirty reads by insuring that no one can read the data while others are writing the dataits being written.

"Blocking" is simply a term that means that one connection is holding a lock on a resource when another connection wants to read or write to it. It doesn't necessarily mean that the owner connection won't release it, just that it's currently holding it.

Like children, these connections are not willing to share at all. Each connection needs access to both of the resources in order to continue. However, they are in a state of permanent blocking. In this state, the parent (DBMS) has to come in and choose a loser so that one of the children (connectionconnections) can have access to both of the resources.

Once that "winning" connection is done, it releases the resources and then the other ("losing") connection can try again to get to both resources.

But thatSo, the concept of a deadlock is where you have two locksresources that are blocking each other.

YouHere, you can read more about all the different types of locklocks SQL Server has to offer, here. That link also tells about and the different resources that can cause blocking (or deadlocks)/deadlocks. The article is old, but it still applies for SQL Server 2000 through 2008 R2. (There are a few more types of locks added to later versions of SQL Server, but that will give you a starting point.)

You can have ten different connections reading from the same datasource at any given time. But to insure that no one changes that data while they're reading it, they take out a Read Lock.

Once a connection has a read lock on a piece of data, all other connections must wait until they release their Read Lock before they can write the data. Others can, however, take out Read Locks of their own on that same piece of data.

When a connection has an exclusive lock on the data, no other connections may read from the data. This helps prevent dirty reads by insuring that no one can read the data while others are writing the data.

"Blocking" is simply a term that means that one connection is holding a lock on a resource when another connection wants to read it. It doesn't necessarily mean that the owner connection won't release it, just that it's currently holding it.

Like children, these connections are not willing to share at all. Each connection needs access to both of the resources in order to continue. However, they are in a state of permanent blocking. In this state, the parent (DBMS) has to come in and choose a loser so that one of the children (connection) can have access to both of the resources.

Once that connection is done, it releases the resources and the other connection can get to both resources.

But that concept of a deadlock is where you have two locks that are blocking each other.

You can read more about all the different types of lock SQL Server has to offer, here. That link also tells about the different resources that can cause blocking (or deadlocks). The article is old, but it still applies for SQL Server 2000 through 2008 R2.

You can have several different connections reading from the same datasource at any given time. But to insure that no one changes that data while they're reading it, they take out a Read Lock.

Once a connection has a read lock on a piece of data, all other connections must wait until the Read Lock is released before they can write the data. Others can, however, take out Read Locks of their own on that same piece of data.

When a connection has an exclusive lock on the data, no other connections may read from the data. This helps prevent dirty reads by insuring that no one can read the data while its being written.

"Blocking" is simply a term that means that one connection is holding a lock on a resource when another connection wants to read or write to it. It doesn't necessarily mean that the owner connection won't release it, just that it's currently holding it.

Like children, these connections are not willing to share at all. Each connection needs access to both of the resources in order to continue. However, they are in a state of permanent blocking. In this state, the parent (DBMS) has to come in and choose a loser so that one of the children (connections) can have access to both of the resources.

Once that "winning" connection is done, it releases the resources and then the other ("losing") connection can try again to get to both resources.

So, the concept of a deadlock is where you have two resources that are blocking each other.

Here, you can read more about all the different types of locks SQL Server has to offer and the different resources that can cause blocking/deadlocks. The article is old, but it still applies for SQL Server 2000 through 2008 R2. (There are a few more types of locks added to later versions of SQL Server, but that will give you a starting point.)

mention blocking regarding the article.
Source Link
Richard
Richard
  • 1
  • 9
  • 42
  • 62

You can read more about all the different types of lock SQL Server has to offer, here. It's That link also tells about the different resources that can cause blocking (or deadlocks). The article is old, but it still applies for SQL Server 2000 through 2008 R2.

You can read more about all the different types of lock SQL Server has to offer, here. It's old, but it still applies for SQL Server 2000 through 2008 R2.

You can read more about all the different types of lock SQL Server has to offer, here. That link also tells about the different resources that can cause blocking (or deadlocks). The article is old, but it still applies for SQL Server 2000 through 2008 R2.

Source Link
Richard
Richard
  • 1
  • 9
  • 42
  • 62

Analogies

Sometimes it helps to use analogies away from computers.

Let's say you have a ball and two children. Only one child can have the ball at any one time. However, if one of the children gets the ball and doesn't let go of it because he's distracted (watching TV, for example), then the other child will not get to play with the ball.

The other child is blocked from that resource.

If we compare this to the TV, for example, several children can watch TV at any one point.

Locks

If we move over to the database world, we see that there are different ways to use resources (just like our two examples above). We can perform "reads" or we can perform "writes".

When we want to read the data, there's no reason that other's can't read the data as well--just like two people watching TV. However, if we want to write the data, then we need to make sure that no one else is looking at it. If they are reading it while we're writing it, they will get "dirty" reads. (Meaning, they'll see the data partially written out, which will be invalid.)

In order to insure that these dirty reads never occur, we have two primary types of locks, Read Locks and Exclusive Locks.

Read Lock

You can have ten different connections reading from the same datasource at any given time. But to insure that no one changes that data while they're reading it, they take out a Read Lock.

Once a connection has a read lock on a piece of data, all other connections must wait until they release their Read Lock before they can write the data. Others can, however, take out Read Locks of their own on that same piece of data.

Exclusive Lock

If a connection wants to update/insert/delete a piece of data, they have to take out an exclusive lock. This prevents any other connection from also taking out a lock on the data (making the lock exclusive to that connection).

When a connection has an exclusive lock on the data, no other connections may read from the data. This helps prevent dirty reads by insuring that no one can read the data while others are writing the data.

Blocking

"Blocking" is simply a term that means that one connection is holding a lock on a resource when another connection wants to read it. It doesn't necessarily mean that the owner connection won't release it, just that it's currently holding it.

Compare this to the case with a child holding the ball. The child holding the ball is blocking all other children from holding the ball.

Deadlock

I know you didn't ask this, but it's only one more step to get to deadlocks (and it's related very directly to blocking).

Deadlocks can happen when you have two connections that each have a lock, but they want each others resource. In this scenario, it's like two children that each has a ball, but wants the other's ball.

Like children, these connections are not willing to share at all. Each connection needs access to both of the resources in order to continue. However, they are in a state of permanent blocking. In this state, the parent (DBMS) has to come in and choose a loser so that one of the children (connection) can have access to both of the resources.

Once that connection is done, it releases the resources and the other connection can get to both resources.

But that concept of a deadlock is where you have two locks that are blocking each other.

You can read more about all the different types of lock SQL Server has to offer, here. It's old, but it still applies for SQL Server 2000 through 2008 R2.