| select first unlock record in table for update [message #556800] |
Wed, 06 June 2012 11:44  |
 |
belf
Messages: 11
Registered: June 2012
|
Junior Member |
|
|
Hi all,
I'm looking for a solution to select the first row that is not currently locked in a table and insert a record to another table that reference that first row. this is my scenario:
create table ticket
(
id number(10) not null,,
ticket_type number(1) not null,,
is_sold number(1) not null,
CONSTRAINT ticket_pk PRIMARY KEY (id)
);
create table customer_ticket
(
cust_id number(10),
cust_name varchar2(50)
ticket_id number(1),
CONSTRAINT ticket_fk
FOREIGN KEY (ticket_id)
REFERENCES ticket(id)
);
SQL> select * from ticket;
id ticket_type is_sold
------------ -------------------- -----
10000004 1 1
10000005 2 1
10000006 1 0
10000007 1 0
10000008 2 0
10000009 2 0
SQL> select * from customer_ticket;
cust_id cust_name ticket_id
------------ -------------------- ----------
1 John 10000004
2 Sara 10000005
my goal is finding the first free ticket ( not sold ) in the ticket table and insert buyer information of that ticket in customer_ticket table. at last I will mark that ticket as a sold one in ticket table with update.
Problem is that the first transaction locks the the first row in ticket table and the second transaction running the same query goes to wait untill the first transaction commit or rollback. However when first transaction finish successfully, second transaction select duplicate id from ticket table that was selected by the first transaction!
I tried to solve problem with "skip locked" and "nowait" options with select for update, but they didn't help.
Thank you for any help...
Regards,
Belf
|
|
|
|
|
|
|
|
|
|
| Re: select first unlock record in table for update [message #556809 is a reply to message #556803] |
Wed, 06 June 2012 13:17  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
I'm surprised that you're using pesimistic locking rather than optimistic - it's very old-fashioned for many reasons and is generally unsuitable for web apps.
You can skip over locked records like this ==> http://www.orafaq.com/forum/m/522598/45693/#msg_522598
An alternative would be to logically lock records for some pre-determined period. Say your business rule allows a record to only be locked for 5 minutes, then you'd update the record bing worked on with the userid and sysdate and then commit. You'd probably also keep pushing out the lock timestamp as long as the user is still working the record. When the timstamp is older than 5 min, then it's available to be tagged and logically locked by another user. Finding unlocked records then becomes trivial based on the lock timestamp.
[Updated on: Wed, 06 June 2012 13:19] Report message to a moderator |
|
|
|
| Re: select first unlock record in table for update [message #556810 is a reply to message #556803] |
Wed, 06 June 2012 13:17 |
|
belf
Messages: 11
Registered: June 2012
|
Junior Member |
|
|
This is my simplified code:
function sellTicket(I_ticket_type IN number,
I_cust_id IN number,
I_cust_name IN varchar2
) return number is
v_mycursor ref cursor;;
vr_mycursor ticket%rowtype;
vt_return NUMBER := 0;
begin
open mycursor for
select id,ticket_type,is_sold
from ticket where id = ( select min(id) from ticket where ticket_type= I_ticket_type and is_sold=0);
for update;
fetch v_mycursor
into vr_mycursor;
if v_mycursor%notfound then
Rollback;
vt_return := 2;
else
insert into customer_ticket
(cust_id,
cust_name,
ticket_id )
values
(I_cust_id,
I_cust_name,
vr_mycursor.id);
update ticket o set o.is_sold = 1
where o.id = vr_mycursor.id;
vt_return := 0;
commit;
end if;
RETURN vt_return;
Exception
when DUP_VAL_ON_INDEX then
Rollback;
return 23;
when others then
raise_application_error(-20000, sqlerrm);
end sellTicket;
I run this function from 2 different sessions. session 2 waits on " open mycursor for..." until committing the operation in session 1.
I check with "nowait" option but it raise application error and session 2 couldn't assign ticket to customer. I also checked with "skip locked" option but result is " if v_mycursor%notfound then ... vt_return := 2;". I expect session 2 can select another available free ticket in table.
I hope this make the problem more clear.
Regards,
Belf
|
|
|
|
|
|
| Re: select first unlock record in table for update [message #556916 is a reply to message #556840] |
Thu, 07 June 2012 05:01 |
|
belf
Messages: 11
Registered: June 2012
|
Junior Member |
|
|
Dear Michel
Thank you for your response. I am sorry if I didn't explain the problem properly. Plz, follow the post to get your response.
Quote:
"Rollback/commit" NO, never in a procedure, ONLY the caller knows if the work does by the procedure (and the previous work) has to be rolled bacn or commited, NOT the procedure
I understand using commit inside a procedure would defeat the basic definition of an atomic transaction and The application that calls the stored procedure should start and commit/rollback the transaction, but in our company the situation is relatively different. Application connects to multiple databases and some of these databases use commit/rollback in their procedure and removing these commits/rollbacks from those are not possible now ( these databases have many packages including many procedures inside them and modifying them is so time-consuming ). So, I should handle commit/rollback inside my code as others.
Quote:
"when others then raise_application_error(-20000, sqlerrm);" this simply STUPID, why do you think it does more than if you don't write it? To know what it does less, why it is an error read WHEN OTHERS.
about using "when others" exception, you are right it is mistake. In fact, I don't throw the exception to application and I catch it and instead return a value to application. So, I do first rollback the operation in "when others" and then return value.
Quote:You open but do not close the cursor
You are right, I should close it.
Quote:
Why use a ref cursor and not a static cursor?
As I mentioned earlier, this code is simplified. I have several other inputs in my functions that affect my where condition in selecting unsold ticket. Each of these input may be null or not, So I check them by if statement and each null input will be removed from where condition. However, if I want to use static cursor in replace of ref cursor, I should declare several static cursors in my function while only one of them every time I use.
Regards,
Belf
|
|
|
|
|
|
| Re: select first unlock record in table for update [message #556940 is a reply to message #556800] |
Thu, 07 June 2012 07:36 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
belf wrote on Wed, 06 June 2012 12:44I tried to solve problem with "skip locked" and "nowait" options with select for update, but they didn't help.
Obviously, SKIP LOCKED will not help the way you wrote a query. SELECT always sees all rows, therefore subquery:
select min(id) from ticket where ticket_type=1 and is_sold=0
will return same is for both sessions. So if session 1 has id selected for update,session 2 without SKIP LOCKED will wait and with SKIP LOCKED will not return any rows since it will skip row locked by session 1 and there are no more rows with min(id). What you need is loop through select all rows in table ticket ordered by ID and then trying to select that row FOR UPDATE SKIP LOCKED. If row is locke and therefore skipped query will raise NO_DATA_FOUND which you catch and mask. Otherwise it is the row you are looking for. For example:
-- Session 1
declare
cursor v_cur
is
select id
from ticket
where ticket_type=1
and is_sold=0
order by id;
v_id number;
begin
for v_rec in v_cur loop
begin
select id
into v_id
from ticket
where id = v_rec.id
for update
skip locked;
exception
when no_data_found
then
dbms_output.put_line('ID = ' || v_rec.id || ' is locked.');
end;
exit when v_id is not null;
end loop;
if v_id is null
then
dbms_output.put_line('No qualifying tickets.');
else
dbms_output.put_line('ID = ' || v_id);
end if;
end;
/
ID = 10000006
PL/SQL procedure successfully completed.
SQL>
And
-- Session 1
declare
cursor v_cur
is
select id
from ticket
where ticket_type=1
and is_sold=0
order by id;
v_id number;
begin
for v_rec in v_cur loop
begin
select id
into v_id
from ticket
where id = v_rec.id
for update
skip locked;
exception
when no_data_found
then
dbms_output.put_line('ID = ' || v_rec.id || ' is locked.');
end;
exit when v_id is not null;
end loop;
if v_id is null
then
dbms_output.put_line('No qualifying tickets.');
else
dbms_output.put_line('ID = ' || v_id);
end if;
end;
/
ID = 10000006 is locked.
ID = 10000007
PL/SQL procedure successfully completed.
SQL>
SY.
[Updated on: Thu, 07 June 2012 07:37] Report message to a moderator |
|
|
|
|
|
| Re: select first unlock record in table for update [message #556962 is a reply to message #556957] |
Thu, 07 June 2012 11:01 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Sure. Use NOWAIT instead. Session1:
declare
v_resource_busy exception;
pragma exception_init(v_resource_busy,-54);
cursor v_cur
is
select id
from ticket
where ticket_type=1
and is_sold=0
order by id;
v_id number;
begin
for v_rec in v_cur loop
begin
select id
into v_id
from ticket
where id = v_rec.id
for update
nowait;
exception
when v_resource_busy
then
dbms_output.put_line('ID = ' || v_rec.id || ' is locked.');
end;
exit when v_id is not null;
end loop;
if v_id is null
then
dbms_output.put_line('No qualifying tickets.');
else
dbms_output.put_line('ID = ' || v_id);
end if;
end;
/
ID = 10000006
PL/SQL procedure successfully completed.
SQL>
Session 2:
declare
v_resource_busy exception;
pragma exception_init(v_resource_busy,-54);
cursor v_cur
is
select id
from ticket
where ticket_type=1
and is_sold=0
order by id;
v_id number;
begin
for v_rec in v_cur loop
begin
select id
into v_id
from ticket
where id = v_rec.id
for update
nowait;
exception
when v_resource_busy
then
dbms_output.put_line('ID = ' || v_rec.id || ' is locked.');
end;
exit when v_id is not null;
end loop;
if v_id is null
then
dbms_output.put_line('No qualifying tickets.');
else
dbms_output.put_line('ID = ' || v_id);
end if;
end;
/
ID = 10000006 is locked.
ID = 10000007
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
|
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
