hi guys for the first time ever i'm going to interview gorifen and that to a mock system design interview he is known for it i know but let's see how does he handle the pressure because the question is a bit complex let's just see how does he do it let's get started thanks kirti for inviting me i hope i'm able to solve it today i have prepared using of course interview ready dot io you can use the coupon code kirti for preparing for your system design interviews we'll see you there soon so let's get started with the interview carla you have to design irctc irctc okay okay i'm already thrown off but okay i'll i'll try my best this is a train booking website which okay so in case you are looking to book a seat in a train for a particular date on a particular train also then this is a website that we are going to be using okay that is i r c t c okay indian railways something i think ticket something something so okay so um i'm i'm getting a few ideas here firstly okay we need to have some sort of a schedule a rough schedule of the trains which are going from one place to another okay the second thing that is coming in my mind is the first feature basically is to book a single seat in a given train okay okay so yeah if i let's say i have a specific train with the train id it has a uh time also when it's going from one place to another a person wants to book a train from a particular location uh for this train uh and then we have to tell them whether that seat is available or not so for now i've just kept saying single seat eventually we'll have to give them something like multiple seats to book okay okay now each train i mean if i look into a train it's got a route so it goes from a city a to b to c it also has a arrangement of seats so seating arrangement is uh where are the seats located so if i want to give them a ui where they can click on this place and then click here so you can click a range of seats or you know multiple seats there okay what else so the route is a mixture of destination or place i would say what do they call it in seattle i mean a city a place a stop yeah a stop it stops here at a particular time okay okay that would be defining the route of a particular train and maybe your train can change routes depending on the dates so then for one train we have multiple dates an infinite calendar let's assume or let's continue the calendar to three months for the next three months this is the maximum time you can three month advance booking okay this is the this is the maximum time i'm going to allow people to come on the website choose a particular train in which they want to book a particular seat for a particular day okay okay also i need to be able to give them a list of trains which go from their source to destination for the dates that they have chosen so if they give me a ui search query where they say that from this date to this date i need to go from here to here i should be able to provide them a list of trains with the with the timings and you know relevant details for that okay now comes the interesting bit which i was dealing purposely uh i don't know kiriti how much you'll score me on this but okay there can be concurrent people i mean there can be multiple people in parallel who are trying to book the same seat so right there is uh parallel seat booking right right this is a interesting thing because if they are booking let's say different seats i mean i can't yeah the general idea before we get deep into this is that if i have a seating arrangement and people can select any of these seats i have to allow people to go and book some seats while the others are looking so there are certain people who are there multiple people who are involved with the transaction but only one person should come off come out of it with the with one seat and i need to give some sort of atomicity guarantee also i think because if you say i want a range of these seats and somebody before that comes and books this uh eventually if there is a mistake at least i should be able to roll back and give them back their money so i can make this manual also where i go and give them money but it would be much better if the system handles this i mean i can i i should ask you sorry kitty is it okay if we manually give back money let's get back to concurrency at first like uh finish all the requirements is this all the requirements like so okay okay yeah and in case of cancellation so i'll just note the last one down in case of cancellation um there should be no booking or rather i mean if the person clicks on the cancel button or if they try to uh wrong booking multiple seats being booked by the same person then there should be no booking so then the money is not debited and things are fine okay at the moment this is a schedule of trains and then search will be in this these are the things that i can think of uh yeah you want to search based on the time and stuff so these are the things that i can think of is there anything else also that you would like me to expand on no i i was actually waiting for you to mention search because that is the first thing that we do okay so yeah so you have mentioned that so search view details book ticket uh these are the three main things that i'm looking for yeah yeah apart from this i guess like i'm just thinking about it now so when i book the ticket i actually get an email notification and a option to download the ticket but um is that okay if we skip it now yeah let's just skip notifications okay okay good stuff uh okay amongst these like you said there are three requirements that you're specifically looking into one is the search feature one is the you know booking a seat is pretty important and which includes this includes the parallel seat booking use case and calculation also okay and then three month booking advance is just one thing that we have for limiting things so i'll just skip this part right right okay and then we have this so we have effectively three major features that we we can discuss and this is something that you want to focus on right these three things yes yeah okay okay let's uh i think let's start with the schedule of trains i maybe it will or just give me a minute let me think about what should i touch on first what is the core of the system what is the heart of the system okay okay so a train uh has a seating arrangement and it has so would you like me to do the high level design of this or do you want me to think about the low level design a single component and code that let's do a high level design but i would like to know how you would uh you know keep your data like i want to know that like how are you going to perform the search operation okay okay so fine i'll for that firstly i'll just think about the train as a component a train service which has relevant details about a train i'll think about that service first and then from there i think i can expand outwards so that it's easier for the system to to become more clear so okay so there's a i'll just note this down okay parallel cancellation you also need a single seat or multiple seats and then you need to schedule trains okay okay so firstly i need some sort of a service which gives me train schedules so there is a train service let's say in this from the back end people can add trains which uh have routes those routes have destinations and their uh their arrival times uh so okay what are those things so for every train i need an id of the train i need a seating arrangement let me assume that this is uh not changing is that okay for me to assume there's a seating arrangement that doesn't change yeah okay okay uh now the interesting thing is do i want the seating arrangement as single seats which can be like let's say if there's a thousand seats on the train or 500 seats on the train then do i keep them all as individual seats or do i want to keep them as a single json i would personally and let's assume that we don't worry about how the seats are structured their arrangement let's just say that we know that okay these number of seeds are present and we want to cook like depending on availability okay let's not worry about the arrangement okay so we are not going to allow them to choose a particular seed we are just going to give them a seat randomly yeah let's take it for now and maybe we can discuss later how we could have yeah i think that's a that's a great idea okay i think in booking yeah booking.com uh yeah they have the arrangement also so okay uh instead i can have the i still probably need the the seats to be allotted to different people so okay instead of the seating arrangement now i'll have number of seats and i'll have the uh the seats like the names of the seats so that if a person is allotted a seat i can just randomly pick one and give so seat names okay now apart from this for a particular date a train will have a particular route but it can also extend to the next day and it can also come from the previous day so what i'm seeing is that instead of thinking of a route i should note down maybe the start and end destinations of every train uh for the foreseeable future so what i'm getting here is that this is like a schedule for a particular train id this is the source this is the destination this is the expected departure time and this is the expected arrival time um and then you know if we want to keep something for history or auditing then we can keep a very similar one to schedule where the real times are noted down so actually i have a question yeah so a train has several stops yeah so when you mean source and destination you mean they're starting at the ending point and you're not worrying about the stops in between so uh no i what i'm doing here is that i'm actually uh let's take the as let's assume there's a train one and it goes from a to b to c in times one one o'clock to two o'clock to three o'clock in which case i'll be storing it like this i'll say this is the source the destination is b and then the next the source will become b and destination is c and the estimated departure time here is one over here it is two and here it is two and over here it is three so if if a train needs to take a 10 minute break here then it can do that 210 is the leave time and three o'clock will be the arrival time okay so if you if someone needs to go from a to c uh you'll first go from a to b and then from b to c yeah now if i'm thinking i'm just wondering like how do i make this query uh so because there are going to be a lot of people who are going to say for this date between this date to this date give me the trains which are going from here to here so exactly okay uh first version you are storing it in a yeah only so right now it looks like a relational db is a good idea uh because why because i don't want to put the whole block of uh no sequel over here it's not really benefiting me much i mean i don't see i i don't see any relations in the database right now except that there are some range queries which are happening okay so firstly uh i'll just i'll just expand on this there is a what kind of queries are being done on this database there is a time range query that's being done uh okay and there is a kind of a transitive relation between source to destination where source is random and destination is random right okay so the way i can answer this query is firstly within a given time range that is the estimated departure time i can have an index on this i can do this in nosql also but currently i am going for sql because right why am i going for sql is the scale really that much that i don't think a sql database can handle it read and write operations right operations are rare in this because a train has a schedule but it's not like it's going to have millions of records every day it's just going to have like maybe 50 destinations and even if we have yeah even if we have let's say 10 000 trains 50 destinations 50 000 points we can even keep that in memory i think uh now the thing is uh yeah so in a normal interview i wouldn't have done this but uh since this is for youtube channel you assumed some assumptions right like these number of trains these number of stations yeah can we discuss that as well yeah yeah sure sure okay so firstly uh okay so let's uh so one thousand trains or ten thousand trains yeah india okay let's let's uh i'm not too sure how many are there uh per state how many cities do we have or let me look at the population one billion people maybe point zero one percent travel in a train per day so we have something like one less than one million which is uh about a hundred thousand people are traveling by train every day if a train can take on average 500 people uh so that is these are assumptions that i'm making it might be totally wrong also but i am just assuming that indians like trains but but then they're traveling at this rate so if there's 500 number of people traveling per train and i'm assuming total number of people traveling uh okay from start to destination this is a very small number according to me but i'm roughly taking the estimate okay there's zeros here and there's two zero zero so 200 trains per day i think this is a very small number i think we'll definitely have around around a thousand trains which are traveling in india it's a pretty big country so i'll maybe i'll just uh correct my estimate to 1000 and maybe on diwali and everything people want to travel a lot so in the worst case i'm taking 10 000 just to be sure and in 10 000 if we say you have 50 destinations per day then we have about 15 to 10 000 which is 5 lakh or 500 000 uh points in this table which is okay yeah which should be fine yeah so i have done my homework as an interviewer so let me tell you the real numbers sure so there are 13 000 trains that go every day roughly 13 000 okay so maybe we can take it to 15k right now like yeah so i'll just say okay 30 000 you said three one three oh wow that's that's pretty good okay that's that's not so bad for me uh and uh maybe more than 50 destinations uh a train stops every one hour approximately yeah yeah i think uh every half an hour so 13 000 i'll just take this as 15k like you said uh 1.5 million is it yeah right i've just no i don't know if this will help you but in total there are five 7500 stations like estimate seven point five to eight k stations okay you can use this estimate if you want okay out of the 7500 i'm assuming that a train stops every half an hour this is a long distance train so around 50 destinations around 48 destinations per day so that would be around 50 and then if we take 13 000 then we get roughly uh there's four zeros here and this is 65 so 65 and then there's four zeros so this should be 650 000 which is also manageable yeah we one million records uh a sql database can handle the reason i'm going for this is because usually sql databases are a bit cheaper to handle and also the data wrangling that we can do with this my assumption is that there is going to be certain searches which are specific to certain fields although we can create uh indexes in a nosql database also at the moment a sql database index is a little easier for me to explain i'm i'm assuming that there's a bi no not a bi t or what do they call it b plus tree on this on this database here i'm sorry for asking so many questions but another question so you said uh one million uh is okay for sql so uh how did you decide that like how many entries do you think would be okay so that we can use sql yeah so in my personal experience we have had around 10 million records which which were summaries of particular trips being stored in sql but if i take this entry at the moment this strain id is going to be let's say eight bytes eight bytes long um this source and destination are going to be characters around 32 characters even if we have 64 let's say so that will be 64 into two bytes uh then we have the estimated these are basically date times so even if i take them as eight bytes again that is eight into two uh event like in total this turns out to be eight plus sixteen plus 128 so roughly this is 150 characters or 150 bytes so once we do this we get 6.5 into 10 raised to power 4 here into 10 raised to the power 5 into 150 so this becomes 10 raised to the power 7 and we have something like 10 over there so i would say this is 10 raised to the power 8. these many bytes which means this is roughly around a 0.1 gb so we should be okay 100 mb is totally manageable we can actually i think we can put this in memory also all this stuff but i i won't be too quick because there might be other stuff also that you want to put but yeah and therefore i'll go for a simple database maybe an in-memory database which is being persisted in the back by a simple sql database okay so okay is this would you are you are you comfortable with this part okay cool um okay but i am not comfortable with the rest of the parts let me let me try to think about uh how do i you know do a range query okay so firstly uh you i get a start and end time so people when they search for a destination under and a starting position they tell me when they want to start and when they want to reach maybe so end time so or they can also say that between these two times i want to start so in either case what's going to happen is as i have indexes on these two columns when i'm searching i can use only one index on a sql database so i'm going to be using one of these to take all the trains which have a let's say estimated departure time after start and then i'm going to be filtering out all the trains which have a uh estimated arrival time after end why after starting after the end time so um what i want is all trains between start and end so the time of start has to be after start and the time of end has to be before end or equal to okay so in that case i'm i'm taking all the once let's say the person says i want to leave after two o'clock not before that so in this index i'm going to find this entry and maybe uh i mean if i keep these sorted what's going to happen is i'll get a end entry also so effectively i'm doing a binary search i'm getting these entries and because i've done a binary search on one row i cannot do it on multiple rows i'll need to take these columns based on the end time which has to be less than the required end time so it's let's say four o'clock so i'll be filtering out all the rows which have end time greater than four o'clock i cannot uh reach there after four o'clock that's the requirement so i'll pick these up and then i will uh i'll take these rows let's say these are the rows i have uh you will also be giving me a source and destination this is interesting should i i also want to take one source let's say b and a destination let's say d so this is uh okay just one minute i have to think about this take your time so one brute force way to do this is to first check in all the source rows whether there is any uh starting i mean or whether there is any source which matches these so once i have that i take those rows let's say this is one of the rules so i i said i want to start from b maybe there's another one over here with b so i take those rows only and then i start checking for if for this train id let's say this is a train id number two if for this train id there is a destination that is coming okay that is the general idea how do i do this programmatically i am taking source rows and then i am i am looking for a destination here in that in those rows okay okay one of the things that i can do is i am also having a destination so if you give me the destination let's say d which is where i want to go then i take the source rows and the destination rows and then i try to do a bfs sort of thing yeah a breadth-first search here might help because from here where am i going and then from there where am i going is is the idea okay so but i yeah yes absolutely i'm creating a list here so from i started from a and then i went to b from b i have to somehow figure out where i'm going so that has to be c so but i i don't know if i can just look at the estimated arrival times and everything and do that uh what i just mentioned now is that i can get the source to be if i get a source query then i get all the rows uh from b so all the trains which are going to be i to be at b uh are already with me now i i can start plotting paths of the different trains from b to different destinations and eventually if they end up in d then i know that is one possible route but this looks like a really bad idea because i think we are doing a lot of graph creation and search also uh but i mean it's a it's a million rules here gotta have a question since we are forming a tree slash graph so uh there are some graph page data structure that uh db is also right like yeah not just yeah that's a so do you think that's good i'm just asking like this is for a discussion yeah no i think that's a good idea from b to d and also the edges can store the times that there is a root so effectively the source and destination will be not required they'll become nodes the train id arrival time and departure time will become nodes okay okay that's a very good point i think that's a excellent point i have to what i am worried about uh is that what if the uh you know the amplification like from b there are let's say millions of roots because it's happening every day so yeah from there i mean how do i choose only those ones within a particular time interval uh that is my concern yeah then obviously there is no right and wrong uh this is just for discussion whether it will it be a right thing or not we can do this no i think uh i think it's a good idea i think maybe if i think uh or maybe like there might be there might be people who can solve this part but at the moment like i'm a little concerned about that and maybe maybe it's already solved but i'm not sure i'm not very comfortable with grad graph databases that's that's probably the reason why so on this schedule table that you'll be having is this the only query that will happen the search query or is there any other query also for which you should optimize so this is one of the queries uh i'm assuming that okay let's we create a graph or something we can keep a bi-directional one also so we can quickly converge and because the number of rows are not very high though we can probably manage creating it in memory and then you know finding the optimal route and we can also cache things a bit although i don't know whether the start and end time we can cache that way right but okay um okay uh now i'm sorry what is the question is this the only query or are there any other queries that's going to happen like what do you think yeah in the train service people want to know from this is one common query which is what they do in the when they just join the other thing that they want to do is they want to book a seat for a train [Music] yeah so in this schedule i i don't think they will be doing any other query oh yeah there is another query for this train where all is it going and at what times so they want to get the whole route of the train for that day that could be one thing so in that case uh the query is a bit better for us we have an index on this we get the time in which they're looking so another api which is get schedule for a given train id uh and and a given date so with this date we can actually choose the number of rows that we want we actually select based on this date and then the id of the train we can filter out over here and then we can give them a sort of a route that it goes from here to here with the times i want to make your life a bit harder and ask you that there are also trains that have like uh roots so big that they might be like two three two day three day travel right so how do you handle multiple dates sort of thing since you mentioned the date i was not thinking of it but since you mentioned so uh that's that's a really good point so you we might have not for a particular date but we might have a two i mean from timestamp and two timestamp also in which case we still use the the same index estimated departure time we get the rows that are relevant to us based on this range so it has to be the time stamp has to be greater than front time stamp and less than two times stamp after we have those rows we filter out on the the train the train id and um then we can in the ui itself we can create a route and show them that okay from this time to this time the train is traveling from a to b and so on yeah these are the two types of queries uh the first query is for getting the uh the trains in a particular range and the second one is getting the schedule okay any other type of query that a train does i don't think so i i think it is either specific to a train or specific to a to a route yeah okay okay okay so is that is that fine kitty do you think it's no that's okay we can get to the booking part now yeah okay okay sorry you know in a normal interview i wouldn't be addressing people by their first name but yeah what would you say [Music] okay so we have a train uh and in this train we need to book seats so there's multiple people who are going to be booking seats and uh the seat name i'm assuming can be anything we can give them any random ones so they're effectively like tokens and people are like if there's 60 seats and 60 tokens are consumed the seats are over okay okay now how do we do this so i have to give them for every source destination i have to give them the number of seats available and if they tell me that i am booking from mumbai to delhi then all the seats in between also have to be booked and uh yeah okay all the seats in between also have to be booked and on top of that what i need to do is i need to make sure that the seat name that i give them is the same for all of them okay okay why irctc is so much more complex than flight or any other system is because uh people can leave in between the station and then we have to see someone else might get like uh take that seat after two stations or after one station so we have to find the optimized way to do it right so yeah that's that's absolutely right here okay now what we need to do here is take these number of seats and give them as a token okay but i can't give them as a token i have to is it possible that a person who's booking from mumbai to delhi has a seat change in between or do we have to give them the same seat throughout the journey or should we like should we take into consideration that if it is possible for them to get a seat such that they will have to change their seat that's a good question uh let's say we don't give like if one one entire route is not possible we say that okay it is not possible then the person has to use his or her brain and see that okay i can book one ticket till like a midway station and then another ticket from there let's say we don't support that for now okay okay so um okay oh i'm assuming that uh the tickets are booked but the seat numbers are allotted after a few days so what will happen now is that uh okay and cancellations for now i'm keeping aside uh it's a little complicated but let us say that i book a seat i don't get the seat number immediately because i know eventually i have to anywhere randomly a lot seats so two days before the train thing i'll do that okay now i'm getting a token bucket kind of algorithm here the general idea is that i have a counter some sort of an atomic counter which says that one second i'll just rub this off so if i have an atomic counter what's going to happen is let's say this is i mean we can't have an atomic counter in a database rather every time a person says that book a seat i have to i have to lock this row so that nobody else can read it concurrently and then i have to decrement that counter by one once this is a success i have to alert that seat to one person so seat names there has to be a ticket object also which says for this train um and uh this is the user who has booked the seat for this train this user has a seat yeah then it makes an entry and i'll keep this blank for now and the train id let's say is one user ids let's say one to three this person has a seat that would be one way that i ensure that there is no there is just one person booking one seat and if 60 such operations come 60 times there's a decrement i'm going to be taking the user ids putting them here in this table and eventually i'll have the 60 users who have booked seats yeah and then i can randomly alert them one of the seat names here okay this is a very basic one uh if from destination a to b what happens if it is from destination a to x okay yeah in that case this will probably not work for a train i have number of seats this is not enough information in this table so instead i need to also have from where to where so i guess i can remove seat names right now because i i don't have that use here if i'm allotting them randomly here it doesn't really matter for now i'll just say that this is a booking for a particular train with an id this is the source this is the destination and seats left so this is a counter okay why am i rushing for this maybe i should just consider let's say there is a person who wants to go from a to c i have given them source a b and b c i'm again going to be doing the same thing is it i'm going to be creating a graph and then decrementing the seats on all those rows okay let's take this use case a b c and d one person wants to book a seat from here and another person wants to book from here so in that case if i say that give me all the rows with abc and do a lock then this person will not be able to book the seat at the same time okay because i have taken a a lock on the number of seats okay is that fine with i mean if there's lots of people who are trying to book a seat this could be a problem let me go with the brute force approach for now the basic approach is we again create a graph we figure out okay there is a root from a to c i think another simple approach can be taken instead of us creating the graph every time in in memory we can have a root table also just to optimize i know that maybe this is not required but a root table which says what are the destinations that a train is going to so train id and this is the destination so train id 1 goes to a train id 1 goes to b c and d and then the date also here and what date does it have so if now i need to i don't need to create a graph in memory i can just say that are you having a start time a i mean do you do you go to a yes do you go to d yes oh okay fine there is a root between you two so if you are going from here to here to here to here these are the times these are always incrementing the times so i can make an index on this date and then i can say that if you're between a and d that's fine i mean once i pick these rows up if i find a source with a and a destination with d two rows then i'm sure that this strain is satisfying that card okay okay if if that works then what i need to do is uh if a person says book me all the book me one seed between a b and c or multiple seats i think it will be similar uh i have to see whether the train satisfies this or not okay uh once i get the train a person says book me a seat okay uh source and destination and then i say seats left when a person books a seat i have to also tell that over here there's only one seat so if if there are two seats total in the train um [Music] then i have to say that there is one seat remaining here one seat remaining and one seat remaining and two remaining here so when the second person comes to book they make this zero zero and one okay yeah okay now how do i uh how do i lock this okay when i so the brute force approach lock a b and c are three rows which have to be locked so let's say i log these three rows in the booking uh in the booking table so initially when the number of seats remaining there'll be identical table for booking for this so in that it's going to have number of seats i'm sorry is this getting confusing i can explain a little more if so you are basically checking that at least one seat is there for all of these stations is that right yeah yeah initially we have two two two two and then a person says book me from a to c so i say okay fine the range is this a to c i know you're going from a to c so i have just logged these three rows and now i'm going to decrement all these three values by one this is a exclusive lock so there is nobody can do anything here like when the second person comes and they say please book me seeds from b to d i say oh wait there are some rows that you're asking for which are locked i can't even read the rows right now uh let alone write so over here this will remain at 2 while this will become one one one then the lock will be released at which point the second query which is bcd will pick up this lock and it will say okay decrement the counters by one one one again it it has taken a lock so nobody else can do anything right now and eventually this is the the table that we'll have the number of seats for a particular train yeah one point since we decided that we are not going to uh support that you know the person has to change the seat uh just having the counter might not work we might have to like keep the seat uh like reference to particular seat because it is possible one person left another person came the counter will be in the end same uh but right okay let's assume for now that we this will uh work only if we allow one or two things that's true that's true no it is it is a problem if people are leaving and things one of the things that i can do as i isctc is say key within seven days if you want to cancel your ticket i don't charge you anything i will again increment this counter taking an exclusive lock i'll again make this one one and two which is fine basically b to d is booked by one person so that's the reason why instead of two it is one and now um you know in the last 12 hours i will not allow you to make any changes so basically 12 hours before i'll send you the seat number uh along with the guarantee that now you're not going to get a refund so even if you cancel for me basically you are in the train in the system i'll just mark you as cancelled but i won't be making any changes to the number of seats left okay just to give you one more case so if they were abcd okay one person is going from a to b one person is going from c to d now a person is booking from b to c okay so they will say one seat left from a to b okay uh just one second they are going from a to b and uh c to d and some person is going from b to c yes yes okay so now okay once it is left for uh like a b also once it is left for cd also but it is not necessary that bc has that one same seat remaining like get my yeah yeah okay so one person is going from a to b okay and then b two c and c two d yes absolutely all of them should get one seat but what i'm doing is i'm locking the rows and i'm reducing the number seats so i'm reducing them incorrectly i think if a person says a to c it should actually be two here so if initially when the counter is two for all if when a person says a to c effectively they are sitting in the train when a is departing and b is departing and when they are seeing from b to c they are making this 0 when b is departing and c is departing so it should have been this i was incorrectly showing it as that now yeah you're right so from a to b now if i take the same scenario 2 2 2 i just decrement this to 1 a to b b to c so you are here but this is the destination so i don't count this destination and c to d yeah c to d so that is that is one here and this is two okay that actually uh that's a good point because then i don't need to take a lock on three rows i need to take a lock on the first two rows only when i'm booking from a to c can you explain that point once more spoiler audience yeah so so uh if you're booking from any point a to x with multiple points in between then i don't need to when i'm taking a lock on the rows i'm not going to be including the destination row lock i'm just going to be taking the previous ones so for example when it is a to c i have a b and c so i'm going to be taking a lock on these two rows because from c anyway the seat has been freed up so in this case instead of making this instead of locking it like that i'm going to just lock these two rows decrement the counters to one one and then release the exclusive lock after which the other person comes god okay okay okay so that would be for once this has happened i mean this can be ignored i think and then we we have a ticket which is generated when a person okay uh okay okay a ticket was just generated so a ticket should also have a seat number which we'll decide later and then a source and destination so if a person cancels also we can just look at the source destination again come back here and then again increment the counts okay so that takes care of making bookings without contention uh that takes care of searching for train schedules um contention if there is a problem so i have just taken an exclusive lock which solves the problem but it is quite restrictive because nobody else can read or write those columns at the same time right so okay can i i can also do another thing called i can do something else called optimistic lock so what i did was a pessimistic lock basically i said that yeah i mean basically it just forced the lock one person goes through ten people one survives instead of that i can do optimistic lock which says try making the change if it fails then we'll see then roll back the change so the idea is pretty similar what we do is we first try to decrement these two from two 2 we try to make it 1 1 1 and just before making the change there is a hardware operation which checks whether uh it is still 2 2 or not so initially let's note down the version it is 2 2 we are trying to make it 1 1. somebody comes in between and changes it to one month so somebody else came let's say some other user they saw two two they quickly changed it to one one before we could finish our operation for some reason the ordering got messed up in that case if i have an optimistic lock i'm first going to see my version the older version 2 2 is in my hand i'm going to see the current version 1 1 and i'm going to say oh my god there's been a change i cannot trust the change i have made because the the original state has changed therefore i just failed the transaction and i can tell the user that uh sorry the try the try failed yeah the request field so i can do either of these two things i would be more comfortable with a pessimistic lock because it's a little easier to reason about and also i think this is a lot of wasteful resources that have to be you know continuously used there's going to be multiple read operations on the same table but potentially in some cases it's useful when the lock contention is very high in this case i think a pessimistic lock is is sort of reflecting my personality also in general but yeah great okay i think we are out of time now uh would you like to add any uh last minute things like any other yeah sure so what what is the role about and uh can you tell me a little bit more about the company right let's get to the feedback pad yeah thank you thank you i think i had almost passed but with this comment i've definitely failed but how do you think about the interview like this is a very complex thing yeah so honestly speaking like uh um was i able to finish the things that we were initially thinking about uh the contention this uh i can't remember what else was there was there anything else no we we thought about search we thought about booking and cancelling these three were the main things and roughly yeah we were able to cover most of them okay okay i'll just go and build irctc anyway it's too expensive so i'll just get some trains and build this site but okay cool uh coming to the feedback part from my end they could have been uh like especially the requirements but this system is huge okay we could have at least mentioned that okay we are not talking about these things first artist if i have to be a very picky interviewer then uh isatc is not just stream booking system we could have mentioned that that you know we are going to focus only on train booking and not on anything else if i have to be a picky interviewer but yeah isctc is oh yeah it books hotels yeah so yeah it's okay uh we got into the estimates a bit later like we should have done it a bit earlier maybe before getting into the data part that's one thing right just to be safe we did not discuss a lot of requirements like tatkal and you know there are different coaches in train like um class one a to a then when we book tickets the prices vary according to the coaches according to state numbers like right right right like i wouldn't have any way asked you to focus on those but uh like when we are talking about requirements it's always a good thing to mention these things so just saying yeah definitely totally yeah yeah right i i probably haven't booked for a while but it totally makes sense to yeah there's multiple coaches and the other one was very important i think uh yeah yeah this okay okay please go on yeah yeah and estimates i uh i'm glad that you were able to take close enough estimates but uh your life would have been much easier if you could have just asked me what are the numbers that you're looking for like you know okay okay yeah like because you are the system design expert so actually i want to ask you this what is better like should we uh estimate ourselves or should we ask the interviewer what do you think is better to do in my personal experience uh people guess but yes if the interviewer says this is the exact number then it's awesome because then you know you actually designed the system as close to the thing itself uh you had numbers which was really nice probably i should have asked you in my experience i've never asked an interviewer maybe that's a problem uh maybe i should ask that if do you have any numbers which i can use but yeah no i i think that's a that's a good thing to do in general if i am a little concerned uh i can always ask the interviewer who can search on the internet and maybe give me a one minute response or we can go ahead with the assumption if it's not very easily searchable right right we could at least discuss with the interviewer and like you know look for are these the numbers that you're looking for sort of thing at least right i am just being very very picky over here and giving a lot of feedback the intention no it's not which totally makes sense i think it will help improve also uh the thing that you mentioned about tatkal is super important it's like one of the most important things where people like scripts to book tickets so right yeah that would have been interesting to see like at tremendous load what actually happens uh i would probably still go for pessimistic locking but uh i am not entirely sure with the tremendous amount of load how do i like how do i stop all these users from booking at the same time so yeah i could say that you were very concerned about concurrency from the starting like there were a lot more things that we could have worried about like like you got into too much detail in the starting yourself but that's okay i i couldn't understand the excitement that yeah that is the most interesting challenge yes this okay i will be able to do it sort of thing yeah but this system in general is very complex then then there are also passenger trains and you know luggage trains and there is like is he also supports catering there are a lot of things like i was ready in my head that okay if you ask me these questions what do we have to support what if we don't have to support but yeah in 45 minutes there's very limited things that can happen yeah i know it it has hotels also that you can book i i just remembered the ads yeah but yeah right okay i think in general it was a good experience do you have any feedback for me as an interviewer was i poking too much no i think you were very like you gave me a lot of leeway which helped me build a system i think in general i could have asked maybe more questions or try to involve you more in the discussion than make it a explanation of my own uh in a real interview i think i would be um yeah maybe i should involve the interviewer more like i'm not there to show how to show what i think about irctc i'm there actually to solve the problem that is being asked so i should have definitely i think uh i think when it came to requirements i was i was careful but after that what happened is i started with my own solutions um and then eventually there was that hey do you agree with this or not this is that i think can be done better i think we can get more into the discussion side uh in a real interview and we can also focus on what the interview is thinking about i was interested in concurrency like you said uh in a real interview i'm sure you would have been a little irritated because communication skills are missing here because i'm not i'm not able to think about what the customer's problem is i'm actually thinking about what my problem is but i understand you're a teacher maybe you should get interviewed by me more frequently totally totally totally up for these lessons but okay cool great thank you so much for doing this i hope the audience likes it as much as i do sure thank you kitty for inviting and it's been a wonderful experience thanks thank you i hope you enjoyed the interview as much as we did please don't forget to like share and subscribe it will mean a lot to me thank you