0

two seperate exe (Process) is using same database and update database, how to syncronise database operation if user run both the exe for database updation in Delphi xe2. if we run the one exe it looks like hang till doing updation in database. I am using ODBC connection. Please suggest.

I am using control to connect to the database in both exe(Process). The Database I am using is Sybase SQL Anywhere 12.

Arioch 'The
  • 15,799
  • 35
  • 62
Ashu
  • 49
  • 1
  • 4
  • 2
    Databases do this with transactions – David Heffernan Apr 16 '13 at 22:19
  • Are you leaving both connections open the whole time? You should open your connection only when you are ready to update, then immediately close it - do this on both machines. If you are careful to only keep your connections open when necessary, Sybase should handle the rest and you will be in sync. If you keep both open all the time, you may run into locking/deadlock issues that will hold you up. (Still can happen if you close but much less likely). When something hangs, do you see an error messages or timeout at a certain point? – Vector Apr 17 '13 at 08:01
  • 1
    @user it usually makes sense to write Delphi version and libraries used to connect to server – Arioch 'The Apr 17 '13 at 08:31

1 Answers1

2

For what i can google, there are two versions of Anywhere aka WatcomSQL: server and UltraLite

1st question, of course, is whether you use transactions: http://en.wikipedia.org/wiki/Database_transaction

While most RDBMSes require explicit use of transactions, some simplified quazi-RDBMSes like MySQL or Advantage do not. And it looks SQL Anywhere also let you work without transactions. That si a trap: it may be easier to start without transaction, but it only works while you have single exclusive client. http://infocenter.sybase.com/help/topic/com.sybase.help.sqlanywhere.12.0.1/uladmin/fo-databases-s-5108100.html

If you cut the corners and made transaction-less programs that may be the time to refactor it. Or maybe Sybase Relay Server can help somewhat.

You may be using embedded database mode or some kind of local connection. If so, try to install Server as a separate application and connect to it by TCP/IP network despite it is physically run on the same machine.

http://infocenter.sybase.com/help/topic/com.sybase.help.sqlanywhere.12.0.1/dbadmin/da-dbconnections.html

IF you use transactions, then that may be during masive insert updates one transactions if blocking another. For example if you use numeric ID field there is a common database killer trap to assign new data ID's as Select MAX(ID) + 1 from TableName or it may be some other locking. http://en.wikipedia.org/wiki/Snapshot_isolation

While SQL Anywhere Server 12 is told to use versioning engine maybe it only uses it on some transactions and uses blocking in other transactional modes, subject to lock escalations ? Or Anywhere UltraLite does not use it perhaps ?

Well, anyway snapshot isolation in SQL Anywhere is based on first-writer-wins, which forces writers to block writers.

To sum it up - you have chances to have locks in DB transactional layer, in network layer and even in connection libraries. To monitor and detect all potential locking troubles you need a comprehensive knowledge of given database and which monitoring and testing tools are available for it and how to use it. It think you would easier find help on Sybase forums and in Sybase section of Stack Oмerflow, rather than in Delphi's. I think this answer is worth posting at https://dba.stackexchange.com/questions/tagged/sybase

Community
  • 1
  • 1
Arioch 'The
  • 15,799
  • 35
  • 62