Monday, May 12, 2008

Improving Concurrency

Database scalability and performance is a concern of almost every database developer. This becomes increasingly important as your application gets used by more an more concurrent users.

It is important to note that scalability is not always linear (see Glenn Paulley's article "The myth of linear scalability"). How an operation is performed can be just as important as how fast your network and server are. For example using a table to store primary keys is commonly used but can cause scalability issues.

Lets examine a typical implementation of this concept. The user needs to get the value they will use as their key, then increment the value so the next user gets a unique value. To ensure that the next value is unique only one user can be reading the value at any given time so the following sequence is followed.

  1. Find the proper record
  2. Lock the record
  3. Read the record
  4. Increment the record
  5. Return the result to the client
  6. Unlock the record

This sequence requires 6 calls between the client and server and is normally executed very quickly. As the number of users increases, however, several clients may be requesting a lock on the same record. These lock requests will get processed by the server which can delay the client who owns the lock from unlocking the record. Essentially the server is busy telling all the other users the status of the record while the user who has the record locked waits to unlock it.

This type of situation may not occur for a small number of users since each request is processed quickly. As an example let's say the database server serializes all requests and processes them in turn. Each request takes 10ms to complete. This would make our primary key operation take 60ms to complete (6 requests at 10ms each). If 20 additional users issue lock requests immediately after the lock was granted to one user an additional 200ms would pass before the first user could perform the next step. If the clients issued lock retries in a tight loop this would add even more time to the procedure.

Of course, in the real world these simple operations can take far less time and the server is capable of processing multiple requests simultaneously. However, the fact remains that many calls to the same record can delay further operations on that record. This can be reduced significantly by moving the operation into a stored procedure. Since the stored procedure is run entirely on the server, thus a single client request, the lock owner holds onto a worker thread until the entire operation is completed. The thread does not have to wait while other clients are trying to lock the same record it can continue on to the next step. Thus there is no delay in completing the process and returning the result to the client.

You will still have to have some type of mechanism within your stored procedure to retry if the lock fails. However, the time it takes to obtain the key value from the table should remain relatively constant regardless of how many users are attempting the same operation at a time.

No comments: