Booking the last 2 seats at the theatre - an alternative way of explaining ACID transactions . . .

How to book two seats when there’s only a row of three in a cinema or theatre.

(Or ‘An alternative and obscure way of explaining ACID Transaction Processing’) 

"There are ways around that!" - David Lightman, Wargames.

You go to make a theatre booking. It’s busy and seats are ‘Selling fast! Book now!’

There are 3 left for your favourite comedian/band/orchestra/throat singers – and they are together! You and your favourite person can go to see the show and sit together. Great!

The following is an anthropomorphised version of the ‘conversation’ you and your browser will be having with the theatre website: 

“L21 and L22 please”

“I’m sorry, you can’t do that – that would leave L23 free which is a single seat and they are difficult to sell.”

“That’s your problem isn’t it? There are 3 left. I want two of them. Is it harder to sell one seat or three?”

“We have a ‘no single seats left’ policy. Do you not have another friend who would like to join you? It’s only £ker-ching for another ticket  . .”

Yes, you do have another friend – and it’s called another browser, or another computer.

How to solve the ‘no single seats left problem’ 

In browser (or computer) 1:

• Log onto the theatre website and select the 2 seats you want in the block of 3
• Wait just at the point before it gives you the ‘no single seats’ message.

(No one will want to book three, so you’re in no rush unless anyone else knows this trick)

Now, open up a new different browser/computer - this is browser/computer 2:

This is not a separate window or tab, as you may share the same cookies/session and it won’t work. e.g. if you started in Firefox, open Chrome or Safari for this 2^nd part.

(Do NOT log in to the theatre website with your account or userid/password for this part – you must buy tickets as a ‘visitor’ or ‘unregistered user’)

• In the 2^nd browser (or computer) , select the 1 ‘other’ seat in your row of three. 
• Important: Do not pay for the seat. Do not log in. Do not enter your credit card.
• Get as far as the ‘Your seat has been held for 15 minutes’ (or whatever) screen.

**REPEAT: DO NOT BUY ANYTHING AT THIS POINT IN BROWSER 2**

Go back to browser/computer 1:

• Click ‘select/buy my seats’ on your original selection – and you’re in!
• You are able to select the seats now, even though you couldn't before.You might need to refresh and re-select them on some sites, but it should work.
• You can now pay at your leisure, book meals, donate to the restoration fund, sign up to mailing lists and all the other fun things you can do on theatre websites.

 Go back to browser/computer 2:

• Leave the seat selection/reservation on browser/computer 2 to expire.
• (Just close the 2^nd browser, or cllck ‘release tickets’ if there is such a button on the screen.) 

Someone else can now buy the ticket. You’re not ‘locking’ that seat for the performance. It’s still available to be sold by the theatre after the 15 minute ‘reservation’ expires. If it’s such a busy show, it’ll sell anyway.

Why does this work?

The theatre website sees you using the 2^nd browser as someone else trying to buy the single seat.

They block the seat out for 15 minutes for them to buy it.

No one else can buy it during this period. As far as the theatre is concerned, that seat is sold and they now only have 2 left.

To prove this, after you ‘reserve’ the single seat in browser 2, if you go back and search the site in browser 1 for tickets, you will see that there are now only 2 left, not three. You can’t ‘see’ the seat the ‘other person’ is in the process of buying.

(This is the reason you ‘cue up’ the 1^st browser with your seat selection ready to go in the instructions above– anyone else looking at the same time will see the two free seats and might grab them before you do)

If some site-developer wises up to this loophole and detects that there are two browsers on the same computer (maybe by checking your IP address rather than a session cookie), simply use your phone to ‘block out’ the single seat you don’t want. Your mobile provider will have a different IP address range to your land-line/ADSL/Cable ISP and you will still look like a different person.

Enjoy the show. You’re welcome.

What does this have to do with ACID/XA transactions???

(The following refers to 'database ACID transactions' this is equally applicable to other ACID/XA transaction systems such as MQ/Messaging, Application Servers such as IBM WebSphere, Mainframe transaction systems such as CICS etc etc)

The ‘reserving the seat for 15 minutes’ is a ‘soft transaction lock’ i.e. putting a marker on that seat to say that it is selected and locked for update and that no-one else can update it (book the seat), or even see that seat to select it.

This is how database row updates work under ACID (Wikipedia) transactions. When you select for update, the rows you want to update are locked until you commit the transaction. Most databases won’t let you lock for 15 minutes though. They do, however, have lock timeouts and will unlock the row (give back the seat) if you take too long!

The ‘I’ in ACID stands for ‘Isolation’. This means that if user 1 is updating the rows in the database, user 2 can’t see those updates until user 1’s transaction is committed.

This is the same as you not being able to see the single seat in the theatre when the ‘someone else’ is booking it.

This does give us a problem in databases. If user 1 has a transaction lock on row 10 and user 2 tries to read row 10, what does user 2 see?

This depends on the transaction read settings in the client and DB engine.

Option 1: User 2 sees the row as it was before user 2 started their transaction

Option 2: User 2 gets a ‘This row is locked’ error. More likely, user 2’s request will wait until user 1’s update has finished. In computer speeds this is rarely more than a few seconds (normally it’s a few milliseconds). After that, they will see the row after user 1 has made their updates.