DbProviderFactory and transactions.

[BLUE]

Is there any way to use transactions if I use DbProviderFactory class to
abstract from the provider?

If not, is there any way to manage concurrency in a "universal"/provider
indipendent mode?

I've seen that when a DataSet is used (optimistic lock), if the update
detect concurrency problems, it is possible to inform the user with a
MessageBox or on the ASP page or in any other GUI way.

I've 2 or more handheld devices that sync with a DB through a Web Service
and I don't want the handhelds to stop syncing because of a concurrency
problem: I'd like the WS to handle the problem for example cycing until it
succeeds updating the DB or something like that.

In such situations how can concurrency be managed?
What is the best practice?


Thanks a lot,
Luigi.

 

[Nicholas Paldino [.NET/C# MVP]]

Luigi,

If you are using the DbProviderFactory abstract class, then when you
call CreateConnection, you can then call BeginTransaction to get a
DbTransaction instance which you can use to manage a transaction.

Personally, though, I would use the TransactionScope class in the
System.Transactions namespace, and wrap your calls to the DbProviderFactory
in that. If the underlying provider supports it (and it should if it works
with the DbProviderFactory in .NET 2.0) then it will auto enlist in the
transaction, and you won't have to manually handle it yourself.

 

[BLUE]

Personally, though, I would use the TransactionScope class in the

System.Transactions namespace, and wrap your calls to the
DbProviderFactory in that

Thank you very very much!!!
I didn't know about it but in 5 minutes I've seen it's easy to use and
powerful.


I'll use transactions so if there are problems I can roll back all inserts
and update I've done inside a transaction scope.

I want to create a string and insert it into a record of the DB only if not
present: if it is present I want to generate another string and retry and so
on (this is the scenario when multiple users are inserting into my tables
and I need to check primary key is unique before inserting it and not wait
the eventual exception).
Can I loop until an insertion goes well or there is a better way (best
practice)?


Thanks,
Luigi.

 

[Nicholas Paldino [.NET/C# MVP]]

Luigi,

How is it that you are generating your primary key where you can get
conflicts?

 

[BLUE]

How is it that you are generating your primary key where you can get

conflicts?

I've a varchar field where I put the ID of an RFID tag, but if the app user
do not want to use this ID I'll generate it automatically with C# NewGuid
method converting the guid to an alphanumeric string without dashes (so it
is consistent with RFID ID).

From MSDN we know that there is a possibility to have two equal Guid:

"A GUID is a 128-bit integer (16 bytes) that can be used across all
computers
and networks wherever a unique identifier is required.

There is a very low probability that the value of a new Guid is all zeroes
or equal to any other Guid."


Since I think it's wrong to rollback handheld syncing when this stupid
errors occurs I'm trying to prevent at least them.


Thanks,
Luigi.

 

[Nicholas Paldino [.NET/C# MVP]]

Luigi,

Honestly, I would not code against this issue. The chances of two
generated GUIDs being the same is almost infintesimally small (you would
have to generate 5000 GUIDs a second for the next billion years or something
to get conflicting ids).

I wouldn't waste the time coding against this.

 

[BLUE]

Ok thanks for the help!


Now the only problem is I've four tables and:

- I need to insert data in the first if not present

Using transactions if when I check I see I can proceed to insertion and
another concurrent access arrives before my insertion, I'll get an exception
as soon as I do executeNonQuery?


- I need to do noop if I'm trying to insert data in all the tables and one
field has the same value of that in the data I want to insert

How can I check this at the moment of insertion?
Maybe I've to create a good SQL query with a select in the where clause of
an insert?


Thanks a lot for your help!
Luigi.

 

[Nicholas Paldino [.NET/C# MVP]]

Luigi,

See inline:

- I need to insert data in the first if not present

Using transactions if when I check I see I can proceed to insertion and
another concurrent access arrives before my insertion, I'll get an
exception as soon as I do executeNonQuery?

You won't have an exception. If you have a serializable transaction and
you are inserting the record into the database, the other transaction that
tries to perform an operation will block until you are done with the first
transaction.

If you are looking to update this data in the event that the record is
already inserted, then you should select the data out first (or do something
to obtain the lock) and then if you get any rows back, perform an update,
otherwise, perform an insert.

- I need to do noop if I'm trying to insert data in all the tables and one
field has the same value of that in the data I want to insert

How can I check this at the moment of insertion?
Maybe I've to create a good SQL query with a select in the where clause of
an insert?

Why bother? If you are performing an update, and the fields that you
are updating are the same as the fields that are already there, then its the
same thing as a no-op. There is no reason to check every field in the
table.

 

[Marc Gravell]

A minor note - when using SELECT in this way to obtain a lock, it can
be advisable to use the UPDLOCK hint; otherwise you can get
lock-escalation deadlocks. I'd also try to push as much of this down
the the DB in a single ADO command as possible - not two commands (one
SELECT, one INSERT/UPDATE).

If the data is *mostly* UPDATEs (i.e. a state table), then one option
here is:

UPDATE blah
SET ...
WHERE {expected record details}

IF @@ROWCOUNT = 0 -- not there yet, so insert
BEGIN
INSERT blah
VALUES (...)
END

This will then avoid the extra operation for your most common usage,
and deals with the lock escalation issue itself (rather than you
having to tell it).

As for the all-fields-being-equal being a no-op, that isn't *entirely*
true; triggers etc will still fire. Depending on your usage
(DataTable, ORM or bespoke), you might choose to maintain a "dirty"
flag to prevent trivial updates, coupled with concurrency checking
(e.g. a timestamp) to prevent lost updates (i.e. the database being
updated while you weren't looking).

Marc

 

[BLUE]

I must better explain what I'm doing.


Table Actions:
ActionId (PK)
ActionType (Unique1)
EmployeeId (Unique1)

ActionType is 1 or 2; if I have an action of type 1 into the DB I have not
to insert it another time so I think I've to do a SELECT and if it returns 0
rows an INSERT.
Since there are concurrency problems I can do an INSERT wich has in the
WHERE clause a SELECT so the problem should be solved because I think a
query is executed atomically right?
If not, using Transaction scope the others concurrent acesses wait as if a
lock has been done?


After having succeeded in the previous insertion, I've to insert data in 3
others tables and all in the same TransactionScope of the previous insert.
These data are about an operation on an asset with a certain AssetId.
If I have to insert an operation on AssetId e.g. 123 and ActionType is 1 I
can insert this operation only if an action of ActionType 2 has already been
done on that AssetId else do noop.
If I have to insert an operation on AssetId e.g. 123 and ActionType is 2 I
can insert this operation only if an action of ActionType 1 has already been
done on that AssetId else do noop.

Since there are concurrency problems I can do an INSERT wich has in the
WHERE clause a SELECT so the problem should be solved because I think a
query is executed atomically right?
If not, using Transaction scope the others concurrent acesses wait as if a
lock has been done?


As for the UPDLOCK, since I'm trying to do a provider indipendent
application I'd like to use only "standard SQL queries" or ways offered by
C#: I think it is possible else why Microsoft have created DbProviderFactory
(it would be useless if having a factory we use provider specific SQL
languages).


Thanks,
Luigi.

 

[Marc Gravell]

Yes - I *believe* that doing an INSERT with a SELECT clause should
take out the lock from the outset ("key range" or equivalent, I
suspect), removing the need for UPDLOCK to prevent escalation
deadlocks [let me know if you want an explanation]. Your approach
looks reasonably sound (from the detail give); the TransactionScope
(at serializable isolation - the default) should make life pretty
simple for you; this deliberately causes blocking (as necessary) to
prevent concurrency chaos from competing connections.

Marc