SQL PassThrough and Transactions

[itflash]

Hi all

As autonumber is never guarenteed (different discussion), I need to generate my autonum in a SQL Server Table so I can account for every single ID used.

Therefore I have a table that holds the next ID.

My question is (add new record):

I connect using...

STORE SQLSTRINGCONNECT(mysqlstring) TO gnConnHandle
=SQLSETPROP(gnConnHandle,"Transactions",2)

I then update my ID table and insert a record into another table using this new ID during this transaction.

Is there a possiblity another user could be doing the same task and get a deadlock, or will it wait for the other users transaction to end first?

Thanks
ITflash

 

[ramani]

Hi

When a autonumber is used, the way I do is... to call the autonumber function as an ADD trigger from the table itself. SO the table is locked and the conflicts will not arise.





ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[Mike Lewis]

ITFlash,

Why do you say that the number cannot be guaranteed? (Sorry, I missed the other discussion you mentioned).

I have always used the IDENTITY attribute to generate the auto-number, and @@IDENTITY to return it to the client. That avoids the whole issue of having a separate key table.

Why doesn't that work for you?

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[itflash]

Thanks for the replies.

I have seen on Microsofts site (I'll try and dig it out somewhere) that there are occasions that the autonumber will get out of sync.

If I remember correctly, I think it is when the server crashes and reboots without you reseting something or other.

My client requires the number to be 99.99% correct, in sequence without any missing.

I was just looking for a more sure fire bet.

Anyone else any opinions out there?

 

[normanhk]

Hi itflash,

Do you mean that you want to guarantee that every users can insert into the table and get a unquie ID to identify the record hey inserted? As MySQL does not support transaction, I am using the following method.

=SQLEXEC(h,"LOCK TABLE XXX WRITE&quot
=SQLEXEC(h,"INSERT INTO XXX SET YYYYY&quot
=SQLEXEC(h,"SELECT ID FROM XXX ORDER BY ID DESC LIMIT 1&quot
SELECT sqlresult
mid = ID
=SQLEXEC(h,"UNLOCK TABLE&quot

h is the handler. ID is an auto-increment key field.

I don't know if there is any chance of getting wrong in unexpected condition such as system breakdown. But this codes have run for years without a single error (so much better than 99.99%).

Hope this help.
Norman

 

[itflash]

Yes, thats what I mean...like the identity field in SQL Server and autonumber in MS Access.

I have done somthing similiar to what you suggested, but was worried about deadlocks.

I posted onto the SQL Forum and did some additional research and think the identity field will be OK. I use transactions, so hopefully it will remain in sync.

If not, will have to relook at it another time.


Wonder what everyone else does? For example, accounts invoicing software requires it.


Thanks for the reply.
ITflash

 

[Mike Lewis]

ITflash,

You can never be sure there are no gaps in the numbering sequence. You will get a gap if a record is deleted. Or if a user begins to insert a record but then cancels. Or if a record is inserted but then the transaction is rolled back after another user has inserted a different record.

If you really want to be sure there are no gaps, you are right not to use an IDENTITY field and to generate your own keys instead.

So, going back to your original question .... the ideal solution would be to write a stored procedure. This would update the key table, then insert the new record, the whole thing being wrapped in BEGIN TRANSACTION ... COMMIT TRANSACTION.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[itflash]

Thanks for the reply.

This is what I was worried about.

But what you say, is that really the case?


For the insert:

My code is in a transaction, therefore if it crashes, then it shouldnt have allocated an ID.


Deletes

Users cannot delete.


Transaction

The transaction is only for a short time - when the user clicks save. Then it returns the ID. So it shouldnt impact with other users.



Tell me if I'm talking rubbish


ITflash

 

[itflash]

Just tried my scenario
<-------------------->

1. User clicks save.
2. I begin my SQL Pass Through.
3. Begin Transaction.
4. Insert into table.
5. Save/End Transaction.
6. If problem, rollback and Exit

I made the SQL Statement fail, and this didnt use an identity number.

Apart from Deletes, what other circumstances could mess up the identity?

 

[Mike Lewis]

Itflash,

At a quick glance, your scenario looks OK to me. As far as I can see, your identity numbers should be fine.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[itflash]

Thank you.


ITflash

 

[itflash]

I dont think there are other circumstances that could mess it up.

The user cannot delete and the transaction rollback takes care of the incorrect insert.


Its becoming an obsession



ITflash

 

[Mike Lewis]

ITflash,

Well, give it a try .. and let us know if you hit any problems.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[itflash]

Will do.......I'll be bankrupt...

 

[itflash]

Cannot leave it at 13 replies

 

[zhavic]

Is there a possiblity another user could be doing the same task and get a deadlock, or will it wait for the other users transaction to end first?

The answer is:
it will wait for the other users transaction to end first

And exacly, it will wait on your UPDATE statemet, because the row was updated in another transaction, that is still in progress.

You get deadlock, if you run your transaction two ( or more ) times in the same application at the same time with different gnConnHandle.
( e.g. run that transaction again before the first transaction ends )

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[itflash]

Thanks

That helps

ITflash

 

[itflash]

FYI

I concluded to use the identity in the end.

Hopefully, the transactions should protect the sequence and I wont let users delete records.


Thanks
ITflash

 

[camig1]

when you define your table in SQL server, you must to use the identity column atribute in table´s column you want to create the secuencial number, and when you create the new record you got the new secuencial number in that column.