Chak,
This topic needs a lot of discussion. In very short, you don't need
pessimistic locks to save data, in fact you should avoid them when you can.
There are 4 flavors of concurrency that donot involve pesssimistic locks.
1. Check only for PK during save
2. Check only for changed columns + PK
3. Check for all columns
4. Check for special column say timestamp in SQL Server.
Here is a bit more detail, DataSets encourage a disconnected data access
paradigm. In other words, you connect, fill data, disconnect, maintain a
change history (one level deep - enough to manage concurrency), then
reconnect check for various concurrency issues, save the data, or throw
appropriate exceptions (or simply set the RowError - the DataAdapter can do
that too).
This situation works nicely in the case of a single table scenario, but once
you have multiple related tables is where things start getting hairier.
In a multiple related table scenario, say you have two tables A & B
connected with a ForeignKeyConstraint. (A holds the PK, B holds the
referenecd column i.e. FK).
So in inserts, you need to insert A's rows first, then B (to maintain
referential integrity at the database)
Updates need to happen after inserts because you might be updating a freshly
inserted row. They also need to go in sequence A->B
Deletes need to happen in reverse order B->A
The problem is, if you implement the above solution, once you put it in
production, you will be hit with deadlocks i.e. transactions going from
A->B, blocking the ones going from B->A. So the better way out is to remain
disconnected, and during the update part, do this
Connect
Segregate the rows from B and A that will be updated, for a specific row in
A
Pessimistic Lock them using SELECT HOLD LOCK
Update them.
Disconnect
Move to the next set of rows
Go back to step 1 until done.
By explicitly pessimistic locking, any major DB like SQL Server/Oracle/DB2
will be able to detect deadlocks using deadlock resolution and clean up the
mess as need be. I.E. User Interfaces won't get blocked until CommandTimeout
(which you will agree is truly awful). Given the complexity hierarchical
changes introduce to your logic, you must try and use them only if you must.
Also, databases like MySql and certainly Access have no deadlock resolution
features, so you are caught dead in the water with those products.
Here is some further reading on this topic --
Concurrency -
http://codebetter.com/blogs/sahil.ma.../07/56645.aspx
Persisting Hierarchical Changes -
http://codebetter.com/blogs/sahil.ma.../06/62893.aspx
Also for this and more such real world problems and their solutions, be sure
to check out my upcoming book on ADO.NET
http://tinyurl.com/9bync.
- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book -
http://tinyurl.com/9bync
----------------------------------------------------------------------------
"chak" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>I assume that since datasets are disconnected , and database locks are
>connected, the only way to have pessimistic locks, and yet use datasets
>would be to use application level locks, instead of database locks. Right ?
>
> Regards,
>
> Chak.
>
Similar Threads
