Re: ensuring row/table locking

[Tobias Downer <toby@xxxxxxxxx>]

Unfortunately, there is no SQL standard for handling table/row level 
locking in a database.  The SQL standard does not specify exactly how 
the SERIALIZATION transaction isolation should be implemented.  For 
example, some database engines implement serialization transaction 
isolation by locking the records preventing access by other transactions 
and informing the client if deadlock occurs.  Databases such as Oracle, 
PostgreSQL and Mckoi implement a versioning system that does not lock 
any rows and allows multiple concurrent changes to a row, and detects 
conflict at commit time.

I have considered implementing an atomic row/table level locking system 
with deadlock detection to Mckoi that could be used on top of the 
current versioning model, however this would not be a transparent change 
and you would need to add queries such as 'LOCK ROWS SELECT FROM MyTable 
WHERE id = 20'.  I don't think this would satisfy your cross-DB 
requirements either.

One possibility I can think of is to implement locking yourself with a 
small 'lock management' server as part of your application.  It would 
run by the side of the database application and negotiate locking 
between all your clients.  This is the only way I can think to implement 
a system that will work across all DB software.

Toby.

Aaron Westendorf wrote:
> Is there a cross-DB method of ensuring that a thread or process has 
> exclusive access to a table or row?  Within a single process this can be 
> easily done with synchronization, but if multiple processes are 
> accessing the DB, can I use SQL standards to accomplish this task?
> 
> -Aaron