SELECT statement silently dies - postgresql

315 views Asked by At

I'm testing the differences between SELECT and SELECT FOR UPDATE in postgresql. One thing that I've noticed with SELECT statements is that when a lock or contention occurs, postgresql "seems" to be silently dying. Consider the following script:

require("luasql.postgres")

-- GLOBAL DECLARES --
local con
local env
local databasename = "XXXX"
local databaseUser = "XXXX"
local databasepassword = "XXXX"
local databaseserver="xx.xx.xx.xx" 
local databaseport = 5432 

local databaseconnect = function()
   if not con then
      -- create environment object   
      env = assert (luasql.postgres())
      con = assert (env:connect(databasename, databaseUser, databasepassword, databaseserver))   
      return true
   else
      return false 
   end
end

local escape = function(sql)
     sql = sql or ""
     return con:escape(sql)
end

local databasedisconnect = function()
   if env then
      env:close()
      env = nil
   end
   if con then
      con:close()
      con = nil
   end
end

local userid, servername = ...

--CONNECT TO DATABASE
if not con then databaseconnect() end

print(now)
sql = "UPDATE tbl_availablenumbers SET UsedYesNo = true, user_id="..userid..", updateddatetime='"..os.date("%Y-%m-%d %H:%M:%S").."' WHERE reservationnumber =("
      .."SELECT reservationnumber FROM tbl_availablenumbers WHERE UsedYesNo=false Order By id ASC Limit 1 ) RETURNING reservationnumber"

print('Attempting to update tbl_availablenumbers table...')
assert(con:execute(sql))       

--DISCONNECT FROM DATABASE
if con then databasedisconnect() end
print("Goodbye")

I was assuming that if there's a failure of some sort where the update doesn't work, postgresql will return a message.

Here's what I'm doing. I've created a wrapper script for the above logic that will invoke it X number of times. For now, I try running it 100 times. Then I run these scripts from 2 different servers against the same database. I know there are failures / locking problems because after both statements run, I don't have a total of 200 records. Session 1 creates for example, 99 and the other 65. My question is why am i not notified of dropped transactions? I read something about a NOWAIT clause. Does that have something to do with it? I'm not too familiar yet with postgresql and have been comparing it with other databases like mysql and ms sql server where there is a configuration setting that defines how long to wait in race conditions.

Perhaps all I need is the NOWAIT. But I wanted to just confirm with someone who may have more experience. Thanks.

0

There are 0 answers