MySQL database locks
A database lock is used when multiple users need to access the database at the same time. This stops shared resources, such as tables, from being corrupted. For example, if user A is changing the value of a field, the database management system must decide what to do with user B who also wants to update the value of the field at the same time. Locking comes in a number of forms and each form determines what will happen in situations where data is being used by multiple users.
MySQL uses row-level locking for InnoDB tables, table-level locking for MyISAM, Memory and Merge tables and page-level locking for DBD tables. Prior to version 5.5, MyISAM was the default storage engine for tables. From version 5.5 onwards, InnoDB is the default storage engine for tables.
MySQL has switched to implementing InnoDB as the default storage engine because InnoDB is now considered more appropriate in most situations than MyISAM. However, the choice which is made is wholly dependent on what the database is being designed to do and how it needs to perform. Each time we design a table, we must make a choice over which type of database locking we require for the table.
It should be remembered that there is no requirement to have each table in a database using the same storage engine; it may be appropriate to have some MyISAM tables and some InnoDB tables within the same database. Remember that the storage engine is specified for each table in MySQL and is specified when the table is created.
InnoDB tables allow multiple sessions and applications to read from and write to the same database table at the same time, without the need to make different sessions or applications wait for each other. MyISAM tables on the other hand acquire locks very quickly and allow for multiple sessions and applications to read data at the same time. However, MyISAM tables, which use table-locking rather than row-locking, force sessions wanting to write to the table to wait for exclusive access to the table. In practice, this means that the session needs to wait until earlier processes are completed. A read statement that takes a long time to run will prevent other sessions from updating the table making the other sessions appear slow. While the table is waiting to be updated, other sessions that issue read statements will queue up behind it. Applications which require concurrent read-writes from multiple sessions would, therefore, be better served using the InnoDB storage engine.
A big advantage of row-level locking is that a single row can be locked for a long time. Whilst this stops the row being updated by another process, other sessions can access data in other rows without waiting for the persistent row lock to be released. Row-level locking generates fewer locking conflicts when different sessions access different rows and also requires fewer changes if the database requires a rollback.
Row-level locking has some disadvantages too. Row-level locking requires more memory that table-level locks and is also slower than table locking when used on a large part of the table because of the number of locks which need to be acquired. Row-level locking is also slower if group by operations are done frequently or if the entire table requires scanning frequently.
For tables which use table-locking, the lock is always deadlock free. Deadlock avoidance is managed by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order.
Table write locks always take precedence over table read locks. If a write access is queued alongside a read access, the write access will be granted first. This means tables are never starved of data but it does mean that if there is heavy updating going on in the table, select queries are made to wait until there are no more updates. The MySQL variables Table_locks_immediate and Table_locks_waited indicate the number of times that table locks were granted immediately as requested or whether they were queued.
Generally speaking, table-level locks (and hence MyISAM tables) are considered superior to row-level locks in the following situations:
- Most statements for the table are reads, such as the databases which power websites where the content doesn’t change too often
- Statements for the table are a mix of reads and writes, where writes are updates or deletes for a single row that can be fetched with one key read – i.e. through using a where clause which uses the primary key value for the table
- Statements which require a high number of full table scans, or statements which use a group by clause
New Year: New Bus Prices
My daily commute requires me to take a 7.2 mile round trip on a First Leeds bus or to cycle 10.3 miles door to door. I’ve long ruled out using my car, because the price of city centre parking is so high.
Before today, I would get a single bus ticket costing £1.90 for each journey in and out of the city centre. That’s 52.7p per mile. By comparison, my bike probably costs me £30 per month in replacement parts to run for the 300 miles per month I use it, working out at 10p per mile.
From today, the day that First Leeds put up the cost of their bus prices, my daily bus commute will cost £2.00 for each journey in and out of the city centre. The price per mile is now 55.5p, or an increase of 5.3%. This seems reasonable and, albeit on the high side, roughly in line with inflation.
The “Day Saver” ticket, allowing unlimited travel for a whole day, used to cost £4.30 at peak times. From today, it now costs £4.60 – an increase of 7%. The “Green Zone” weekly ticket used to cost £12.00 per week and, from today, it’s now £13.00 per week – an increase of 8.3%.
I feel sorry for people having to travel further or more often and hence people who need to buy more expensive tickets. Passengers travelling out of the Green Zone would have paid £2.50 at peak times for their ticket. From today, the price is now £2.80 – an increase of 12%. Why is it that more expensive tickets have increased by a bigger amount?
Is the increase in price one of convenience for the bus company, rather than one linked purely to increased costs? It is clear that costs have increased over the 16 months since the last price increase but can someone explain why travelling further on a bus should be relatively more expensive today than it was yesterday? Surely we should be encouraging people to travel further on public transport? I notice the cheapest ticket, the £1.00 ticket which is a “Short Hop” ticket, remains unchanged today.
I don’t get the bus every day, as I cycle to work some days, and so I’ve always been unsure whether to buy a Green Zone weekly ticket. At the previous rates of £12.00 per week, or £1.90 per journey, I’d need to take 7 or more journeys for it to be cheaper to buy a Green Zone ticket. At the new rates of £13.00 per week, of £2.00 per journey, I still need to take 7 or more journeys for it to be cheaper to buy a Green Zone ticket.
A 5.3% increase on the £2.50 ticket would have led to the ticket being priced at £2.63 – which is clearly impractical – but would £2.60 have been a more difficult amount to manage by bus drivers than £2.70 or £2.80? Is £2.80 easier to manage because the change required from a whole pound is easier to manage on the move on the bus?
I guess my interest in this subject is that the price increases don’t appear to be uniform. With inflation at or around 5% for the last year, I can understand a 5% increase. What I can’t understand is how the £2.50 ticket now needs to cost 12% more? I’d be interested to know the reason as, from an uninformed perspective, it seems a little unfair, especially in a society where paying more usually leads to greater discounts. That said, for the thousands of people using the bus each day for their commute, they have little option but to pay the increase asked of them as it’s still more cost effective that using their car. I wonder how many people will start cycling to work instead? I think I’ll use my bike 12% more of the time.
Roof Box Fuel Economy
I’ve been driving around lately with a roof box on the car, to help with the demands Christmas can put on your free boot space. I’ve noticed that with a fully loaded roof box my mpg drops by around 5mpg. Is that a lot? Does it make a big difference?
The price of diesel at my local garage is currently 140.9p per litre. I usually get 55mpg when driving the 300 miles down the M1 to visit family. With a roof box on the car, this drops to 50mpg. At 55mpg, the fuel costs 11.6p per mile, at 140.9p per litre. At 50mpg and 140.9p per litre, the fuel costs 14.2p per mile. This may not seem like a massive difference in the pence per mile but, over 300 miles, the pences turn into pounds on the overall cost. The fuel cost for 300 miles at 11.6p per mile is £34.94 whereas at 14.2p per mile it is £42.70 – or a difference of £7.76 over the 300 miles.
£7.76 is still cheaper that getting those Christmas presents sent by courier so, even though my mpg drops, the roof box is a great idea. Bearing all this in mind though, it does make sense to take the roof box off the car when it’s not in use as that extra 5mpg can make a massive difference over a year.
For example, a 5mpg drop in fuel consumption over the 9,000 miles I’ll drive this year, would cost me £232.92 in extra fuel. That’s worth saving, especially in this economic climate, not least because it represents a saving of 165 litres of fuel I didn’t need to use. As well as saving some money, it has also probably saved a very small bit of the planet from damage. Probably a small section of Scotland, somewhere near Aberdeen.
Lottery Statistics: part 1
I’ve bought a Thunderball ticket tonight, in the hope of winning £500,000. I often play Thunderball because the chances of me winning the jackpot are better than playing the main Lotto game. But, how much better?
In the Thunderball game, you pick 5 numbers between 1 and 39 and a seperate, “Thunderball”, between 1 and 14. To win the jackpot you need to correctly choose the 5 main numbers drawn and the Thunderball. The chance of winning the jackpot is 1 in 8,060,598.
The chance of correctly choosing the first number drawn is 1 in 39 – about 2.5%. If that was all you had to do to win a prize, on average you’d win a prize 1 in 39 times you played the game. In order to win the jackpot though, you’ll also need to correctly choose the second number drawn. There are now only 38 numbers left in the draw machine. The probability of choosing the first and second numbers correctly is (1/39 x 1/38) or about 0.07%. That’s still not enough to win the jackpot, so we need to keep on going.
Using similar logic for the rest of the main numbers, we can multiply the individual probabilities together – 1/39 x 1/38 x 1/37 x 1/36 x 1/35, remembering that each time a ball is drawn it is not returned and so the next choice involves one less ball. The “running total” so far is 1/69,090,840 but this represents the probability that the 5 main numbers we’ve chosen match the 5 main balls drawn by the machine and they’re in the same order.
It doesn’t matter if a number we choose is drawn first or last but what is important is that our number is drawn at all. There are 120 different orders that our chosen numbers could be drawn from the machine and so we can multiply 1/69,090,840 by 120. This tells us that the chances of correctly choosing 5 numbers from a possible 39, assuming order isn’t important, is 1/575,757.
The last thing we need to win the jackpot is to also choose the correct Thunderball. The chance of doing this is 1 in 14 and so we must multiply 1/575,757 by 14 to get the probability of winning the jackpot. This tells us that the probability of winning the Thunderball jackpot is 1/8,060,598. Put another way, on “average”, you’d have to play Thunderball 8,060,598 times before you’d win the jackpot.
To win the main Lotto game, you have to correctly choose 6 numbers, from 1 to 49. The probability of winning is therefore 1 in 13,983,816.
The chances of winning the Thunderball jackpot are approximately 3/5ths that of winning the main Lotto game. That said, neither is particularly likely. Let me change my original statement. I often play Thunderball because the chances of me winning anything are better than playing the main Lotto game. I’l consider that statement in another post.
How much water does Hull’s The Deep have?
The Deep, Hull: 612 – 749 miles.
I’ve been to The Deep in Hull, which brands itself as the world’s only Submarium. In the main attraction you can see 7 species of shark alongside thousands of other sea creatures.
There was nothing mathematically significant about my trip today, other than the observation that the main attraction holds 2,500,000 litres of water. That’s the same amount of water as is held by an Olympic sized swimming pool, which is 50m long, 25m wide and 2m deep, or the amount of water I’d use in 44 years.
I’m due to leave for India in 2 days, where I’m expecting to be presented with a variety of random curried seafood that I’ve never experienced before. I hope that I haven’t just seen a relative of what makes it onto my plate whilst visiting the Deep today. Controversially, I had Shark’s fin soup in Malaysia a few years ago: i’ll pass on the chance to have it again.
Baby computing and a Baby train (06003)
Museum of Science and Industry, Manchester: 382 – 478 miles.
I headed over to Manchester today to go to the Museum of Science and Industry. As a kid, I visited a lot in the late 80′s, finding the mix of collections fascinating. In the days before I owned any kind of computer, I remember spending hours being entranced by the endless of rows of early computers and reading about their applications. I think it was those Saturday mornings with my parents which sparked my interest in computers and logic and motivated me to learn more about programming.
The museum has a replica of “the Baby” – the Manchester Small Scale Experimental Machine, which was the worlds first stored-program computer. Baby was built in 1948 as a testbed for a primitive form of computer memory. Baby could store 32, 32 bit words and was used to find the highest proper divisor of 262,144. After running for 52 minutes, and performing 3.5 million operations in testing every integer downwards from 262,143, Baby calculated the correct answer: 131,072. For a primitive computer, 1.1 kIPS (Instructions per Second) is pretty impressive!
The Museum is a great mix of Manchester’s industrial heritage, it’s transport legacy and a record of the lasting impression it left in the area of maths and computing. As well as a great insight into early computing, Manchester’s textile heritage and a collection of aircraft build in Manchester, the museum sits on the site of the Liverpool and Manchester Railway’s Manchester Liverpool Road Station. The station was the terminus of the world’s first inter-city passenger carrying steam railway and is now the world’s oldest surviving railway station. The museum runs short train rides at weekends.
In the station yard, near the entrance to the Power Hall, I saw shunter the recently acquired class 06 shunter, number 06003 (pictured above). Other locomotives at the museum include 27001, a class 77 locomotive which was used on the Woodhead pass route between Manchester and Sheffield, and a broad gage 4-4-0 Beyer Peacock locomotive used in Pakistan.
East Cost Delays
Leeds to London: the first 381 miles.
I’ve just got back from a very brief trip to London, to see a man about a graph. The man liked my graph, so all was well with the world.
Things got a little less positive when the 1535 1D20 East Coast Kings Cross to Leeds service got delayed. Although the guard first reported the delay as “about 20 minutes” due to signalling problems near St. Neots, the actual delay was 37 minutes on arrival at Leeds.
Train delays can be very annoying, especially when it’s not clear what’s going on or what the delay is. The East Coast train guard demonstrated excellent customer service by keeping passengers informed of the reason for the delay and helped confused passengers work out how to change their travel plans because of missed connections.
The same helpful guard went out of her way to let every passenger know that, because the delay was more than 30 minutes, refunds would be issued. I wasn’t aware, but, if an East Coast service is delayed by more than 30 minutes, you can get a refund of 50% of the price of your ticket. East Coast could have hidden this fact in the bottom of their T&Cs somewhere but to come through the train literally handing out refund forms was a great demonstration of keeping customers informed. In case anyone else is interested, check out the terms here: East Coast Delay Repay.
The 185 mile journey was scheduled to take 137 minutes. At 37 minutes, the delay was 27% of the original journey time. The 27% delay realised a 50% refund. That’s not a bad return, even in today’s struggling economy. I didn’t actually get the money, partly because I was travelling for work and it wasn’t my money, but also because the refund is in travel vouchers and not in cash. Maybe it’s not so much of a decent return after all. I guess it’s the thought that counts.
December journey starts here
For some people, the 1st of December marks the beginning of chocolate fuelled calendar based madness. For some, the 1st of December comes 4 days into this year’s Christian season of Advent. For me, the 1st of December marks the start of a month long 10,000 mile journey which will see me seek for a specific type of perfect graph, the perfect Indian buffet and, possibly as bonus, a perfect graph of an Indian buffet.
I don’t know whether I’ll achieve any of these three things but the journey promises to be an eye-opening experience. Whatever happens, I’ll try and eat naan and curry on.
Matrix Operations in MS Excel
I’ve been doing a lot of work with matrices recently and doing much of the matrix arithmetic by hand. It’s useful to know that, whilst Microsoft Excel isn’t as good at matrix arithmetic than Mathlab or other specialist programs, it doesn’t do too badly.
There are specific functions which deal with matrix multiplication (MMULT), finding the inverse of a matrix (MINVERSE) and finding the determinant of the matrix (MDETERM). All three functions accept a range of cells which define the matrix you wish to operate on. Below are a number of examples of arithemtic on two matrices A & B, where B is the inverse of matrix A.
Matrix Arithmetic
Permutations of Bowling
I’ve just got home from an evening of bowling. My performance these days is a long way from the heady days of my youth, where I’d regularly achieve 150+ and sometimes top 200. I can’t recall properly, but I think I passed 200 on 3 occasions, with 232 being my best ever score. Today’s scores of 113 and 95 aren’t bad, but certainly aren’t what I’d consider good.
Tonight got me thinking about the possible scores you could achieve in one game. There are 1,924,226 different game combinations which would bring about a final score of 232. The most likely score is 77, with 172,542,309,343,731,946 different combinations which would bring about that score.
This problem, that of working out how many individual ways there are of making a particular bowling score, suffers from combinatorial explosion. For the first ball you bowl, there are 11 possible outcomes. You could score 0, 1, 2, through to 10, a strike. What happens on the second ball depends on what happens on the first ball; if you have hit 8 pins first time around you only have 3 options available for the second throw.
So, when you consider that the mean of the possible score distributions is 79.7, I realise that my scores from tonight are still “above average”. Maybe I just need a bit more practice to get me back into the highest few percent of bowling scores. For the record, the total number of possible games, i.e. ways to get a final score in bowling, is 5,726,805,883,325,784,576. That’s quite a few. Maybe a couple more games are called for after all.