Wednesday, March 11, 2015

Software Interview Nightmares

The below is from my Quora answer to ”Has Cracking the Coding Interview made it more difficult for recruiters to evaluate software dev…” @ http://qr.ae/j5qwX

The problem with the hiring process in many software companies (especially the big ones) is that instead being optimized to establish diverse teams of smart, creative and passionate engineers – it is heavily optimized to filter out the Secretly Terrible Engineers, for the most part by using the same old, back-to-basics algorithms questions, irrespective of candidates experience and background. Unfortunately, as recognized by many (including Google, Facebook), this interview strategy result in an outstanding false negative ratio.

Now, can a company come out with an alternative system with much lower false negative ratio without compromising the hiring bar and increasing the false positive (miss hire) ratio? yes they can! Myself and my extended team have been doing just that in the past 4 years.

The honest truth is that very few of us (if any) have the wisdom to assess the programming skills of experienced engineers in a 45 minutes algorithms quiz which is performed under extremely stressful conditions.

Trust me, I've been asking these questions for years. I even documented all the questions (and answers) that I've asked and being asked, it’s all right here: Get Ready for Software Interview. As an interviewer, algorithms questions are the easiest to ask. You don’t have to sweat nor think too much. You know the questions and answers by heart, and you are always surprised to see engineers with great experience struggle when they try to solve these questions with their back against the whiteboard.

It never really felt quite right, and the more I interviewed the more I realized that I simply can't evaluate years of experience using 45 minutes quiz. That definitely wasn't the way I wanted to be evaluated.

We can do better. We should do better. Hiring managers must know better. Instead of settling on these awful odds, they should refresh their interview strategy. They should hand pick the interviewers based on the candidate experience and current set of skills. They should guide the interviewers to focus less on algorithms on the whiteboard (enough already!) and more on good old, one-on-one software related conversations.

I believe that a good interviewer, with similar background as the interviewee, should be able to asses the quality of the latter simply by talking with him/her for 15-30 minutes. Talk about his/her past experience, look at the code that he/she has written, and check if he/she became an expert in the areas that he/she worked on. Every interviewer that is asked to participate in an interview loop should carefully read the candidate resume and make sure that he can ask coding questions related to the latter past experience. If a technical interviewer doesn't have the appropriate experience to ask these questions (no shame in that), he should excuse himself from the loop instead of defaulting to good old , one size (doesn't) fit all algorithms quiz. I mean, it makes little sense to base an interview on algorithms questions when interviewing an engineer that spent the last 5 years building web applications focusing on HTML and java-script (and then rationalize the no-hire decision on his/her lacking 'core' engineering skills). Yet it happens all the time.
Some of the best developers I know have degrees in Electronics, Physics and Art. They have been developing software since puberty. They are passionate about it. It’s their hobby. They would work for free. Some of them might not know what’s the BigO of Merge Sort (god forbid!), but they have been rockstars in every company that they worked on, the kind of talent that you don’t want to miss. Dare to add those people to your mix and you’ll get a diverse (=more productive) team that create great products that appeal to a wider range of users.

If you search for topics currently asked in Software interviews, you'll find the following:binary search, tree traversal (pre/in/post), sorting algorithms (merge/quick/and some O(n^2) ones), recursion/iteration, graph search, dynamic programming, breadth first search, depth first search, stacks, queues, hashtables, heaps, priority queues, and linked lists (single/doubly/circular).
We expect all candidates to solve these questions on the spot, under pressure, irrespective of their past experience.

The problem is that most developers don’t get a chance to implement even half of the above in their daily work. They reuse existing algorithms or services encapsulated nicely in most modern frameworks/platforms. So, you are optimizing your interview to find the 5% that implement these algorithms in their daily work, plus 5% collage grads that just finished the 'Introduction to Algorithms' class, and maybe another 20% that spent couple of months preparing to these interviews.
The rest are in serious disadvantage. They might or might not pass your tests. It’s more than likely that hiding in this group are the 10x multipliers, the ones that can ramp up quickly on the most complex code-base, the ones that write beautiful and maintainable code, the ones that can design, the ones that can test, the ones that can lead, the ones that stop at nothing and get things done, the ones that make the difference between successful and unsuccessful projects. Isn’t that what you are looking for?!

Having said all that, I have no fantasy that interviewers will stop using the whiteboard so extensively any time soon. It's just too easy. Plus, software engineering is spread across so many areas (web, mobile, SQL, OO, concurrency, distributed systems, cloud, big data, etc) - that algorithms seems like the only common denominator. Just that it isn't

Saturday, January 24, 2015

Could not obtain exclusive lock on database 'model'? Here’s how you find the smoking gun..

If your team owns a process that provision SQL Server databases in masses, you probably encountered the below exception in one of your test clusters, or even in production:

Microsoft.SqlServer.Management.Smo.FailedOperationException: Create failed for Database '***'.  ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Could not obtain exclusive lock on database 'model'. Retry the operation later.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

This happens because when your process attempted to create a database, some other process was using the ‘model’ database. It’s possible that someone started a session with ‘model’ via SQL Server Management Studio, or maybe a SCOM process was using it.

Anyways, the important thing is that you find the smoking gun to unlock your process and make sure that this doesn’t happen again...

If you can repro the problem, you can find the culprit simply by running: ‘EXEC sp_who2’, you will get a list of all the processes/users that use any database in your server, including the ‘model’ database.

Or, you can run the following query in order to get info on the processes that use the ‘model’ database:

DECLARE @Table TABLE
(    SPID INT,
    Status VARCHAR(MAX),
    LOGIN VARCHAR(MAX),
    HostName VARCHAR(MAX),
    BlkBy VARCHAR(MAX),
    DBName VARCHAR(MAX),
    Command VARCHAR(MAX),
    CPUTime INT,
    DiskIO INT,
    LastBatch VARCHAR(MAX),
    ProgramName VARCHAR(MAX),
    SPID_1 INT,
    REQUESTID INT) 
INSERT INTO @Table EXEC sp_who2 
SELECT * FROM  @Table WHERE DBName='model'

Alternatively, you can use:

SELECT nt_domain, nt_username, program_name, cmd, status 
FROM sys.sysprocesses 
WHERE DB_NAME(dbid)='model'

I prefer the last option. The results will look something like this:

image

However, if your process is running somewhere in the cloud, it might be helpfully to get the blame list programmatically. Here’s a sample code that detects ‘model lock’ type error and adds details on the culprit processes to the exception.

    class Program
    {
        static void Main(string[] args)
        {
            var builder = new SqlConnectionStringBuilder()
            {
                DataSource = "localhost",
                IntegratedSecurity = true
            };

            var sqlConnection = new SqlConnection(builder.ConnectionString);
            sqlConnection.Open();
            try
            {
                Server server = new Server(new ServerConnection(sqlConnection));

                string name = "test" + Guid.NewGuid().ToString("N");
                Console.WriteLine("Creating db " + name);
                Database database = new Database(server, name);
                try
                {
                    database.Create();
                    Console.WriteLine("No Failure");
                }
                catch (FailedOperationException e)
                {
                    HandleModelLockException(server.Databases, e);
                    throw;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            finally 
            {
                sqlConnection.Close();
                Console.Read();
            }
        }

        private static void HandleModelLockException(DatabaseCollection databases, FailedOperationException e)
        {
            bool modelLockException = ModelDatabaseLockedAnalysis.IsModelLockException(e);

            if (!modelLockException) return;

            string message = ModelDatabaseLockedAnalysis.Analyze(databases);
            
            var s = FormatErrorMessage(message);
            throw new ModelDatabaseLockedException(s, e);
        }

        private static string FormatErrorMessage(string message)
        {
            var builder = new StringBuilder();
            builder.AppendLine();
            builder.AppendLine("Failed to create database since the system database 'model' is locked. " +
                               "Here's a list of processes that currently use the 'model database:");
            builder.AppendLine(message.Replace(" ", string.Empty));
            string s = builder.ToString();
            return s;
        }
    }

The code above creates a database, incase of an error it calls HandleModelLockException. The latter check if the exception (or one of its InnerExceptions) indicates ‘model locked’ type of error. In that case, it calls ModelDatabaseLockedAnalysis.Analyze that queries for the processes that use the ‘model’ database. Than, it simply add the list to the exception.

Here’s the implementation of ModelDatabaseLockedAnalysis:

    public class ModelDatabaseLockedAnalysis
    {
        private const string query =
            "SELECT nt_domain, nt_username, program_name, cmd, status FROM sys.sysprocesses WHERE DB_NAME(dbid)='model'";

        public static bool IsModelLockException(FailedOperationException e)
        {
            const string messageIndication = "lock on database 'model'";

            Exception exception = e;
            while (exception != null)
            {
                bool modelLockException = exception.Message.ToLower().Contains(messageIndication);
                if (modelLockException)
                {
                    return true;
                }
                exception = exception.InnerException;
            }
            return false;
        }

        public static string Analyze(DatabaseCollection databaseCollection)
        {
            var masterDb = databaseCollection["master"];
            DataSet dataSet = masterDb.ExecuteWithResults(query);
            IEnumerable<LockingProcessInfo> processInfos = Parse(dataSet);
            var builder = new StringBuilder();
            foreach (var processInfo in processInfos)
            {
                builder.AppendLine(processInfo.ToString());
            }
            return builder.ToString();
        }

        private static IEnumerable<LockingProcessInfo> Parse(DataSet dataSet)
        {
            DataTable dataTable = dataSet.Tables[0];
            var list = new List<LockingProcessInfo>();
            foreach (DataRow row in dataTable.Rows)
            {
                string domain = (string) row["nt_domain"];
                string username = (string) row["nt_username"];
                string programname = (string) row["program_name"];
                string cmd = (string) row["cmd"];
                string status = (string) row["status"];
                list.Add(new LockingProcessInfo(domain, username, programname, cmd, status));
            }
            return list;
        }
    }
    
    public class LockingProcessInfo
    {
        public LockingProcessInfo(string domain, string username, string programname, string cmd, string status)
        {
            this.Domain = domain;
            this.Username = username;
            this.Programname = programname;
            this.Cmd = cmd;
            this.Status = status;
        }

        public string Domain { get; private set; }

        public string Username { get; private set; }

        public string Programname { get; private set; }

        public string Cmd { get; private set; }

        public string Status { get; private set; }

        public override string ToString()
        {
            return string.Format("Domain: {0}, Username: {1}, Programname: {2}, Cmd: {3}, Status: {4}", Domain, Username, Programname, Cmd, Status);
        }
    }

    internal class ModelDatabaseLockedException : Exception
    {
        public ModelDatabaseLockedException(string message, Exception exception)
            : base(message, exception)
        {
        }
    }

In order to test the code, open SQL Server Management Studio, create a new query on the ‘master’ database, and run:

use model
go

That would lock the ‘model’ database until you close the query window.

Now, run the program and you will get something like:

image 

You can see clearly that the user ‘avezra’ is using the database using SQL Server Management Studio.