Changeset 13251 for trunk/Nebulous-Server/lib/Nebulous/Server/SQL.pm

Timestamp:

May 4, 2007, 1:36:46 PM (19 years ago)

Author:

jhoblitt

Message:

rework the getmountedvol() stored procedure to attempt to work around what appears to be some nasty transacational isolation leak throught that was causing getmountedvol() to randomly hang when nebdiskd changed the mount table.

File:

• trunk/Nebulous-Server/lib/Nebulous/Server/SQL.pm (modified) (5 diffs)

trunk/Nebulous-Server/lib/Nebulous/Server/SQL.pm

@@ -1 +1 @@
 # Copyright (c) 2004  Joshua Hoblitt
 #
-# $Id: SQL.pm,v 1.39 2007-05-04 21:20:43 jhoblitt Exp $
+# $Id: SQL.pm,v 1.40 2007-05-04 23:36:46 jhoblitt Exp $
 
 package Nebulous::Server::SQL;
@@ -274 +274 @@
 DROP TABLE IF EXISTS class;
 DROP TABLE IF EXISTS log;
+DROP TABLE IF EXISTS mountedvol;
 DROP PROCEDURE IF EXISTS getmountedvol;
 SET FOREIGN_KEY_CHECKS=1
@@ -386 +387 @@
 ###
 
+CREATE TABLE mountedvol(
+    mountpoint VARCHAR(255) NOT NULL,
+    total BIGINT NOT NULL,
+    used BIGINT NOT NULL,
+    vol_id INT NOT NULL,
+    name VARCHAR(255) NOT NULL,
+    path VARCHAR(255) NOT NULL,
+    allocate BOOLEAN DEFAULT FALSE,
+    available BOOLEAN DEFAULT FALSE,
+    KEY(vol_id),
+    KEY(allocate),
+    KEY(available)
+) ENGINE=innodb;
+
+###
+
 CREATE PROCEDURE getmountedvol() DETERMINISTIC
 BEGIN
@@ -401 +418 @@
     SELECT @@session.tx_isolation INTO trans_level; 
 
-    -- set trans level
+    -- set trans level to repeatable-read so the volume table does not change
+    -- out from under our cursor
     SET @@session.tx_isolation = 'REPEATABLE-READ';
-
-    -- create a temp table to hold the merged results of the volume & mount
-    -- tables.  One would hope the that the transaction isolation level will
-    -- stop one session from stomping on another sessions version of this
-    -- table.
-
-    DROP TABLE IF EXISTS mountedvol;
-    CREATE TEMPORARY TABLE mountedvol(
-        mountpoint VARCHAR(255) NOT NULL,
-        total BIGINT NOT NULL,
-        used BIGINT NOT NULL,
-        vol_id INT NOT NULL,
-        name VARCHAR(255) NOT NULL,
-        path VARCHAR(255) NOT NULL,
-        allocate BOOLEAN DEFAULT FALSE,
-        available BOOLEAN DEFAULT FALSE,
-        KEY(vol_id),
-        KEY(allocate),
-        KEY(available)
-    ) ENGINE=MEMORY;
 
     -- iterate over the volume table finding the coresponding entry in the
     -- mount table and inserting union of the volume & mount row into the
     -- mountedvol table
-
     OPEN cur1;
+
+    DELETE FROM mountedvol;
 
     myloop: LOOP
@@ -446 +445 @@
     -- restore the original transaction level
     SET @@session.tx_isolation = trans_level;
+
+    COMMIT;
 END