Wednesday, March 11, 2015

Software Interview Nightmares

Nowadays It seems that the bigger the company is, the bigger the paranoia to hire one of these Secretly Terrible Engineers. Driven by a fear that they might hire one of these idiots, interviewers tend to always ask the same old, back-to-basics, algorithms/data-structures related questions, even when interviewing experienced engineers. Unfortunately, as recognized by many (including Google, Facebook), such interviews result in a significant false negative ratio.

Instead of refreshing their interview strategy, most companies try to improve this awful false negatives ratio by asking/expecting engineers to “prepare” to these questions by reading books like “Cracking the Coding Interview” and practice weeks or months on solving problems (in most cases) completely unrelated to their daily work.

To be honest, ever since I joined Microsoft, I’ve based most of my interviews on these questions. I also wrote down all the questions (and  answers) that I have 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 struggle when they try to solve  them with their back against the whiteboard (except for the ones that solved a  similar question in their recent past).
What I have learned is that cutting off developers based on their ability to  quickly solve algorithms questions on the whiteboard results in a significant false negative ratio. You end up ignoring the best of talent, the x100  multipliers, the ones that you are so desperately looking to find.  

If you ask me, hiring managers should know better by now. Instead of settling on these awful odds, they should join the 21 century and refresh their interview strategy. They should hand pick the  interviewers based on the candidate current set of skills. They should guide the  interviewers to focus less on whiteboard coding (come on already!) and more on  good old, one-on-one software related conversations. A good interviewer should  be able to asses the quality of a candidate simply by talking with the guy for  15-30 minutes. For the rest of the time, show the candidate existing code and  see if he can tell what's wrong with it and how it can be improved. Talk about  his past experience and check if he became an expert in the areas that he worked  on.

Some of the best developers I know have degrees in Electronics, Physics and  Art (some don’t have a degree at all). They have been developing software since  puberty. They are passionate about it. It’s their hobby. The would work for  free. Some of them don’t know (nor care) what’s the Big O 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.

If you search for topics currently asked in Software interviews, you will  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 95% of  developers don’t get a chance to implement most of the above in their daily  work. Someone else already did it better. 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  another 20% that spent couple of weeks preparing to these interviews.
The rest 70% are in serious disadvantage. They might or might not pass your  tests. It’s more than likely that hiding in this group are the 100x multipliers,  the ones that can ramp up quickly on the most complex codebase, the ones that  write beautiful and maintainable code, the ones that can design, the ones that  can test, 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 interviews 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, etc) - that Algorithms and Data Structure seems like the only common denominator. Just that it isn't.

The above 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

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.