Blocking On Demand

Do you ever succumb to "Work Avoidance Syndrome"?

That's when you tackle anything except the task you're supposed to be working on...

...when you check your inbox or Google News or Drudge a thousand times a day instead of working on, well, work.

Right now, the work being avoided 'round here is Foxhound's new AutoDrop feature for automatically dropping ill-behaved database connections that block other connections or have been blocked for a long time.

Instead of that, how about this?

How to easily create blocked connections

First, some setup:

• a few different user ids so it's easy to tell which connection is which when there are a lot of them,

• a table for the different connections to UPDATE and (if they're lucky) grab locks or (if they're not so lucky) get blocked, and

• some rows for the connections to UPDATE.

Here's the setup SQL:

GRANT CONNECT TO "E.Fernandez" IDENTIFIED BY SQL;
GRANT DBA TO "E.Fernandez";

GRANT CONNECT TO "L.Rodriguez" IDENTIFIED BY SQL;
GRANT DBA TO "L.Rodriguez";

GRANT CONNECT TO "C.Gonzalez" IDENTIFIED BY SQL;
GRANT DBA TO "C.Gonzalez";

GRANT CONNECT TO "S.Johnson" IDENTIFIED BY SQL;
GRANT DBA TO "S.Johnson";

CREATE TABLE inventory (
   item_id                INTEGER NOT NULL DEFAULT AUTOINCREMENT, 
   item_count             INTEGER NOT NULL,
   item_name              VARCHAR ( 1024 ) NOT NULL,
   PRIMARY KEY ( item_id ) );

BEGIN
DECLARE @row_counter INTEGER;
SET @row_counter = 1;
WHILE @row_counter <= 10000 LOOP
   INSERT inventory ( item_count, item_name ) VALUES ( 1, 'Initial load.' );
   SET @row_counter = @row_counter + 1;
END LOOP;
COMMIT;
END;

Here's a Windows command file for E.Fernandez to grab and hold locks on three rows...

Tip: You can run SQL statements directly from a dbisql command line without having to open up a GUI window. You can even run more than one statement, even a BEGIN block with local declarations, and you can use the Windows command line separator character ^ to code the statements on separate lines.

Tip: You can use a WAITFOR DELAY statement to prevent a dbisql command line session from terminating right away when all its work is done. That's important in this case because when dbisql terminates it releases all the locks.

ECHO Lock some rows...

START "12.E.Fernandez"^
  "%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=E.Fernandez;PWD=SQL;CON=12.E.Fernandez;"^
  UPDATE DBA.inventory SET item_count = item_count + 1 WHERE item_id BETWEEN 1 AND 3;^
  WAITFOR DELAY '01:00:00';

PAUSE

Yes, the "E" in E.Fernandez stands for "Evil"... the WAITFOR DELAY statement simulates a long-running transaction: no COMMIT or ROLLBACK for an hour.

Tip: The Windows START prefix on the dbisql command is used to launch dbisql in a separate window. This allows the outer command file to get control and continue processing without waiting for the inner dbisql command to finish. You can read more about START by executing HELP START in a command window. The START prefix isn't really required on the E.Fernandez dbisql command line above, but it certainly is necessary in the next command file.

Here's another Windows command file to show L.Rodriguez, C.Gonzalez and S.Johnson falling victim to (getting blocked by) the row locks acquired by E.Fernandez:

ECHO Get blocked...

START "12.L.Rodriguez"^
  "%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=L.Rodriguez;PWD=SQL;CON=12.L.Rodriguez"^
  UPDATE DBA.inventory SET item_count = item_count + 1 WHERE item_id = 1;

START "12.C.Gonzalez"^
  "%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=C.Gonzalez;PWD=SQL;CON=12.C.Gonzalez"^
  UPDATE DBA.inventory SET item_count = item_count + 1 WHERE item_id = 2;

START "12.S.Johnson"^
  "%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=S.Johnson;PWD=SQL;CON=12.S.Johnson"^
  UPDATE DBA.inventory SET item_count = item_count + 1 WHERE item_id = 3;

PAUSE

Note: WAITFOR DELAY statements aren't required for these dbisql commands because they are already prevented from terminating when the UPDATE statements are blocked by the row locks held by E.Fernandez. As soon as the E.Fernandez dbisql command terminates, however, so will all three of the other dbisql commands.

...and that's all there is to it, no multi-threaded application program required, just a handful of dbisql commands. Here's what the four dbisql windows look like:

---

You can see what's going on by running the following script in a separate dbisql session:

SELECT Number,
       Name,
       Userid,
       BlockedOn,
       LockRowID,
       LockTable
  FROM sa_conn_info()
 ORDER BY Number;

SELECT conn_id,
       conn_name,
       user_id,
       row_identifier,
       table_name,
       lock_class,
       lock_duration,
       lock_type
  FROM sa_locks()
 ORDER BY conn_id;

The first SELECT shows all the current connections, with BlockedOn = 27 for each of the three blocked connections:



The second SELECT shows all the locks currently held:



The LockRowID values in the first result set can be matched up with the row_identifier values in the second result set, to determine the following:

• C.Gonzalez is blocked by a row lock on DBA.inventory where the ROWID() = 43450369,

• L.Rodriguez is blocked by a row lock on DBA.inventory where the ROWID() = 43450368, and

• S.Johnson is blocked by a row lock on DBA.inventory where the ROWID() = 43450370.

The blocked connection display in Foxhound does the "matching up" of blocks and locks as well as keeping a permanent record for display on the History page:



You can copy and paste the "Locked Row Query" values from Foxhound into a dbisql session to see the rows that are locked:

SELECT * FROM DBA.inventory WHERE ROWID ( inventory ) = 43450369;
SELECT * FROM DBA.inventory WHERE ROWID ( inventory ) = 43450368;
SELECT * FROM DBA.inventory WHERE ROWID ( inventory ) = 43450370;

item_id,item_count,item_name
2,4,'Initial load.'

item_id,item_count,item_name
1,5,'Initial load.'

item_id,item_count,item_name
3,4,'Initial load.'

Here's what the blocked connections look like in a SQL Anywhere 11 database: "Write lock" instead of the new "WriteNoPK lock" in version 12:



For the record, here's what they look like in Version 10.0.1:



Foxhound doesn't show the same level of detail for Versions 10.0.0 and earlier because the information isn't available to the outside world.

That wasn't really Work Avoidance, was it?

Nooooo, certainly not... the ability to create blocked connections is necessary when the time comes to test AutoDrop, right? So it's real work, right?

---