MySQL Queries No Waiting

Last time we looked at SKIP LOCKED where rows locked by another process were skipped. NOWAIT informs the server to return immediately IF the desired rows can not be locked.

How To Use NOWAIT

Start a connection to your MySQL 8 server, start a transaction, and make a query to lock up part of the data.

mysql>START TRANSACTION;

mysql>SELECT * FROM city WHERE countryCode = 'USA' FOR UPDATE;

On a second connection, start a transaction and issue a SELECT query with NOWAIT.

mysql>START TRANSACTION;

mysql>SELECT * FROM city WHERE countryCode = 'USA' FOR UPDATE NOWAIT;

The second connection will get a message saying 'statement aborted because lock(s) could not be acquired immediately and NOWAIT is SET

So if you can come back later to lock the records or just can not wait around for your lock to be issued, NOWAIT is the keyword to add to your queries.

SKIP LOCKED

SKIP LOCKED is a new feature in MySQL 8 that many will find valuable.  If allows you to not wait about for locked records and work on what is available -- the unlocked records.

How To Use SKIP LOCKED

The MySQL world database has 274 records in the city table where the countryCode field equals 'USA' there are 274 records. From past interactions, we somehow know there are some records with the ID field greater than 4000.

On MySQL shell number 1, start a transaction and lock some records

mysql>START TRANSACTION;

mysql>SELECT * FROM city WHERE ID > 4000 and countryCode = 'USA';

There will be 66 records.

On MySQL shell number number 2, start a transaction and lets try to lock the records starting at IS 3990 and up.

mysql>START TRANSACTION;

mysql>SELECT FROM city WHERE id > 3990 and countryCode='USA'
FOR UPDATE SKIPPED LOCKED;

There will be 10 records.  The records 4000 and up are locked by the transaction on the other shell.  

So no more hanging around for locked records.  You can process what is available at the current time.  So in situations where you are vending seats, hotel rooms, rental cares, or what have you -- you can find out what records are not locked by others and process them.

Scotland PHP, World PHP, and PyTexas -- Support your local conferences

November is the beginning of the end of the 2017 year conference cycle. But there is still time for you to get involved!! Scotland PHP is November 4th and features top presenters in Edinburgh. Last year this show was a happy surprise to find such a vibrant local PHP community and this year will be bigger.

November 15th-17th is PHP World in Washington, D.C. -- a show that had been extremely popular since it started with the movers and shakers of the PHP world.

And PyTexas has moved to Austin and is November 18th and 19th! This show has moved from the campus of Texas A&M into UT territory and will attract developers from the bubbling Texas developer community.
 
If there is a conference in your area it is a big bonus for you to support it. Not only can you get exposed to new ideas, network professionally, and visit vendors, you also show to those who are not from your area or lack local knowledge that there is a a community in your area. A community worth visiting, a community with a talent pool, and a community that needs paying attention to. So what if you do not know anyone there, or are not sure what exactly all the talks are about (I try to go to one session every conference on something I know NOTHING about to help broaden my perspective). You will run into others with like interests. And if you run into those with diametrically opposite interests, they can still teach you a thing or two. Two of the three events above (Scotland PHP and PyTexas(are smaller, regional events that tell vendors like Oracle/MySQL what is going on in that area by the attendees. All three are great events and, if local, worth spending a hard earned weekend day (or two) to attend and a reasonably price. PHP World may be your regional event but it has a larger, global audience that provides a lot of information for the price. The three sets of organizers of these shows do an amazing amount of work on your behalf. The presenters have fascinating things to show you. The vendors will have solutions to your problems. And you are part of a bigger community that needs you to help represent them to the world.
 
If you have not been to a conference this year then please try to make these shows.


And if you can not make it to show this year, you need to be planning for 2018. This is an investment in yourself that you need to make.