From b933a8ec22730a1a4453dce7bf1a22601b521f9f Mon Sep 17 00:00:00 2001 From: Ted Trask Date: Thu, 24 Dec 2015 17:53:40 +0000 Subject: Use database LOCK rather than SERIALIZABLE transaction to prevent serialization errors --- provisioning-model.lua | 56 ++++++++++++++++++++++++++++---------------------- 1 file changed, 31 insertions(+), 25 deletions(-) diff --git a/provisioning-model.lua b/provisioning-model.lua index 40603c6..0ad268e 100644 --- a/provisioning-model.lua +++ b/provisioning-model.lua @@ -105,7 +105,7 @@ local deletedatabase = function() return table.concat(result, "\n") end -local databaseconnect = function() +local databaseconnect = function(unlocked) local result = false local res, err = pcall(function() result = provdb.databaseconnect() @@ -125,9 +125,15 @@ local databaseconnect = function() end end if result then - local sql = "BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE" + local sql + sql = "BEGIN TRANSACTION" mymodule.logevent(sql) provdb.runsqlcommand(sql) + if not unlocked then + sql = "LOCK TABLE provisioning_values IN ACCESS EXCLUSIVE MODE" + mymodule.logevent(sql) + provdb.runsqlcommand(sql) + end end return result end @@ -313,7 +319,7 @@ local get_device = function(device_id, create) local errtxt local res, err = pcall(function() local classes={} - connected = databaseconnect() + connected = databaseconnect(true) if not create and device_id and device_id ~= "" then -- Get the device-to-class mappings local sql = "SELECT class_id FROM devices_to_classes WHERE device_id='"..provdb.escape(device_id).."'" @@ -356,7 +362,7 @@ local get_device_params = function(device_id, editable) local errtxt = "Cannot find device" if device_id and device_id ~= "" then local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) -- First, just check to see if device_id exists local sql = "SELECT * FROM devices_to_classes WHERE device_id='"..provdb.escape(device_id).."' LIMIT 1" local tmp = getselectresponse(sql) @@ -495,7 +501,7 @@ local get_device_values = function(device_id) local errtxt if device_id and device_id ~= "" then local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) local sql = "SELECT g.name AS group, p.name, p.type, CASE WHEN v.value IS NOT NULL THEN v.value WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END AS value ".. "FROM (devices_to_classes d2t JOIN provisioning_classes t USING(class_id) JOIN classes_to_param_groups t2g USING(class_id) JOIN provisioning_groups g USING(group_id) ".. "JOIN param_groups_to_params g2p USING(group_id) JOIN provisioning_params p USING(param_id)) LEFT JOIN provisioning_values v ON(d2t.device_id=v.device_id AND p.param_id=v.param_id AND g.name=v.group_name ) ".. @@ -545,7 +551,7 @@ mymodule.list_templates = function() local errtxt -- Get the templates from the DB local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) local sql = "SELECT value AS filename, label, seq FROM provisioning_options WHERE param_id = (SELECT param_id FROM provisioning_params WHERE name = 'template') ORDER BY seq ASC, label ASC, value ASC" retval = getselectresponse(sql) if connected then databasedisconnect() end @@ -592,7 +598,7 @@ mymodule.get_template = function(self, clientdata) local errtxt if filename and filename ~= "" then local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) local sql = "SELECT * FROM provisioning_options WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name = 'template') AND value='"..provdb.escape(filename).."' ORDER BY seq ASC, label ASC, value ASC" local tmp = getselectresponse(sql) if tmp and #tmp > 0 then @@ -724,7 +730,7 @@ mymodule.list_class_groups = function() local errtxt -- Get the groups from the DB local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) local sql = "SELECT * FROM provisioning_class_groups ORDER BY seq ASC, label ASC" retval = getselectresponse(sql) if connected then databasedisconnect() end @@ -748,7 +754,7 @@ mymodule.get_class_group = function(self, clientdata) retval.seq = cfe({label="Sequence", seq=4}) local errtxt local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) if class_group_id and class_group_id ~= "" then sql = "SELECT * FROM provisioning_class_groups WHERE class_group_id='"..provdb.escape(class_group_id).."'" tmp = getselectresponse(sql) @@ -880,7 +886,7 @@ mymodule.list_classes = function() local errtxt -- Get the classes from the DB local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) local sql = "SELECT class_id, g.label AS group, g.name, c.label, c.seq FROM provisioning_classes c JOIN provisioning_class_groups g USING(class_group_id) ORDER BY g.seq ASC, g.label ASC, c.seq ASC, c.label ASC" retval = getselectresponse(sql) if connected then databasedisconnect() end @@ -906,7 +912,7 @@ mymodule.get_class = function(self, clientdata) local errtxt local res, err = pcall(function() local groups = {} - connected = databaseconnect() + connected = databaseconnect(true) if class_id and class_id ~= "" then local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..provdb.escape(class_id).."'" local tmp = getselectresponse(sql) @@ -1071,7 +1077,7 @@ mymodule.list_groups = function() local errtxt -- Get the groups from the DB local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) local sql = "SELECT * FROM provisioning_groups ORDER BY seq ASC, name ASC, label ASC" retval = getselectresponse(sql) if connected then databasedisconnect() end @@ -1098,7 +1104,7 @@ mymodule.get_group = function(self, clientdata) retval.defaults = cfe({type="group", value={}, label="Parameter Defaults", seq=8}) local errtxt local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) -- First, let's get all the parameters to set up the params.options and defaults local sql = "SELECT * FROM provisioning_params ORDER BY seq ASC, name ASC" local tmp = getselectresponse(sql) @@ -1310,7 +1316,7 @@ mymodule.list_params = function() local errtxt -- Get the params from the DB local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) local sql = "SELECT * FROM provisioning_params ORDER BY seq ASC, name ASC, label ASC" retval = getselectresponse(sql) if connected then databasedisconnect() end @@ -1339,7 +1345,7 @@ mymodule.get_param = function(self, clientdata) retval.seq = cfe({label="Sequence", seq=9}) local errtxt local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) if param_id and param_id ~= "" then sql = "SELECT * FROM provisioning_params WHERE param_id='"..provdb.escape(param_id).."'" tmp = getselectresponse(sql) @@ -1513,7 +1519,7 @@ mymodule.list_devices = function(self, clientdata) -- Get the devices from the DB local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) -- First, get the columns / class_groups local sql = "SELECT * from provisioning_class_groups ORDER BY seq ASC" @@ -1729,7 +1735,7 @@ mymodule.get_class_values = function(self, retval) retval.value.class_id.errtxt = "Invalid class ID" else local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) -- First, just check to see if class_id exists local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..provdb.escape(retval.value.class_id.value).."'" local tmp = getselectresponse(sql) @@ -1778,7 +1784,7 @@ end mymodule.fetch_device_values = function(self, search) local connected local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) search = mymodule.search_device_values(self, search) if search.errtxt then -- Just return the error @@ -1809,7 +1815,7 @@ mymodule.get_search_options = function() retval.comparison = cfe({type="select", value="=", label="Comparison", option={"=", "!=", "~", "!~", "~*", "!~*"}, seq=2}) retval.value = cfe({label="Parameter Value", descr="Parameter value or SQL regular expression", seq=3}) local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) -- Get the group/parameter options local sql = "SELECT g.name AS group_name, p.name AS param_name FROM provisioning_groups g JOIN param_groups_to_params USING(group_id) JOIN provisioning_params p USING(param_id) GROUP BY g.name, p.name ORDER BY g.name ASC, p.name ASC" local tmp = getselectresponse(sql) @@ -1853,7 +1859,7 @@ mymodule.search_device_values = function(self, search) if success then retval.result = cfe({type="structure", value={}, label="Devices", seq=4 }) local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) -- Get the devices from the DB sql = "SELECT d2t.device_id, " local group, param = string.match(search.value.id.value, "([^%.]*)%.(.*)") @@ -1898,7 +1904,7 @@ mymodule.get_param_options = function(self, clientdata) local errtxt = "Cannot find parameter" if param_id and param_id ~= "" then local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) -- First, just check to see if param_id exists local sql = "SELECT * FROM provisioning_params WHERE param_id='"..provdb.escape(param_id).."'" local tmp = getselectresponse(sql) @@ -1997,7 +2003,7 @@ end function mymodule.dump_database(self, db) local connected local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) local lines = {} local sql = "SELECT name, label, seq FROM provisioning_class_groups ORDER BY name, label" local tmp = getselectresponse(sql) @@ -2076,7 +2082,7 @@ function mymodule.get_file(self, clientdata) end local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) -- Add the device to the table of requests local sql = "SELECT * FROM provisioning_requests WHERE mac='"..provdb.escape(string.upper(mac)).."'" @@ -2191,7 +2197,7 @@ mymodule.list_requests = function(self, clientdata) end local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) local filter = {} columns.data = nil -- Cannot regex filter based on date because of the timestamp type @@ -2524,7 +2530,7 @@ mymodule.bulk_dump_devices = function(self, dumprequest) local connected dumprequest.value.devices = cfe({type="raw", value={}, label="devices.csv"}) local res, err = pcall(function() - connected = databaseconnect() + connected = databaseconnect(true) local classes = {} local reverseclasses = {} -- cgit v1.2.3