Multiuser Databases

In today’s world, there are thousands of people making changes to a database simultaneously. How will the report be handled when numbers are changing?

Locking

When some portion of the database is locked, any other users modify (or possibly read) that data must wait until the lock has been released. Two locking strategies:

Database writers must request and receive from the server a write lock to modify data, and database readers must request and receive from the server a read lock to query data. While multiple users can read data simultaneously, only one write lock is given out at a time for each table (or portion thereof), and read requests are blocked until the write lock is released.

Database writers must request and receive from the server a write lock to modify data, but readers do not need any type of lock to query data. Instead, the server ensures that a reader sees a consistent view of the data (the data seems the same even though other users may be making modifications) from the time her query begins until her query has finished. This approach is known as versioning.

Can lead to long waiting time

Long-running queries cannot see the modified data fast enough

Microsoft SQL Server and MySQL

Oracle and MySQL

Lock Granularities

Table locks

Page locks

Row locks

Keep multiple users from modifying data in the same table simultaneously

It takes very little bookkeeping to lock entire tables, but this approach quickly yields unacceptable wait times as the number of users increases

Keep multiple users from modifying data on the same page (a page is a segment of memory generally in the range of 2 KB to 16 KB) of a table simultaneously

Keep multiple users from modifying the same row in a table simultaneously

Row locking takes quite a bit more bookkeeping, but it allows many users to modify the same table as long as they are interested in different rows.

Microsoft SQL Server uses page, row, and table locking and will escalate locks level under certain circumstances. MySQL also uses all 3 types of lock. Oracle uses only row locking.

What Is a Transaction?

The transaction, which is a device for grouping together multiple SQL statements such that either all or none of the statements succeed (A property known as atomicity. In database systems, atomicity is one of the ACID transaction properties).

• One of the tasks that a database server must complete before coming online is to find any incomplete transactions that were underway when the server shut down and roll them back.
• The database server must reapply the changes from your transaction when the server is restarted, in case of a commit was issued but the server shuts down before the change was applied.

Starting a Transaction

Two ways:

• An active transaction is always associated with a database session and the server will begin a new transaction automatically when a transaction ends.
• To begin a transaction, you must first issue a command. Any individual SQL statements is indepenpent of one another and will autocommit.