Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Question on select for update in relation to locking

5 posts in JDBC Connect (product renamed to JConnect) Last posting was on 1997-04-17 22:18:28.0Z
Michiel Veen Posted on 1997-04-14 10:41:32.0Z
Message-ID: <335209DC.2754@tcf.nl>
Date: Mon, 14 Apr 1997 12:41:32 +0200
From: Michiel Veen <michiel@tcf.nl>
Reply-To: michiel@tcf.nl
Organization: The Connection Factory
X-Mailer: Mozilla 3.01 (WinNT; I)
MIME-Version: 1.0
Subject: Question on select for update in relation to locking
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Newsgroups: sybase.public.jdbcconnect
Lines: 59
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:1025
Article PK: 252777

Hi,

We are using Sybase 11, Jconnect and JDBC to make a Java application.
Eventually this application should work on all kind of databases. We
have one table (INIT) that holds all kind of key values. When we want to
insert something in one of the other tables, we fetch the key from INIT,
increment the key value and use the fetched key for inserting a row in a
table.

The fetch increment action should of course be an atomic operation and
has to run on the server. The question we have is how can we do this in
a smart way J

We found 3 possible solutions:

1: use RMI and the synchronized mechanism. In the RMI method we use a
select and a update, the synchronized mechanism will make these actions
atomic.

2: use servlets and the synchronized mechanism. Same idea but doesn’t
look good because the call to the method and return value have to go
through httpd.

3: use SQL like your example in the on-line documentation from Jconnect
(example below). However I don’t now how the locking of the
record/page/table works in combination with cursors. (i.e. do you have a
shared or an exclusive lock after the executeQuery of line 3) Also,
works this the same on other databases? Because in the end this
application should work other databases as well.

-------------------------- start example -----------------------------

1: Statement stmt1 = _connection.createStatement();
2: Statement stmt2 = _connection.createStatement();
3: ResultSet rs = stmt1.executeQuery("SELECT
au_id, au_lname, au_fname
FROM authors WHERE city = 'Oakland'
FOR UPDATE OF au_lname");
4: String cursor = rs.getCursorName();
5: String last_name = new String("Smith");
6: stmt2.executeUpdate("UPDATE authors
SET au_lname = '"last_name + "'
WHERE CURRENT OF " + cursor);

Any insight is greatly appreciated, I will post a summary if people are
interested.

Kind Regards,

Michiel

PS, my compliments on Sybase 11, I don’t work al lot with databases but
the whole product is very intuitive and the installation worked like a
dream.

--

#-- michiel@tcf.nl
#-- The Connection Factory b.v.
#-- http://www.tcf.nl


David Clegg Posted on 1997-04-16 17:07:17.0Z
Message-ID: <33550745.37D4FC32@sybase.com>
Date: Wed, 16 Apr 1997 10:07:17 -0700
From: David Clegg <davec@sybase.com>
X-Mailer: Mozilla 2.01 (X11; I; Linux 1.2.13 i586)
MIME-Version: 1.0
To: michiel@tcf.nl
Subject: Re: Question on select for update in relation to locking
References: <335209DC.2754@tcf.nl>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Newsgroups: sybase.public.jdbcconnect
Lines: 100
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:995
Article PK: 252747


> We are using Sybase 11, Jconnect and JDBC to make a Java application.
> Eventually this application should work on all kind of databases. We
> have one table (INIT) that holds all kind of key values. When we want to
> insert something in one of the other tables, we fetch the key from INIT,
> increment the key value and use the fetched key for inserting a row in a
> table.
>
> The fetch increment action should of course be an atomic operation and
> has to run on the server. The question we have is how can we do this in
> a smart way J
>
> We found 3 possible solutions:
>
> 1: use RMI and the synchronized mechanism. In the RMI method we use a
> select and a update, the synchronized mechanism will make these actions
> atomic.

RMI will only defer your problem to some middle-tier operation, which
will in turn need to do JDBC to access your database, and will have to
use its own mechanism to make the select and update atomic.

> 2: use servlets and the synchronized mechanism. Same idea but doesn’t
> look good because the call to the method and return value have to go
> through httpd.
Same problem.

>
> 3: use SQL like your example in the on-line documentation from Jconnect
> (example below). However I don’t now how the locking of the
> record/page/table works in combination with cursors. (i.e. do you have a
> shared or an exclusive lock after the executeQuery of line 3) Also,
> works this the same on other databases? Because in the end this
> application should work other databases as well.
I think you just want to use standard SQL transactional semantics
to accomplish your work. If your INIT table just has 1 column of
integers and you want to simply use it to give you unique keys for
other tables, you could to:


int myKey = 0;
connection.setAutoCommit(false);
rs = executeQuery("select max(keyColumn) from INIT");
if (rs.next)
{
myKey = rs.getInt();
}
executeUpdate("insert into INIT values (" + (myKey + 1) +")");
executeUpdate(... whatever query you want to do with your
unique key ...)
connection.commit();

This would be portable to any SQL-Compliant database, but would
also not be suitable for heavy concurrent users (the last row
in the INIT table would quickly become a hot-point in your system,
essentially synchronizing all database operations around that 1
transactional lock point).

All database vendors have solutions for dealing with this type of
issue. Check the www.sybase.com pages for white papers on generating
unique keys and optimistic concurrency if this is an issue for you.



>
> -------------------------- start example -----------------------------
>
> 1: Statement stmt1 = _connection.createStatement();
> 2: Statement stmt2 = _connection.createStatement();
> 3: ResultSet rs = stmt1.executeQuery("SELECT
> au_id, au_lname, au_fname
> FROM authors WHERE city = 'Oakland'
> FOR UPDATE OF au_lname");
You must use stmt1.setCursorName() before you execute the query if
you are going to use a cursor (otherwise we do not do cursor operations
on the SQL Server).

> 4: String cursor = rs.getCursorName();
> 5: String last_name = new String("Smith");
> 6: stmt2.executeUpdate("UPDATE authors
> SET au_lname = '"last_name + "'
> WHERE CURRENT OF " + cursor);
>
> Any insight is greatly appreciated, I will post a summary if people are
> interested.
>
> Kind Regards,
>
> Michiel
>
> PS, my compliments on Sybase 11, I don’t work al lot with databases but
> the whole product is very intuitive and the installation worked like a
> dream.
>
> --
>
> #-- michiel@tcf.nl
> #-- The Connection Factory b.v.
> #-- http://www.tcf.nl


Michiel Veen Posted on 1997-04-17 07:17:11.0Z
Message-ID: <3355CE77.2F7C@tcf.nl>
Date: Thu, 17 Apr 1997 09:17:11 +0200
From: Michiel Veen <michiel@tcf.nl>
Reply-To: michiel@tcf.nl
Organization: The Connection Factory
X-Mailer: Mozilla 3.01 (WinNT; I)
MIME-Version: 1.0
Subject: Re: Question on select for update in relation to locking
References: <335209DC.2754@tcf.nl> <33550745.37D4FC32@sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.jdbcconnect
Lines: 36
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:978
Article PK: 252729


David Clegg wrote:
>
> I think you just want to use standard SQL transactional semantics
> to accomplish your work. If your INIT table just has 1 column of
> integers and you want to simply use it to give you unique keys for
> other tables, you could to:
>
> int myKey = 0;
> connection.setAutoCommit(false);
> rs = executeQuery("select max(keyColumn) from INIT");
> if (rs.next)
> {
> myKey = rs.getInt();
> }
> executeUpdate("insert into INIT values (" + (myKey + 1) +")");
> executeUpdate(... whatever query you want to do with your
> unique key ...)
> connection.commit();
>
> This would be portable to any SQL-Compliant database, but would
> also not be suitable for heavy concurrent users (the last row
> in the INIT table would quickly become a hot-point in your system,
> essentially synchronizing all database operations around that 1
> transactional lock point).
>

Thanks for the reply, However I have one last question. What happens if
the Java client who is executing this code crashes after, lets say, the
first executeQuery. Are there no locks hanging around, effectively
blocking any further access to the INIT table....

If this is true I could move the code to a servlet on the server, or am
I now just paranoid :-)

Regards Michiel.


--

#-- michiel@tcf.nl
#-- The Connection Factory b.v.
#-- http://www.tcf.nl


David Clegg Posted on 1997-04-17 22:18:28.0Z
Message-ID: <3356A1B4.21914306@sybase.com>
Date: Thu, 17 Apr 1997 15:18:28 -0700
From: David Clegg <davec@sybase.com>
X-Mailer: Mozilla 2.01 (X11; I; Linux 1.2.13 i586)
MIME-Version: 1.0
To: michiel@tcf.nl
Subject: Re: Question on select for update in relation to locking
References: <335209DC.2754@tcf.nl> <33550745.37D4FC32@sybase.com> <3355CE77.2F7C@tcf.nl>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.jdbcconnect
Lines: 24
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:965
Article PK: 252716

If the Java client exits before it commits, then the connection
will be closed, and the SQL-Server will automatically rollback
the transaction.

If the java client just hangs w/out closing the connection... you
may have a problem.

dave

> > This would be portable to any SQL-Compliant database, but would
> > also not be suitable for heavy concurrent users (the last row
> > in the INIT table would quickly become a hot-point in your system,
> > essentially synchronizing all database operations around that 1
> > transactional lock point).
> >
>
> Thanks for the reply, However I have one last question. What happens if
> the Java client who is executing this code crashes after, lets say, the
> first executeQuery. Are there no locks hanging around, effectively
> blocking any further access to the INIT table....
>
> If this is true I could move the code to a servlet on the server, or am
> I now just paranoid :-)
>


ernie wright Posted on 1997-04-14 15:24:34.0Z
Message-ID: <33524C30.1CB1BD55@ibm.net>
Date: Mon, 14 Apr 1997 10:24:34 -0500
From: ernie wright <javaguy@ibm.net>
X-Mailer: Mozilla 4.0b3 [en] (Win95; I)
MIME-Version: 1.0
Subject: Re: Question on select for update in relation to locking
X-Priority: 3 (Normal)
References: <335209DC.2754@tcf.nl>
Content-Type: text/plain; charset=iso-8859-1
Newsgroups: sybase.public.jdbcconnect
Lines: 89
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:1022
Article PK: 252773

<HTML><BODY>
Michiel Veen wrote:

<BLOCKQUOTE TYPE=CITE>Hi,
<BR>
<BR>We are using Sybase 11, Jconnect and JDBC to make a Java application.
<BR>Eventually this application should work on all kind of databases. We
<BR>have one table (INIT) that holds all kind of key values. When we want to
<BR>insert something in one of the other tables, we fetch the key from INIT,
<BR>increment the key value and use the fetched key for inserting a row in
a
<BR>table.
<BR>
<BR>The fetch increment action should of course be an atomic operation and
<BR>has to run on the server. The question we have is how can we do this in
<BR>a smart way J
<BR>
<BR>We found 3 possible solutions:
<BR>
<BR>1: use RMI and the synchronized mechanism. In the RMI method we use a
<BR>select and a update, the synchronized mechanism will make these actions
<BR>atomic.
<BR>
<BR>2: use servlets and the synchronized mechanism. Same idea but doesn’t
<BR>look good because the call to the method and return value have to go
<BR>through httpd.
<BR>
<BR>3: use SQL like your example in the on-line documentation from Jconnect
<BR>(example below). However I don’t now how the locking of the
<BR>record/page/table works in combination with cursors. (i.e. do you have
a
<BR>shared or an exclusive lock after the executeQuery of line 3) Also,
<BR>works this the same on other databases? Because in the end this
<BR>application should work other databases as well.
<BR>
<BR>-------------------------- start example -----------------------------
<BR>
<BR>1: Statement&nbsp; stmt1 = _connection.createStatement();
<BR>2: Statement&nbsp; stmt2 = _connection.createStatement();
<BR>3: ResultSet&nbsp;&nbsp;&nbsp;&nbsp; rs = stmt1.executeQuery("SELECT
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
au_id, au_lname, au_fname
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
FROM authors WHERE city = 'Oakland'
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
FOR UPDATE OF au_lname");
<BR>4: String&nbsp;&nbsp;&nbsp; cursor = rs.getCursorName();
<BR>5: String last_name = new String("Smith");
<BR>6: stmt2.executeUpdate("UPDATE authors
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
SET au_lname = '"last_name + "'
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
WHERE CURRENT OF " + cursor);
<BR>
<BR>Any insight is greatly appreciated, I will post a summary if people are
<BR>interested.
<BR>
<BR>Kind Regards,
<BR>
<BR>Michiel
<BR>
<BR>PS, my compliments on Sybase 11, I don’t work al lot with databases but
<BR>the whole product is very intuitive and the installation worked like a
<BR>dream.
<BR>
<BR>--
<BR>
<BR>#-- michiel@tcf.nl
<BR>#-- The Connection Factory b.v.
<BR>#-- <A HREF="http://www.tcf.nl">http://www.tcf.nl</A>
</BLOCKQUOTE>
&nbsp;I am indeed interested.&nbsp; We are working on similar functionality
and I would appreciate a follow up post&nbsp;

</BODY>
</HTML>