A discussion of the inherent issues in managing concurrent accesses to a transactional database and the type of mechanisms which a RDBMS might use in defence of data integrity and to resolve potential deadlocks.

General Info

Concurrent access is something that all databases and dbas need to deal with daily. This is the case for internal databases but also the case for bases that deal with external users like the booking system mentioned in the question. Maintain data integrity is the primary goal of a database is slightly out of date, data is better than inaccurate data. 

 

Concurrent access is when two transactions are trying to accrue at the same time on a database. For example, two people booking the same year or downloading the same file at the same time. 

 

Mechanism 1

The first mechanism an RDBMS (relational database management system)  is the locking of the database. This can mean both users are locked out of the system but often it is the second user trying to access that gets locked out. This is most likely to happen when someone is trying to buy a unique product. 

 

The most common examples of this are the buying of a ticket for public transport, for a live musical/sporting event. Because there is a limited number of seats, priority needs to be given to users already taking part in the system. If someone is buying a ticket to sit in seat 27b at Old Trafford, for a select set of time, that seat will be locked for that user so that they can complete their purchase. During this set time the seat will appear already taken to any other users on the site. If the original user/customer decides not to make the purchase or their time elapses, the seat will become available again for selection. 

 

The second user is effective locked out of buying that particular seat. Concurrency always gives the priority to the first user which is determined by online timestamps. It follows the principle that consistent data is more important than the most up to date info as mentioned above. So in the off chance that two users select a seat at the exact same moment, they will both be locked out of the system and both purchases will be cancelled. In this case, the seat might become available again for someone else to buy. 

 

Mechanism 2

Another mechanism that could be done by a company like Ticketmaster is to limit the number of people who can access a database once. For example, only 1000 people can buy tickets of any seat at once, but once a user goes off the system another one can get on. Similar to how supermarkets are currently only allowing a certain amount f people in-the store at once. A reason why this might be done to keep the website up and running. This was especially a problem in the past when servers had their limits and it was very expensive to scale anymore. This is not so much of an issue in recent times due to the rise of cloud computing and services such as AWS and Google Cloud meaning databases are easily scalable.


Summary

In summary, this answered looked at 2 mechanisms that RDBMS have to solve deadlocks and maintain data integrity while usually practical examples when possible. 

Comments

Post a Comment

Popular posts from this blog

An explanation of SQL (Structure Query Language) Injections

SQL Injection – How they happen Like any other attack this is what happens when a hacker is trying to gain access to a database. This is when a hacker with the use of a query tries to gain access to the database. Essentially what the hacker is trying to do is guess how a query in this database is written and how the database schema must be designed. This would mean trying to figure out under what table things are organised and what tables are in the database in the first place. If one knows the structure of the tables and their attributes they may be able to get into the database and access information that should not be able to do like user’s passwords and email address or even debit/credit card details. A hacker may even remove the need for any password at all.    Using different HTTP requests and SQL queries a hacker can zero in on where and how the database is vulnerable. A search box is a useful tool to hack a database as when a user makes a search query the database is a...
Read more

10 things you notice after coming home from abroad

As I write this blog post, it is two months to the day since I returned to Ireland from my year in Germany. With that in mind, I decided to put a list together of what emotions, feelings, and realizations have hit me over that time. I wanted to write this piece about two weeks before starting University again, but at the time I found my mind was still abroad and it was too early to write such a blog post in a manner that would reflect reality.   1) Your body and mind will feel out of sink  When leaving your newly adopted home, you say many goodbyes followed by many hellos. You pack your things up and then unpack again while dealing with annoying and over-crowded airports in between. All of these actions would suggest that you are home for good, but it takes your brain time to fully understand that you’re not going back. Even if it is just that one per cent of you that is not willing to accept the fact yet.   2) Your heart won't break but it will be divided Before moving t...
Read more