Simple Mutex implementation with SQL

When data in a database are used and changed by multiple processes, you have to make that piece of data isolated from other ones. In other words, you can't let two or more changes happen for one particular field in a database.

Let me provide an example. Assume that you have a phone book table that contains numerous rows of names, phone numbers, and status:

Name	Phone	Status	Last Check
John Doe	111-111-1111	OK	1-1-2014 00:00:12 UTC
Mrs. Peach	222-222-2222	OK	1-1-2014 00:00:14 UTC
Curious George	333-333-3333	Disconnected	1-1-2014 22:22:22 UTC
Roger Waters	444-444-4444	Busy	1-1-2001 01:00:12 UTC
Bill Gates	555-555-5555	Busy	1-1-1998 10:05:23 UTC
The God	000-000-0000	Unreachable	1-1-2014 20:00:10 UTC

The "Status" field shows what happened when our application tried to call "Phone" of "Name" on "Last Check".

Now you want to run your app to constantly keep the table up to date. To do that, it keeps calling all numbers in a multithreaded loop, in which number of threads are total number of the rows.  Therefore, all rows should be busy at the same time.

Here is a simple flowchart of each thread (but a bad one):

Before we talk about the problem that will occur, please see the famous Sleeping Barber problem, to understand why we need Mutex (Mutual Exclusion) and Semaphore.

For instance:

1. Thread A "Selects" one row (John Doe)
2. At the same time, Thread B "Selects" the same row.
3. Thread A calls John Doe and it's ringing. That means the "Status" is OK.
4. Thread B calls John Doe and it is busy, because thread A is calling.
5. Thread A "Updates" the row to OK
6. Thread B immediately "Updates" the row to Busy, that was not what we really meant.

Now we have some understanding. The next step comes to mind is to have a "flag" to let other threads know that particular phone number is under process, so no intervention should occur. As an example, we add a flag column to our table, and will set it to "true" when is in use, and "false" when it is not.

Name	Phone	Status	Last Check	Flag
John Doe	111-111-1111	OK	1-1-2014 00:00:12 UTC	True
Mrs. Peach	222-222-2222	OK	1-1-2014 00:00:14 UTC	True
Curious George	333-333-3333	Disconnected	1-1-2014 22:22:22 UTC	False
Roger Waters	444-444-4444	Busy	1-1-2001 01:00:12 UTC	True
Bill Gates	555-555-5555	Busy	1-1-1998 10:05:23 UTC	False
The God	000-000-0000	Unreachable	1-1-2014 20:00:10 UTC	False

And here will be the flowchart (again, not so good):

This is a better solution and a naive developer might think it's sufficient. It actually is a good solution if we're dealing with an array of objects in RAM, instead of a database resides in a hard disk and perhaps also needs some time to establish connection and getting the response. Most relational databases such as MySQL, MSSQL, Oracle and Postgres support different isolation levels. We also have predefined Table-lock and Row-lock strategies for different engines. For example, InnoDB engine is a very good choice when it comes to concurrency and its isolation level is  REPEATABLE READ , that means we can have multiple reads (Select) on each row, but only one write (Update) against the same row, and we can have multiple writes against multiple rows.
Changing the isolation level to "Serializable" makes almost same behavior, unless we would be able to write only one row at a time.
That means changing the isolation level to the highest (Serializable) will NOT help to prevent a problem like this:

1. Thread A "Selects" one row (John Doe)
2. At the same time, Thread B "Selects" the same row. (it is possible in serializable mode)
3. Thread A calls John Doe and it's ringing. That means the "Status" is OK.
4. Thread B calls John Doe and it is busy, because thread A is calling.
5. Thread A "Updates" the row to OK
6. If thread B immediately tries to "Update" the row at the same time  of Thread A, we will get an error (because the row was locked at the moment, this is a deadlock situation) 
7. If thread B  has a slight delay (most of the time), that means the job of Thread A is done and it can set the row to Busy, same problem as above.

Now we can talk about the real solution. There are three different approach we can do to prevent this race conditions:

1. (Not a good idea, but yet a solution): Set max connection to your database to "1", and increase the timeout to a bigger number. This makes all queries get into a line and get executed one by one.
2. (A better solution, not the best): You can lock the table/row even when you're doing a Select, by adding "Lock in Share Mode" at the end of select query. If you're doing this, I strongly suggest to lower your isolation level to something like READ COMMITTED.
3. (The good solution) instead of using "true" and "false" in our flag, we can have something like "UUID" and "False". Then we need to conduct the UPDATE query ALONG with our SELECT as a sub-query. This approach keeps the other threads away while the UPDATE is doing it's job (and the row is locked by the DB engine).

Here is an example:

Instead of doing following two queries in above (bad) approaches:

-> Mysql: Select name,phone From mytable Where Flag='False';
-> Script: set name_var = name
-> Mysql: Update mytable Set Flag='True' Where name = ${name_var};

Do it this way:

-> Script: generate a UUID (or a long random string or number)
-> Mysql: Update mytable Set flag= ${UUID} Where name in (Select name From (Select name From mytable Where flag='False' Order By RAND() Limit 1 ) As dummyTableName);
-> Mysql: Select name,phone From mytable Where Flag=${UUID};

SSH and remote commands during automation.

Sometimes, you have to check a system's log or run some commands during automation or load test.

For example, recently I had to perform some end-to-end tests against enterprise outdoor modems. The test case requires to perform 100 "provisioning" against each modem per hour, having 50 modems at a time. Here is a brief about what "provisioning" means:

1- Script picks one modem from data source and apply a mutex (do-not-use flag).

2- Get corresponding parameters that should be used in web service requests from data source.

3- Generate additional needed parameters (Random IDs, Names created from timestamp , etc)

4-Send some requests in ordered manner (add to ESB DB, create a traffic service, activate)

5- Listen to JMS for the response,

6- Reboot the device

7- wait for the device to get back online

8- check device status, resources and logs.

9-continue to activate more services (repeat steps 1 to 8) for x more times.

The lines I marked as red are not the hardest parts, but are the ones I'm going to write about. 

I'm going to use Putty as the extension tool because:

• I can run multiple Putty instances in multi-thread load tests,
• We can easily save logs through Putty output.

Step 1: Create sessions for each remote device. Here is what I suggest in Putty config:

1. Name the session to IP or NS to limit your data-driven setup efforts.
2. "Close window on exit" : Always
   
3. Logging: Printable output with a unique file name, Always append to the end.
   
4. Connection keepalive check interval: ~5 (that depends, but is necessary to make the window closed when inactive)
   
5. Define Data -> Auto-Login username
   
6. if you are going to send only one line of command, put it in SSH -> Remote Command; otherwise, you can put all commands in a text file and call it externally as I explain below.
   

Step 2: create command files:

Since we have two group of commands (Reboot and Monitor), then I will use external files:

• Reboot: It is usually needed to send the "reboot" command with /sbin/ prefix if  you are sending it over SSH remotely. Therefore, my reboot.txt file only contains one line (/sbin/reboot)
• Monitoring: I want to constantly tail logs, and monitor CPU and RAM usage at the same time. So I have to run one of them in the background. something like: 

                      tail -f /var/log/messages & top -ibd2

Step 3: Call putty (and ping) as OS processes:

The way to conduct this step depends the tool or language you are using, but the concept is the same. You simply need to execute a command using OS command shell. for example in Java you'll need to run it like this:

exec(String command, String[] envp, File dir)

Remember, "String command" is a little tricky in windows: you usually need to add "cmd /c" before the main command string.

What you are going to call putty is: 

c:/puttyFolder/putty.exe -load "session_name" -pw password -m c:/textFiles/Reboot.txt

And if you are going to use it in a tool like JMeter: