The best way to design a Reservation based table

Question

One of my Clients has a reservation based system. Similar to air lines. Running on MS SQL 2005.

The way the previous company has designed it is to create an allocation as a set of rows.

Simple Example Being:

AllocationId | SeatNumber | IsSold

1234         | A01        | 0

1234         | A02        | 0

In the process of selling a seat the system will establish an update lock on the table.

We have a problem at the moment where the locking process is running slow and we are looking at ways to speed it up.

The table is already efficiently index, so we are looking at a hardware solution to speed up the process. The table is about 5 mil active rows and sits on a RAID 50 SAS array.

I am assuming hard disk seek time is going to be the limiting factor in speeding up update locks when you have 5mil rows and are updating 2-5 rows at a time (I could be wrong).

I've herd about people using index partition over several disk arrays, has anyone had similar experiences with trying to speed up locking? can anyone give me some advise onto a possible solution on what hardware might be able to be upgraded or what technology we can take advantage of in order to speed up the update locks (without moving to a cluster)?

Answer 1

I don't think you'd getting anything out of table partitioning -- the only improvement you'd get would be in fewer disk reads from a smaller (shorter) index trees (each read will hit each level of the index at least once, so the fewer levels the quicker the read.) However, I've got a table with a 4M+ row partition, indexed on 4 columns, net 10 byte key length. It fits in three index levels, with the topmost level 42.6% full. Assuming you had something similar, it seems reasonable that partitioning might only remove one level from the tree, and I doubt that's much of an improvement.

Some off the-cuff hardward ideas:

Raid 5 (and 50) can be slower on writes, because of the parity calculation. Not an issue (or so I'm told) if the disk I/O cache is large enough to handle the workload, but if that's flooded you might want to look at raid 10.

Partition the table across multiple drive arrays. Take two (or more) Raid arrays, distribute the table across the volumes[files/file groups, with or without table partitioning or partitioned views], and you've got twice the disk I/O speed, depending on where the data lies relative to the queries retrieving it. (If everythings on array #1 and array #2 is idle, you've gained nothing.)

Worst case, there's probably leading edge or bleeding edge technology out there that will blow your socks off. If it's critical to your business and you've got the budget, might be worth some serious research.

Answer 2

How long is the update lock hold for?

Why is the lock on the “table” not just the “rows” being sold?

If the lock is hold for more then a faction of a second that is likely to be your problem. SqlServer does not like you holding locks while users fill in web forms etc.

With SqlServer, you have to implement a “shopping cart” yourself, by temporary reserving the seat until the user pays for it. E.g add a “IsReserved” and “ReservedAt” colunn, then any seats that has been reserved for more then n minutes should be automatically unreserved.

This is a hard problem, as a shopper does not expect a seat that is in stock to be sold to someone else where he is checking out. However you don’t know if the shopper will ever complete the checkout. So how do you show it on a UI. Think about having a look at what other booking websites do then copy one that your users already know how to use.

(Oracle can sometimes cope with lock being kept for a long time, but even Oracle is a lot faster and happier if you keep your locking short.)

Answer 3

I would first try to figure out why the you are locking the table rather than just a row. One thing to check out is the Execution plan of the Update statement to see what Indexes it causes to be updated and then make sure that row_level_lock and page_level_lock are enabled on those indexes. You can do so with the following statement.

Select allow_row_locks, allow_page_locks from sys.indexes where name = 'IndexNameHere'

Answer 4

One last try…

It is clear that there are too many locks hold for too long.

Once the system starts slowing down due to too many locks there is no point in starting more transactions.

Therefore you should benchmark the system to find out the optimal number of currant transaction, then use some queue system (or otherwise) to limit the number of currant transaction. Sql Server may have some setting (number of active connections etc) to help, otherwise you will have to write this in your application code.

Oracle is good at allowing reads to bypass writes, however SqlServer is not as standared...

Therefore I would split the stored proc to use two transactions, the first transaction should just:

• be a SNAPSHOT (or READ UNCOMMITTED) transaction
• find the “Id” of the rows for the seats you wish to sell.
• You should then commit (or abort) this transaction,

and use a 2nd (hopefully very short) transaction that

• Most likcly is READ COMMITTED, (or maybe SERIALIZABLE)
• Selects each row for update (use a locking hint)
• Check it has not been sold in the mean time (abort and start again if it has)
• Set the “IsSold” flag on the row

(You may be able to the above in a single update statement using “in”, and then check that the expected number of rows were updated)

Sorry sometimes you do need to understant what each time of transaction does and how locking works in detail.

If the table is smaller, then the update is shorter and the locks are hold for less time.

Therefore consider splitting the table:

• so you have a table that JUST contains “AllocationId” and “IsSold”.
• This table could be stored as a single btree (index organized table on AllocationId)
• As all the other indexes will be on the table that contrains the details of the seat, no indexes should be locked by the update.

Answer 5

Here are a few ideas:

1. Make sure your data and logs are on separate spindles, to maximize write performance.
2. Configure your drives to only use the first 30% or so for data, and have the remainder be for backups (minimize seek / random access times).
3. Use RAID 10 for the log volume; add more spindles as needed for performance (write performance is driven by the speed of the log)
4. Make sure your server has enough RAM. Ideally, everything needed for a transaction should be in memory before the transaction starts, to minimize lock times (consider pre-caching). There are a bunch of performance counters you can check for this.
5. Partitioning may help, but it depends a lot on the details of your app and data...

I'm assuming that the T-SQL, indexes, transaction size, etc, have already been optimized.

In case it helps, I talk about this subject in detail in my book (including SSDs, disk array optimization, etc) -- Ultra-Fast ASP.NET.