[Gllug] A programming question. long.

Fri Jul 6 09:47:17 UTC 2001

Yes to some extent. The SELECT .. FOR UPDATE will block other sessions who
also try to SELECT .. FOR UPDATE until it COMMITs or ROLLBACKs.
You cannot do a select max() type functions with FOR UPDATE, but you could
do this in two stages

1. select max(primary key) from table
2. select xyz from table where column = primary key for update

You can also do a SELECT .. FOR UPDATE NOWAIT which will not block on the
row lock but will return with an error saying the row is locked.

Robin

-----Original Message-----
From: Paul Brazier [mailto:pbrazier at cosmos-uk.co.uk]
Sent: 06 July 2001 10:33
To: Gllug (E-mail)
Subject: RE: [Gllug] A programming question. long.

Would this row lock prevent another user from doing SELECT max(id) FROM
table?
i.e. would it just return the max id excluding the locked row or would
it wait for the row lock to be released?

-----Original Message-----
From: robin.c.smith at bt.com 
Sent: 06 July 2001 10:16
To: bt.com.robin.c.smith; linux.co.uk;.gllug
Subject: RE: [Gllug] A programming question. long.

SELECT xyz FROM pqr FOR UPDATE;

This will lock the row ( works in Oracle, I haven't checked Postgres and
MySQL ).

Robin

-----Original Message-----
From: Paul Brazier [mailto:pbrazier at cosmos-uk.co.uk]
Sent: 06 July 2001 10:09
To: Gllug (E-mail)
Subject: RE: [Gllug] A programming question. long.

I guess the root of the problem is that a SELECT doesn't lock the table
for other SELECTs so two people could select the same top row. You could
lock the whole table for each SELECT but I guess this would be
inefficient. You can't do a row lock because you need access to all rows
to get the top (max) row.

How about having an extra field in the table called LockedBy or
something.
Then instead of SELECTing the top row, INSERT some sort of connection ID
into this field. (because INSERTing does use a row lock)
Then someone else can INSERT into the max row WHERE LockedBy is empty. 

Then the connection can SELECT and DELETE the row (referencing by the
connection ID) without worrying about other users also using it.

You'll also be able to easily see which connections are working on which
rows at any time.

The only problem you might have is if the connection is ended unusually
in which case you might have to do some housekeeping to avoid
half-processed rows from staying around. 

-----Original Message-----
From: co2cool at yahoo.com 
Sent: 05 July 2001 12:59
To: linux.co.uk.gllug; yahoo.com;.co2cool
Subject: [Gllug] A programming question. long.

I was talking to Bruce last night about the small
prediciment I am in - I have a database with about 3
million rows in the main table.  Theres about 100-150
users accessing this data,  they want to copy the top
row off the table and into the application and mark it
as dealt with.

open connection
 select the top 1 * from the table where the date they
should be called by is in the past.
copy data
delete row
close connection

then if for example we need to call them again, we
just insert the row back in with a suitable date, for
example in a weeks time.

What i need to do is make sure that nobody gets the
same row, this can only happen if somebody accesses
the table with a select statement before the row the
previous user deletes the row from the table.

so we need some sort of locking. Enter the spin lock.

We can use a bit of code that locks or waits.

so...

if Locked_Database(username:password) 
{
  select
  delete
  unlock
  do stuff
}
else
 grump "databases wont lock"
end if

locked_database:

my_connection = open_database_connection
locked=false
whilst not_locked and not_timed_out
  try to lock data
  set variables accordingly
end while
return the database connection to the application.

Now, heres the tricky bit we only want to lock the
table, and we only really want it for select
statements.

Its ugly and it could be nicer, that is hellishly
nasty code but it would work. I would like to know how
to do that in perl. Or a nicer way to do it which I
know for a fact there is.

Any help appreciated.

Ta 

David

=====
"Artificial Intelligence is no match for human stupitity!"

__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/

-- 
Gllug mailing list  -  Gllug at linux.co.uk
http://list.ftech.net/mailman/listinfo/gllug

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the originator.

This footnote also confirms that this email message has been checked
for the presence of computer viruses.

**********************************************************************

-- 
Gllug mailing list  -  Gllug at linux.co.uk
http://list.ftech.net/mailman/listinfo/gllug

-- 
Gllug mailing list  -  Gllug at linux.co.uk
http://list.ftech.net/mailman/listinfo/gllug

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the originator.

This footnote also confirms that this email message has been checked
for the presence of computer viruses.

**********************************************************************

-- 
Gllug mailing list  -  Gllug at linux.co.uk
http://list.ftech.net/mailman/listinfo/gllug

-- 
Gllug mailing list  -  Gllug at linux.co.uk
http://list.ftech.net/mailman/listinfo/gllug