Quantcast

HyperSQL (HSQLDB) explicit shared (read) lock syntax

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

HyperSQL (HSQLDB) explicit shared (read) lock syntax

Mihalcea Vlad
Hi,
 
According to HSQLDB documentation, there are two select-for-updating directives:

- FOR UPDATE
- FOR READ ONLY

"If the SQL qualifier, FOR UPDATE is used, then all the updatable columns of the result set become updatable.

If a cursor is declared with FOR READ ONLY, then it is not updatable.

In HSQLDB, if FOR READ ONLY or FOR UPDATE is not used then all the updatable columns of the result set become updatable. This relaxes the SQL standard rule that in this case limits updatability to only simply updatable SELECT statements (where all columns are updatable)."

If the FOR UPDATE takes an exclusive (write) lock, does the FOR READ ONLY directive take a shared (read) lock instead?

Vlad Mihalcea
 

------------------------------------------------------------------------------
Dive into the World of Parallel Programming. The Go Parallel Website,
sponsored by Intel and developed in partnership with Slashdot Media, is your
hub for all things parallel software development, from weekly thought
leadership blogs to news, videos, case studies, tutorials and more. Take a
look and join the conversation now. http://goparallel.sourceforge.net/
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: HyperSQL (HSQLDB) explicit shared (read) lock syntax

Fred Toussi-2
Hi,
 
I assumed there's a construct similar to FOR UPDATE/FOR SHARE that PostgreSQL has:
 
 
FOR UPDATE - takes an explicit lock on the selected rows
FOR SHARE - takes a shared lock on the selected rows
PostgreSQL behaviour is not relevant here.
 
The current session obtains the locks that I mentioned. When you use FOR READ ONLY, you cannot update the rows of the result set (that is, update via JDBC ResultSet methods).

You can still update the rows of the table by using UPDATE atable SET ... statements in the same session.
 
Fred
 
 
 
So, related to FOR READ ONLY, I still didn't get this part "you cannot update the rows of the result set."
 
The Select ResultSet only retrieves the database rows, but it requires an UPDATE statement to apply any change. So, the FOR READ ONLY prevents any update to that rows in this particular transaction, or all concurring transactions till the end of the current transaction?
 
Vlad
 
On Friday, January 30, 2015 3:02 PM, Fred Toussi <[hidden email]> wrote:

The SELECT ... FOR UPDATE directives primarily dictate what you can do with the result set. If FOR READ ONLY is used, you cannot update the rows of the result set.
 
The lock that is created depends on the transaction model. In 2 phase lock (LOCKS) mode exclusive and shared locks are introduced on the table. In MVCC mode no lock is introduced at all and any conflict due to updates by multiple sessions is handled when it occurs.
 
Fred
 
On Fri, Jan 30, 2015, at 08:02, Mihalcea Vlad wrote:
Hi,
 
According to HSQLDB documentation, there are two select-for-updating directives:

- FOR UPDATE
- FOR READ ONLY

"If the SQL qualifier, FOR UPDATE is used, then all the updatable columns of the result set become updatable.

If a cursor is declared with FOR READ ONLY, then it is not updatable.

In HSQLDB, if FOR READ ONLY or FOR UPDATE is not used then all the updatable columns of the result set become updatable. This relaxes the SQL standard rule that in this case limits updatability to only simply updatable SELECT statements (where all columns are updatable)."

If the FOR UPDATE takes an exclusive (write) lock, does the FOR READ ONLY directive take a shared (read) lock instead?
 
Vlad Mihalcea
 
------------------------------------------------------------------------------
Dive into the World of Parallel Programming. The Go Parallel Website,
sponsored by Intel and developed in partnership with Slashdot Media, is your
hub for all things parallel software development, from weekly thought
leadership blogs to news, videos, case studies, tutorials and more. Take a
look and join the conversation now. http://goparallel.sourceforge.net/
_______________________________________________
Hsqldb-user mailing list

------------------------------------------------------------------------------
Dive into the World of Parallel Programming. The Go Parallel Website,
sponsored by Intel and developed in partnership with Slashdot Media, is your
hub for all things parallel software development, from weekly thought
leadership blogs to news, videos, case studies, tutorials and more. Take a
look and join the conversation now. http://goparallel.sourceforge.net/
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: HyperSQL (HSQLDB) explicit shared (read) lock syntax

Mihalcea Vlad
Thanks for the notification.

So with a FOR READ ONLY construct you cannot issue the following operations:

rs.absolute(5);
rs.updateString("NAME", "AINSWORTH"); // updates the
rs.updateRow(); // updates the row in the data source
Got it.

Vlad
 



On Friday, January 30, 2015 3:44 PM, Fred Toussi <[hidden email]> wrote:


Hi,
 
I assumed there's a construct similar to FOR UPDATE/FOR SHARE that PostgreSQL has:
 
 
FOR UPDATE - takes an explicit lock on the selected rows
FOR SHARE - takes a shared lock on the selected rows
PostgreSQL behaviour is not relevant here.
 
The current session obtains the locks that I mentioned. When you use FOR READ ONLY, you cannot update the rows of the result set (that is, update via JDBC ResultSet methods).

You can still update the rows of the table by using UPDATE atable SET ... statements in the same session.
 
Fred
 
 
 
So, related to FOR READ ONLY, I still didn't get this part "you cannot update the rows of the result set."
 
The Select ResultSet only retrieves the database rows, but it requires an UPDATE statement to apply any change. So, the FOR READ ONLY prevents any update to that rows in this particular transaction, or all concurring transactions till the end of the current transaction?
 
Vlad
 
On Friday, January 30, 2015 3:02 PM, Fred Toussi <[hidden email]> wrote:

The SELECT ... FOR UPDATE directives primarily dictate what you can do with the result set. If FOR READ ONLY is used, you cannot update the rows of the result set.
 
The lock that is created depends on the transaction model. In 2 phase lock (LOCKS) mode exclusive and shared locks are introduced on the table. In MVCC mode no lock is introduced at all and any conflict due to updates by multiple sessions is handled when it occurs.
 
Fred
 
On Fri, Jan 30, 2015, at 08:02, Mihalcea Vlad wrote:
Hi,
 
According to HSQLDB documentation, there are two select-for-updating directives:

- FOR UPDATE
- FOR READ ONLY

"If the SQL qualifier, FOR UPDATE is used, then all the updatable columns of the result set become updatable.

If a cursor is declared with FOR READ ONLY, then it is not updatable.

In HSQLDB, if FOR READ ONLY or FOR UPDATE is not used then all the updatable columns of the result set become updatable. This relaxes the SQL standard rule that in this case limits updatability to only simply updatable SELECT statements (where all columns are updatable)."

If the FOR UPDATE takes an exclusive (write) lock, does the FOR READ ONLY directive take a shared (read) lock instead?
 
Vlad Mihalcea
 
------------------------------------------------------------------------------
Dive into the World of Parallel Programming. The Go Parallel Website,
sponsored by Intel and developed in partnership with Slashdot Media, is your
hub for all things parallel software development, from weekly thought
leadership blogs to news, videos, case studies, tutorials and more. Take a
look and join the conversation now. http://goparallel.sourceforge.net/
_______________________________________________
Hsqldb-user mailing list

------------------------------------------------------------------------------
Dive into the World of Parallel Programming. The Go Parallel Website,
sponsored by Intel and developed in partnership with Slashdot Media, is your
hub for all things parallel software development, from weekly thought
leadership blogs to news, videos, case studies, tutorials and more. Take a
look and join the conversation now. http://goparallel.sourceforge.net/

_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user



------------------------------------------------------------------------------
Dive into the World of Parallel Programming. The Go Parallel Website,
sponsored by Intel and developed in partnership with Slashdot Media, is your
hub for all things parallel software development, from weekly thought
leadership blogs to news, videos, case studies, tutorials and more. Take a
look and join the conversation now. http://goparallel.sourceforge.net/
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Loading...