But you can execute SQL Script to execute server-side tx: http://www.orientechnologies.com/docs/last/orientdb.wiki/SQL-batch.html
Lvc@ On 17 October 2014 17:06, Luca Garulli <[email protected]> wrote: > Hi Alan, > This has been moved to 2.2, sorry. > > Lvc@ > > On 17 October 2014 14:43, Alan Johnson <[email protected]> wrote: > >> Luca, have the issues raised in this thread been resolved in the v2 drop? >> If partial, in what way? >> >> Thanks! >> >> Alan >> >> On Monday, July 23, 2012 7:58:45 AM UTC-4, Lvc@ wrote: >>> >>> Hi, >>> not yet. This is something we will think for 2.0. >>> >>> Lvc@ >>> >>> On 23 July 2012 13:27, Danny Thomas <[email protected]> wrote: >>> > Luca, are distributed transactions also on the road map in that kind >>> of >>> > timescale? >>> > >>> > Danny >>> > >>> > >>> > On Monday, 23 July 2012 11:01:02 UTC+1, Lvc@ wrote: >>> >> >>> >> Hi Mic, >>> >> you're right: transactions against the "remote" protocol have some >>> >> limitations we'd like to fix in 1.2. Probably not al of them but >>> most. >>> >> >>> >> Lvc@ >>> >> >>> >> On 22 July 2012 02:41, Mich Geni <[email protected]> wrote: >>> >>> >>> >>> Hi Luca, >>> >>> >>> >>> What are the plans in the roadmap for implementing support for real >>> ACID >>> >>> local, remote and distributed transactions, coherently? >>> >>> >>> >>> Reference Links: >>> >>> >>> >>> http://code.google.com/p/orient/issues/detail?id=442 >>> >>> http://code.google.com/p/orient/issues/detail?id=931 >>> >>> http://code.google.com/p/orient/issues/detail?id=784 >>> >>> http://code.google.com/p/orient/issues/detail?id=90 >>> >>> http://code.google.com/p/orient/issues/detail?id=12 >>> >>> http://code.google.com/p/orient/issues/detail?id=890 >>> >>> http://code.google.com/p/orient/issues/detail?id=484 >>> >>> http://code.google.com/p/orient/issues/detail?id=713 >>> >>> http://code.google.com/p/orient/issues/detail?id=181 >>> >>> https://groups.google.com/d/msg/orient-database/7zvllV_ >>> ItYo/WM1rg9cyozIJ >>> >>> https://groups.google.com/forum/#!topic/orient-database/sqo84YE6UV8 >>> >>> ...and other posts by users on OrientDB google group >>> >>> >>> >>> >>> >>> To have a real ACID Transactions compliant database, we need serious >>> >>> consideration of the issues involved. >>> >>> >>> >>> I think, we need a complete new mindset, and a world-view to create >>> a >>> >>> base for supporting transactions. >>> >>> >>> >>> SideNote: Transactions are a necessity. We cannot go to classical >>> era of >>> >>> RDBMSes for transactions support. All new applications surround the >>> NoSQL >>> >>> solutions. There are so many use-cases in modern applications, where >>> >>> consistency is a must. So who is the first to fully support them? >>> will be a >>> >>> clear winner. >>> >>> >>> >>> >>> >>> Regards, >>> >>> >>> >>> Mic >>> >>> >>> >>> >>> >>> On Monday, 2 July 2012 22:53:22 UTC+5, Lvc@ wrote: >>> >>>> >>> >>>> On 1 July 2012 11:43, Mich Geni <[email protected]> wrote: >>> >>>>> >>> >>>>> Hi Luca, >>> >>>>> >>> >>>>> I am glad to see caring and thoughtful people here. >>> >>>>> Yes, a combination of client and server involvement in the >>> transaction >>> >>>>> will be much beneficial. >>> >>>>> >>> >>>>> e.g. to elaborate it more: >>> >>>>> >>> >>>>> When a client begins a transaction, it notifies the server. The >>> server >>> >>>>> creates a transaction slot/session for the client. Which is also >>> an >>> >>>>> indication (to server) from client, that write the changes only >>> when I say >>> >>>>> commit. And behave accordingly. >>> >>>> >>> >>>> +1 >>> >>>>> >>> >>>>> The transaction can have a timeout (on server), in case the client >>> >>>>> begins a transaction and never commits it, or disconnects in the >>> middle. In >>> >>>>> that case the slot/session will be destroyed, and all changes >>> discarded. >>> >>>> >>> >>>> +1 >>> >>>>> >>> >>>>> During the transaction: the changes in data client make, should >>> remain >>> >>>>> at client side, to prevent unnecessary data transfer. But when >>> client needs >>> >>>>> something from server, the server cooperates in a manner (and send >>> it to the >>> >>>>> client such) that the client thinks it as actual information. We >>> can call it >>> >>>>> transaction contextual information. In this way, the client will >>> have >>> >>>>> complete (but fictional and also sometimes real) information >>> necessary for >>> >>>>> the transaction. So server's transaction awareness is crucial. >>> >>>> >>> >>>> +1 >>> >>>>> >>> >>>>> We can have a some special format/notation/range for RIDs during >>> >>>>> transactions. This will be a flag for client, server and >>> developer/admin, >>> >>>>> that this information is contextual. But these RIDs should not be >>> isolated >>> >>>>> only at client side, such that the server has no information about >>> them. But >>> >>>>> I think such contextual RIDs should be provided by server on >>> client's >>> >>>>> request, and booked for it until free'd by client. So that client >>> can use it >>> >>>>> for its transaction, and server can map/translate them later after >>> commit to >>> >>>>> actual/real RIDs. >>> >>>> >>> >>>> This is already implemented with transient RIDs having negative >>> numbers, >>> >>>> example: #3:-4 >>> >>>>> >>> >>>>> The client will create a changeset (a small graph, a set of >>> documents >>> >>>>> etc), containing some information to be made real/actual or >>> decided on >>> >>>>> server only (or in other words leave some patches in the changeset >>> for the >>> >>>>> server to cover up). And send this information to server for >>> commit. The >>> >>>>> server will validate those changes (check if valid and possible) >>> and write >>> >>>>> the changes as permanent. >>> >>>> >>> >>>> This is already in this way. >>> >>>>> >>> >>>>> People using the transactions API, should be educated to do only >>> atomic >>> >>>>> operations in transactions. (an atom is very very small, by the >>> way). So >>> >>>>> bulk (insertions/updation/deletion) operations should not be done >>> in >>> >>>>> transactions. As transactions are meant to make small changes and >>> to ensure >>> >>>>> data integrity. In this way, the data exchanged between client and >>> server >>> >>>>> will be minimum for a transaction. >>> >>>> >>> >>>> This is partially true. Now transactions are used to speed up >>> "remote" >>> >>>> operations by grouping them in transactions of X operations. This >>> is used >>> >>>> setting the tx.useTxLog(false). >>> >>>>> >>> >>>>> In case: a record X (set of records, or portion of graph) was read >>> by a >>> >>>>> client A at time t1. Then at time t2 (i.e. t2 > t1), client B >>> reads and make >>> >>>>> changes in it/them, in a transaction. Then at time t3 (i.e. t3 > >>> t2) , when >>> >>>>> client A wants to make changes in it/them, the server should give >>> an error, >>> >>>>> that your version of information needs to read again before making >>> any >>> >>>>> changes, because it was recently changed. As the server knows >>> that, client B >>> >>>>> has initiated a transaction and not client A, so it will give >>> priority to >>> >>>>> client B, and notify A. And yes, this does include some >>> conflicting >>> >>>>> situations, when both client A and client B begin transaction one >>> after >>> >>>>> another, and try to make changes to same set of data. In that case >>> the >>> >>>>> change-request by the later client can be denied, to read the new >>> data, and >>> >>>>> try again. >>> >>>> >>> >>>> This is already supported by the MVCC system but happens at >>> commit() >>> >>>> time. We planned to support pessimistic approach as well but it's >>> not >>> >>>> urgent, at least today, as priority. >>> >>>>> >>> >>>>> I was thinking on this solution, whether there is a possibility of >>> >>>>> deadlocks or dog-trying-to-catch-its-tail kind of problems. But am >>> unclear >>> >>>>> on this yet. >>> >>>>> Managing transactions in distributed version of OrientDB, will >>> also >>> >>>>> bring some new questions. But again, we are already sitting in a >>> world, >>> >>>>> where many of such problems have been thoroughly studied and >>> solved. >>> >>>> >>> >>>> Now distributed TX are not supported, let see in the future. >>> >>>> >>> >>>> Lvc@ >>> >>>> >>> >>>>> >>> >>>>> >>> >>>>> Regards >>> >>>>> >>> >>>>> >>> >>>>> >>> >>>>> >>> >>>>> >>> >>>>> On Sunday, July 1, 2012 4:33:31 AM UTC+5, Lvc@ wrote: >>> >>>>>> >>> >>>>>> Hi Mich, >>> >>>>>> the choice to leave everything on the client side was to maximize >>> >>>>>> concurrency on the server side. Unfortunately this approach has >>> the >>> >>>>>> limitations you exposed. >>> >>>>>> >>> >>>>>> So today transactions works fine using the embedded/local mode >>> but >>> >>>>>> against a remote servers lack of these problems. >>> >>>>>> >>> >>>>>> To solve them this is the solution we thought: leave all as is, >>> but >>> >>>>>> filter the result set on the client side reading the TX changes. >>> This would >>> >>>>>> leave all the benefits of current approach in scalability, but >>> this means >>> >>>>>> also that commands like SQL UPDATE and DELETE should be divided >>> in these >>> >>>>>> steps: >>> >>>>>> >>> >>>>>> client sends the command to the server >>> >>>>>> the server get the target records to change and send to the >>> client the >>> >>>>>> RIDs >>> >>>>>> the client filter them and sends to the servers the changes >>> (mostly >>> >>>>>> none or few) >>> >>>>>> the server do the really SQL statement against filtered records >>> >>>>>> >>> >>>>>> In case you update 1,000 records this means that 1,000 RIDs will >>> be >>> >>>>>> sent to the client side. One serialized RID is 10bytes, so 10k >>> will be >>> >>>>>> transferred to the client. >>> >>>>>> >>> >>>>>> For the SQL Select the overhead is usually minimum because the >>> target >>> >>>>>> is already transferred to the client and just filtered on it. >>> >>>>>> >>> >>>>>> Lvc@ >>> >>>>>> >>> >>>>>> >>> >>>>>> On 30 June 2012 01:55, Mich Geni <[email protected]> wrote: >>> >>>>>>> >>> >>>>>>> Hi, >>> >>>>>>> >>> >>>>>>> I want to illuminate a problem people have faced and discussed >>> in >>> >>>>>>> this forum before. Which is transactions. Most people when first >>> start to >>> >>>>>>> use OrientDB think that they can do pretty stuff in a >>> transaction (means by >>> >>>>>>> which they think to be done in their transaction). But soon they >>> find that >>> >>>>>>> since everything remains in memory on client side until the >>> transaction is >>> >>>>>>> committed (especially temporary RIDs stuff) and not on >>> server-side, so >>> >>>>>>> immediately they start to break their 'transaction' into more >>> pieces (in >>> >>>>>>> OrientDB sense of implementing transactions) in order to make it >>> work. >>> >>>>>>> >>> >>>>>>> But one thinks that what is the need of transactions then? >>> Orient is >>> >>>>>>> a Document-Graph DB with ACID Tx. So one expects that it will be >>> >>>>>>> transactional for graphs. But when you push the transaction >>> thing on the >>> >>>>>>> client side, it really becomes a nightmare. Consider an example >>> below: >>> >>>>>>> >>> >>>>>>> (NOTE: I have tried to give a more complete code example. >>> OrientDB >>> >>>>>>> version: 1.0.1 (but to date svn trunk 5965 still is relevant to >>> consider). >>> >>>>>>> In order to be simple, I have skipped many checks and efficiency >>> jugglery) >>> >>>>>>> >>> >>>>>>> We are taking an example of a financial application which works >>> in >>> >>>>>>> today's modern days of social networks and graphs. A user sends >>> an amount of >>> >>>>>>> $300 from his account to his friend account. Here is how we will >>> do it >>> >>>>>>> OrientDB using Graph API: >>> >>>>>>> >>> >>>>>>> Steps: >>> >>>>>>> >>> >>>>>>> Create domain classes (vertices and edges) >>> >>>>>>> Insert initial records >>> >>>>>>> Write and Run our code to perform the transaction (money >>> transfer) >>> >>>>>>> See results >>> >>>>>>> >>> >>>>>>> Step 1: >>> >>>>>>> >>> >>>>>>> orientdb> Create Class Account Extends V >>> >>>>>>> >>> >>>>>>> Class created successfully with id=10 >>> >>>>>>> >>> >>>>>>> orientdb> Create Class BalanceUpdated Extends E >>> >>>>>>> >>> >>>>>>> Class created successfully with id=11 >>> >>>>>>> >>> >>>>>>> orientdb> Create Class SendAmount Extends E >>> >>>>>>> >>> >>>>>>> Class created successfully with id=12 >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> Step 2: >>> >>>>>>> >>> >>>>>>> orientdb> insert into Account (accountNumber, currentBalance) >>> values >>> >>>>>>> ("516544646", "1000") >>> >>>>>>> >>> >>>>>>> Inserted record >>> >>>>>>> 'Account#8:0{accountNumber:516544646,currentBalance:1000} v0' >>> in 0.005000 >>> >>>>>>> sec(s). >>> >>>>>>> >>> >>>>>>> orientdb> insert into Account (accountNumber, currentBalance) >>> values >>> >>>>>>> ("5165485491", "100") >>> >>>>>>> >>> >>>>>>> Inserted record >>> >>>>>>> 'Account#8:1{accountNumber:5165485491,currentBalance:100} v0' >>> in 0.011000 >>> >>>>>>> sec(s). >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> Step 3: >>> >>>>>>> >>> >>>>>>> // Graph Transaction test >>> >>>>>>> OGraphDatabase graphDB = >>> >>>>>>> OGraphDatabasePool.global().acquire("remote:localhost:2424/graphtest1", >>> >>> >>>>>>> "admin", "admin"); >>> >>>>>>> Date currDate = new Date(); >>> >>>>>>> int amountToTransfer = 300; // in some currency >>> >>>>>>> String senderAccountNum = "516544646"; >>> >>>>>>> String receiverAccountNum = "5165485491"; >>> >>>>>>> >>> >>>>>>> try { >>> >>>>>>> // graphDB.begin(TXTYPE.OPTIMISTIC); // >>> >>>>>>> <-------------------- >>> >>>>>>> >>> >>>>>>> List<ODocument> listAcc = graphDB.query(new >>> >>>>>>> OSQLSynchQuery<Knows>("select from Account where accountNumber = >>> '" + >>> >>>>>>> senderAccountNum + "'")); >>> >>>>>>> >>> >>>>>>> ODocument senderAccount = listAcc.get(0); >>> >>>>>>> >>> >>>>>>> int senderCurrentBalance = >>> >>>>>>> Integer.parseInt(senderAccount.field("currentBalance").toString()); >>> >>> >>>>>>> >>> >>>>>>> if (senderCurrentBalance - amountToTransfer <= >>> 0) { >>> >>>>>>> //throw BalanceBelowTransferAmount; >>> >>>>>>> } >>> >>>>>>> >>> >>>>>>> // useful in generating statements >>> >>>>>>> ODocument senderAccountHistory = >>> >>>>>>> graphDB.createVertex("Account").fields( >>> >>>>>>> "accountNumber", senderAccountNum, >>> >>>>>>> "currentBalance", senderCurrentBalance, >>> >>>>>>> "date", currDate >>> >>>>>>> // and other necessary info >>> >>>>>>> ).save(); >>> >>>>>>> >>> >>>>>>> // Deduct the amount to be transferred from >>> sender >>> >>>>>>> account >>> >>>>>>> ODocument senderAccountUpdated = >>> >>>>>>> senderAccount.fields( >>> >>>>>>> "currentBalance", senderCurrentBalance - >>> >>>>>>> amountToTransfer, >>> >>>>>>> "date", currDate >>> >>>>>>> // and other necessary info >>> >>>>>>> ).save(); >>> >>>>>>> >>> >>>>>>> // link sender account latest to previous >>> balance >>> >>>>>>> ODocument senderBalanceHistory = >>> >>>>>>> graphDB.createEdge(senderAccountUpdated, senderAccountHistory, >>> >>>>>>> "BalanceUpdated").field("date", currDate).save(); >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> List<ODocument> listAcc1 = graphDB.query(new >>> >>>>>>> OSQLSynchQuery<Knows>("select from Account where accountNumber = >>> '" + >>> >>>>>>> receiverAccountNum + "'")); >>> >>>>>>> >>> >>>>>>> ODocument receiverAccount = listAcc1.get(0); >>> >>>>>>> int receiverCurrentBalance = >>> >>>>>>> Integer.parseInt(receiverAccount.field("currentBalance").toString()); >>> >>> >>>>>>> >>> >>>>>>> // useful in generating statements >>> >>>>>>> ODocument receiverAccountHistory = >>> >>>>>>> graphDB.createVertex("Account").fields( >>> >>>>>>> "accountNumber", receiverAccountNum, >>> >>>>>>> "currentBalance", >>> receiverCurrentBalance, >>> >>>>>>> "date", currDate >>> >>>>>>> // and other necessary info >>> >>>>>>> ).save(); >>> >>>>>>> >>> >>>>>>> // Deduct the amount to be transferred from >>> sender >>> >>>>>>> account >>> >>>>>>> ODocument receiverAccountUpdated = >>> >>>>>>> receiverAccount.fields( >>> >>>>>>> "currentBalance", receiverCurrentBalance >>> + >>> >>>>>>> amountToTransfer, >>> >>>>>>> "date", currDate >>> >>>>>>> // and other necessary info >>> >>>>>>> ).save(); >>> >>>>>>> >>> >>>>>>> // link receiver account latest to previous >>> balance >>> >>>>>>> ODocument receiverBalanceHistory = >>> >>>>>>> graphDB.createEdge(receiverAccountUpdated, >>> receiverAccountHistory, >>> >>>>>>> "BalanceUpdated").field( >>> >>>>>>> "date", currDate).save(); >>> >>>>>>> >>> >>>>>>> // link sender and receiver accounts >>> >>>>>>> ODocument sendAmount = >>> >>>>>>> graphDB.createEdge(senderAccountHistory, >>> receiverAccountHistory, >>> >>>>>>> "SendAmount").fields( >>> >>>>>>> "amount", amountToTransfer, >>> >>>>>>> "date", currDate >>> >>>>>>> ).save(); >>> >>>>>>> >>> >>>>>>> // graphDB.commit(); // <-------------------- >>> >>>>>>> >>> >>>>>>> } finally { >>> >>>>>>> graphDB.close(); >>> >>>>>>> } >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> Step 4: >>> >>>>>>> >>> >>>>>>> orientdb> select from Account >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> ---+---------+--------------------+--------------------+---- >>> ----------------+-------------------- >>> >>>>>>> #| RID |accountNumber |currentBalance |date >>> >>>>>>> |out >>> >>>>>>> >>> >>>>>>> ---+---------+--------------------+--------------------+---- >>> ----------------+-------------------- >>> >>>>>>> 0| #8:0|516544646 |700 >>> |2012-06-30 >>> >>>>>>> 03:00:52 |[1] >>> >>>>>>> 1| #8:1|5165485491 |400 >>> |2012-06-30 >>> >>>>>>> 03:00:52 |[1] >>> >>>>>>> 2| #8:2|516544646 |1000 >>> |2012-06-30 >>> >>>>>>> 03:00:52 |[1] |[1] >>> >>>>>>> 3| #8:3|5165485491 |100 >>> |2012-06-30 >>> >>>>>>> 03:00:52 |null |[2] >>> >>>>>>> >>> >>>>>>> ---+---------+--------------------+--------------------+---- >>> ----------------+--------------------+-------------------- >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> 4 item(s) found. Query executed in 0.206 sec(s). >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> orientdb> select from BalanceUpdated >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> ---+---------+--------------------+--------------------+-------------------- >>> >>> >>>>>>> #| RID |out |in |date >>> >>>>>>> >>> >>>>>>> ---+---------+--------------------+--------------------+-------------------- >>> >>> >>>>>>> 0| #13:0|#8:0 |#8:2 >>> |2012-06-30 >>> >>>>>>> 03:00:52 >>> >>>>>>> 1| #13:1|#8:1 |#8:3 >>> |2012-06-30 >>> >>>>>>> 03:00:52 >>> >>>>>>> >>> >>>>>>> ---+---------+--------------------+--------------------+-------------------- >>> >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> 2 item(s) found. Query executed in 0.095 sec(s). >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> orientdb> select from SendAmount >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> ---+---------+--------------------+--------------------+---- >>> ----------------+-------------------- >>> >>>>>>> #| RID |out |in |amount >>> >>>>>>> |date >>> >>>>>>> >>> >>>>>>> ---+---------+--------------------+--------------------+---- >>> ----------------+-------------------- >>> >>>>>>> 0| #14:0|#8:2 |#8:3 |300 >>> >>>>>>> |2012-06-30 03:00:52 >>> >>>>>>> >>> >>>>>>> ---+---------+--------------------+--------------------+---- >>> ----------------+-------------------- >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> 1 item(s) found. Query executed in 0.078 sec(s). >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> orientdb> >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> As depicted in Step 3, we are doing a single financial >>> transaction on >>> >>>>>>> graph, inside the try/finally block. If we face any runtime >>> error in the >>> >>>>>>> middle, our transaction is not done (and needs to be rolled >>> back). But as >>> >>>>>>> you can see the transaction begin() and commit() statements are >>> commented >>> >>>>>>> out. Because OrientDB's current implementation doesn't allow >>> such >>> >>>>>>> transaction. Every thing is client side until committed, and >>> sent to server. >>> >>>>>>> And as you can see, we are doing many things in this >>> transaction, which are >>> >>>>>>> decided/found on server side. So we are left with doing no >>> transaction and >>> >>>>>>> doing each step as non atomic. Not to mention the helper code >>> required in >>> >>>>>>> case of errors if happen in the middle, to undo the previous >>> changes to the >>> >>>>>>> DB before error. And the most concerning thing is, this will be >>> done >>> >>>>>>> thousands of times by thousands of developers to cover these few >>> loop holes >>> >>>>>>> in OrientDB. Isn't it more easy to cover the loop holes by >>> themselves. >>> >>>>>>> >>> >>>>>>> Therefore, it will be great to see real transactions (be it >>> server >>> >>>>>>> side or in any other way, but correct and non-surprising) >>> implementation in >>> >>>>>>> OrientDB. And as OrientDB is undergoing heavy >>> evolution/development, we can >>> >>>>>>> expect such problems to go away in near future. >>> >>>>>>> >>> >>>>>>> >>> >>>>>>> Regards >>> >>>>>> >>> >>>>>> >>> >>>> >>> >>> -- >>> >>> >>> >>> >>> >>> >>> >> >>> >> >>> > -- >>> > >>> > >>> > >>> >> -- >> >> --- >> You received this message because you are subscribed to the Google Groups >> "OrientDB" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> For more options, visit https://groups.google.com/d/optout. >> > > -- --- You received this message because you are subscribed to the Google Groups "OrientDB" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
