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.